Paste of datagridview into Excel causes data format changes.

Posted on 2012-08-10
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:
Excel makes it:

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

            o = TblWIWPDataGridView.GetClipboardContent

            objSheet.Columns.NumberFormat = "@"


[Insert programmer slapping Bill Gates here]
Question by:Karen Wilson
    LVL 17

    Accepted Solution

    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.

    Author Comment

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

    Author Comment

    by:Karen Wilson
    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)

                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

                Dim o As DataObject
                o = TblWIWPDataGridView.GetClipboardContent


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

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

                End With

                Dim autoRange4 As Excel.Range = objSheet.UsedRange

                With autoRange4
                    .Font.Name = "Arial"
                    .Font.Size = 9
                    .ColumnWidth = 255
                End With

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    It’s quite interesting for me as I worked with Excel using for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now