CopyFromRecordset bug in Excel

This looks like a bug in the Add-In. Perhaps Report2 contains a lazy line using "Sheet1.Range" or "Activesheet.Range" at the wrong time, so that formatting meant for the second report end up on the first sheet.

Does report2 contain data columns? Does it work if you run report2, then report1? Does it change whether it's a new workbook?

(°v°)

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.

        'Run procedure
        Dim rsCurrencyPlanningDataResults As ADODB.Recordset
        Set rsCurrencyPlanningDataResults = conn.Execute( _
            "EXEC Currency_Planning_Data '" & _
            Replace(CompanyCode, "'", "''") & _
            "'")
    
        'testing
'        Set rsCurrencyPlanningDataResults = conn.Execute("EXEC TestProc1")

Select allOpen in new window

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.

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

Select allOpen in new window

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.

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.

> 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°)