asked on

Paste of datagridview into Excel causes data format changes.

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]

ASKER CERTIFIED SOLUTION

andrewssd3

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Karen Wilson

ASKER

I will try the variant array method and see what happens. Thanks for the direction.

Karen Wilson

ASKER

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