AJS_Developer
asked on
CopyFromRecordset bug in Excel
A very strange, yet simple to explain bug:
I have an XLA Add-In that provides 3 reports. When Report 1 is run, which has 36 columns of all currency data copied from a SQL Server stored procedure to a new sheet, it works fine. Report 2 works in a very similar way, except that after doing CopyFromRecordset 3 of the columns in Report 1 are now formatted as Dates.
How could this be?
I have an XLA Add-In that provides 3 reports. When Report 1 is run, which has 36 columns of all currency data copied from a SQL Server stored procedure to a new sheet, it works fine. Report 2 works in a very similar way, except that after doing CopyFromRecordset 3 of the columns in Report 1 are now formatted as Dates.
How could this be?
ASKER
Thanks for the reply harfang. It happens no matter what workbook / worksheet I have open. It always applies date formatting to whatever sheet was previously active (even if it wasn't Report 1).
The name of Report 2 is actually Currency_PlanningData. I have copied 2 lines from the source code. It is definitely thrown by CopyFromRecordset because if I UNcomment the second line and comment out the first line then the bug stops occurring.
TestProc1 is a bogus procedure I created that just selects 'Hello World!' as a 1 column, 1 row result.
The name of Report 2 is actually Currency_PlanningData. I have copied 2 lines from the source code. It is definitely thrown by CopyFromRecordset because if I UNcomment the second line and comment out the first line then the bug stops occurring.
TestProc1 is a bogus procedure I created that just selects 'Hello World!' as a 1 column, 1 row result.
'Run procedure
Dim rsCurrencyPlanningDataResults As ADODB.Recordset
Set rsCurrencyPlanningDataResults = conn.Execute( _
"EXEC Currency_Planning_Data '" & _
Replace(CompanyCode, "'", "''") & _
"'")
'testing
' Set rsCurrencyPlanningDataResults = conn.Execute("EXEC TestProc1")
ASKER
I've managed to boil it down to a simple test case using Excel 2007 and SQL Server 2005.
1. Create TestProc1 (see attached)
2. Add the macro Sub1 to a new Excel workbook (see attached)
3. In this new workbook populate Sheet1!A5 with the number 1
4. Run the macro. It will create sheet 4, with the value 2 in A5 (which is strange in itself because shouldn't 1900-01-01 be stored as 1?!)
5. Go back to Sheet1 and see that A5 now has default date formatting so that 1 is represented as 01/01/1900.
1. Create TestProc1 (see attached)
2. Add the macro Sub1 to a new Excel workbook (see attached)
3. In this new workbook populate Sheet1!A5 with the number 1
4. Run the macro. It will create sheet 4, with the value 2 in A5 (which is strange in itself because shouldn't 1900-01-01 be stored as 1?!)
5. Go back to Sheet1 and see that A5 now has default date formatting so that 1 is represented as 01/01/1900.
SQL for TestProc1:
------------------
ALTER PROC TestProc1 AS
SELECT
CAST('1900-01-01' AS DATETIME) AS UNnamedField
go
Excel Macro:
------------
Sub sub1()
'Get connection string
Dim currentString As String
currentString = GetSetting("Database Settings", "Data", "ConnectionString", "")
'Connect to db
Dim conn As New ADODB.Connection
conn.ConnectionString = currentString
conn.Open
'Run procedure
Dim rsCurrencyPlanningDataResults As ADODB.Recordset
'testing
Set rsCurrencyPlanningDataResults = conn.Execute("EXEC TestProc1")
'Check that we've got a workbook open
If ActiveWorkbook Is Nothing Then
Application.Workbooks.Add
End If
'Put results to new sheet
Dim NewSheet As Worksheet
Worksheets.Add
Set NewSheet = ActiveSheet
'paste out column headings
Dim i As Integer
For i = 0 To rsCurrencyPlanningDataResults.Fields.Count - 1
NewSheet.Cells(4, i + 1).Value = rsCurrencyPlanningDataResults.Fields.Item(i).Name
Next i
'
' 'paste contents
NewSheet.Range("A5").CopyFromRecordset rsCurrencyPlanningDataResults
End Sub
ASKER
My workaround solution for now is to pass all dates through as text values (YYYY-MM-DD). This is because it seems like Excel won't try to format the previously active sheet values to text, it only does it for dates.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome! Thanks harfang that worked a treat.
I'm still going with my string representation of dates though - I really don't want the hassle of testing to make sure everything is being transferred and represented correctly.
It's amazing that Microsoft products still can't be integrated easily when it comes to transferring dates from server to client.
I'm still going with my string representation of dates though - I really don't want the hassle of testing to make sure everything is being transferred and represented correctly.
It's amazing that Microsoft products still can't be integrated easily when it comes to transferring dates from server to client.
> It's amazing that Microsoft products still can't be integrated easily when it comes to transferring dates from server to client.
Yes. Microsoft products have a lot of incompatibility issues... most of them with other Microsoft products...
The safest way to deal with dates is ISO format. I've started using it everywhere, even for display and reporting (I changed my regional setting accordingly). It's like the metric system: at some point we need a universal standard.
Thanks and success with your project!
(°v°)
Yes. Microsoft products have a lot of incompatibility issues... most of them with other Microsoft products...
The safest way to deal with dates is ISO format. I've started using it everywhere, even for display and reporting (I changed my regional setting accordingly). It's like the metric system: at some point we need a universal standard.
Thanks and success with your project!
(°v°)
Does report2 contain data columns? Does it work if you run report2, then report1? Does it change whether it's a new workbook?
(°v°)