Excel spreadsheet loses number formats when data is copied from an ADODB record set

Question

I am updating an excel table from an ADODB record set using the CopyFromRecordset method.

After updating the numbers are displayed as dates, where there are columns with numbers.

The workaround I have used so far is to format the columns to numbers via VBA , but this is not a good solution, since it takes longer to complete the report. I also need to write code to host a large number of tables.

Is there a quick fix? Any help is appreciated.

 'Delete old data and copy the recordset to the table Me.ListObjects(tblName).DataBodyRange.ClearContents Me.Range(tblName).CopyFromRecordset rst 

tblName - refers to an existing table in which data of the same format / data type is stored as the first data

Answer 1

Try this: it copies the results to an array, transfers it and then copies to excel

 Dim rs As New ADODB.Recordset Dim targetRange As Excel.Range Dim vDat As Variant ' Set rs ' Set targetRange rs.MoveFirst vDat = Transpose(rs.GetRows) targetRange.Value = vDat Function Transpose(v As Variant) As Variant Dim X As Long, Y As Long Dim tempArray As Variant ReDim tempArray(LBound(v, 2) To UBound(v, 2), LBound(v, 1) To UBound(v, 1)) For X = LBound(v, 2) To UBound(v, 2) For Y = LBound(v, 1) To UBound(v, 1) tempArray(X, Y) = v(Y, X) Next Y Next X Transpose = tempArray End Function 

Answer 2

I know this is a late answer, but I came across the same error. I think I found a workaround.

Excel seems to expect the range to be the top left cell, not the range of cells. So just change your statement to Range(tblName).Cells(1,1).CopyFromRecordset rst

 'Delete old data and copy the recordset to the table Me.ListObjects(tblName).DataBodyRange.ClearContents Me.Range(tblName).Cells(1,1).CopyFromRecordset rst 

There is also a requirement that the target sheet be active, so you may need the sheet to be active first, and then switch back to the previously active sheet. This may have been fixed in a later version of Excel.

Answer 3

The following is sample code. Whenever proc getTableData is called, the formatting and format of the column in table 1 is stored according to the set of records. Hope this is what you are looking for.

  Sub getTableData() Dim rs As ADODB.Recordset Set rs = getRecordset Range("A1").CurrentRegion.Clear Range("A1").CopyFromRecordset rs Sheets("Sheet1").ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlNo).Name = "Table1" End Sub Function getRecordset() As ADODB.Recordset Dim rsContacts As ADODB.Recordset Set rsContacts = New ADODB.Recordset With rsContacts .Fields.Append "P_Name", adVarChar, 50 .Fields.Append "ContactID", adInteger .Fields.Append "Sales", adDouble .Fields.Append "DOB", adDate .CursorLocation = adUseClient .CursorType = adOpenStatic .Open For i = 1 To WorksheetFunction.RandBetween(3, 5) .AddNew !P_Name = "Santosh" !ContactID = 2123456 * i !Sales = 10000000 * i !DOB = #4/1/2013# .Update Next rsContacts.MoveFirst End With Set getRecordset = rsContacts End Function 

Answer 4

After reading the posts of other people who have faced the same problem on the forums, here are all the options I know (some of them were already mentioned in other answers):

1. Paste only in the first cell of the target range - see @ThunderFrame answer.
2. Activate the sheet with the target range in the line above CopyFromRecordset()

3. Disable automatic recount:

    Application.Calculation = xlCalculationManual .CopyFromRecordset rs Application.Calculation = xlCalculationAutomatic 

workarounds:

1. Loop through rows / columns and insert values ​​one at a time - @Sriketan's answer provides code for this. Other code snippets are in the links below.
2. Paste into the temporary book and copy it from there.

Sources:

1. Post and discussion by Duchgemini: https://dutchgemini.wordpress.com/2011/04/21/two-serious-flaws-with-excels-copyfromrecordset-method/
2. Microsoft Forum: https://social.msdn.microsoft.com/Forums/office/en-US/129c0a52-4ccb-4f54-9e38-8c0ae6e300b1/copyfromrecordset-corrupts-cell-formats-for-the-whole-excel-workbook ? forum = exceldev