?
Solved

Paste of datagridview into Excel causes data format changes.

Posted on 2012-08-10
3
Medium Priority
?
767 Views
Last Modified: 2013-02-25
I copy/paste a table from a datagridview into Excel.  One column has ID numbers that Excel automatically turns into a date format.

Datagrid shows:
      10-09-10
      10-20-24
      10-20-30
      10-20-39
      60-01-02
      60-02-02
Excel makes it:
10/9/2010
10/20/2024
10/20/1930
10/20/1939
60-01-02
60-02-02

I have set the objectsheet to a text format but when the paste occurs, it automatically changes each cell to what it thinks the format should be.  

Here is my code.  I even tried changing the column to text format after the paste.  It turns the date into the five-digit date number.  

Any help will help make me not insane... Thanks!

Dim o As DataObject

            TblWIWPDataGridView.SelectAll()
            o = TblWIWPDataGridView.GetClipboardContent
            Clipboard.SetDataObject(o)

            objSheet.Columns.NumberFormat = "@"

            objSheet.Range("A1").Select()
            objSheet.Paste()

[Insert programmer slapping Bill Gates here]
0
Comment
Question by:Karen Wilson
  • 2
3 Comments
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 2000 total points
ID: 38281757
These problems are crazy.  If you have programmatic access to the datagridview, why not get the values separately and put them into a variant array yourself. I don't do VB .Net, but here's an example of getting data from a range, and putting it back as text - you can substitute your datagridview for my aVals:
Sub insDates()

    Dim aVals As Variant
    Dim aVals1 As Variant
    Dim rngOut As Range
    Dim r As Long, c As Long

    Set rngOut = Selection
    aVals = rngOut.Value
    
    Set rngOut = rngOut.Offset(0, rngOut.Columns.Count + 2)
    
    ReDim aVals1(1 To UBound(aVals, 1), 1 To UBound(aVals, 2))
    
    For r = 1 To UBound(aVals, 1)
        For c = 1 To UBound(aVals, 2)
            aVals1(r, c) = "'" & aVals(r, c)
        Next c
    Next r
    
    
    rngOut.Value = aVals1


End Sub

Open in new window

You manually put in the single quote to force the text-ness of the output.  It's much more efficient to use an intermediate array and put it in in one go rather than adding it cell by cell.  The other approach would be to paste into a text file, then import the text file using the relevant text option, but that's a bit more messy.
0
 

Author Comment

by:Karen Wilson
ID: 38281785
I will try the variant array method and see what happens.  Thanks for the direction.
0
 

Author Comment

by:Karen Wilson
ID: 38926310
I finally got back to this problem and this is the code that fixed it for me.  The spreadsheet has an apostrophe before each WIWPS number, but I don't care!  You can still do sort etc. with it.  



If TblWIWPDataGridView.Rows.Count > 0 Then

            For Each row As DataGridViewRow In TblWIWPDataGridView.Rows
                Dim old As String = CStr(row.Cells(0).Value)
                Dim newNumb As String = CStr("'" & old)
                row.Cells(0).Value = CStr(newNumb)
            Next

            Dim objBooks As Excel.Workbooks
            Dim objSheets As Excel.Sheets
            Dim objSheet As Excel._Worksheet
           
            ' Create a new instance of Excel and start a new workbook.
            objApp = New Excel.Application()
            objBooks = objApp.Workbooks
            objBook = objBooks.Add
            objSheets = objBook.Worksheets
            objSheet = objSheets(1)

            'copy the grid and paste it to the clip board
            Clipboard.Clear()

            Dim o As DataObject
            TblWIWPDataGridView.SelectAll()
            o = TblWIWPDataGridView.GetClipboardContent
            Clipboard.SetDataObject(o)

            objSheet.Range("A1").Select()
            objSheet.Paste()

            Dim rg4 As Excel.Range = objSheet.Columns("A")
            rg4.Select()
            rg4.Delete()

            Dim rg5 As Excel.Range = objSheet.Columns("A")
            rg5.Select()
            With rg5

            End With

            Dim autoRange4 As Excel.Range = objSheet.UsedRange

            With autoRange4
                .Font.Name = "Arial"
                .Font.Size = 9
                .ColumnWidth = 255
                .EntireColumn.AutoFit()
                .EntireRow.AutoFit()
            End With

            'Return control of Excel to the user.
            objApp.Visible = True
            objApp.UserControl = True
        Else
            MsgBox("You need to fill the grid.")
        End If
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question