Excel table loses number formats when data is copied from ADODB recordset

vba excel adodb
flungu picture flungu · Apr 17, 2013 · Viewed 10.3k times · Source

I'm updating an excel table from an ADODB recordset using the CopyFromRecordset method.

After the update, the numbers show up as dates wherever there are number columns.

The workaround I used until now is to format the columns back to numbers through VBA, but it's not a good solution as takes more time for the report to complete. Also I have to write code to accommodate a lot of tables.

Is there a quick fix? Any help is greatly 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 that held data of the same format/datatype as rst data

Answer

ThunderFrame picture ThunderFrame · Oct 20, 2016

I know this is a late answer, but I was encountering this same error. I think I've found a workaround.

It seems Excel expects the range to be the top-left cell rather than a range of cells. So just modify 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 also seems to be a requirement that the target sheet be active, so you might have to ensure the sheet is active first, and then change back to the previously active sheet. This might have been fixed in later version of Excel.

Related questions

Accessing SQL Database in Excel-VBA

I am copying an VBA code snippet from MSDN that shows me how to grab results from a SQL query into excel sheet (Excel 2007): Sub GetDataFromADO() 'Declare variables' Set objMyConn = New ADODB.Connection Set objMyCmd = New ADODB.Command Set objMyRecordset = …

Read More
Excel VBA - connect to sql with a trusted connection (no uid/pwd)

Is there a way to have Excel connect to Sql Server using the current user's network windows authentication (trusted connection/integrated security)? I don't want to rely on a sql login or have a uid/password in my code.

Read More
VBA New Database Connection

How to change the code below to prevent what you see in the screenshot. I am running a macro with the following code Dim conn As ADODB.Connection Dim rec1 As ADODB.Recordset Dim thisSql As String Set conn = New …

Read More