Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 796
  • Last Modified:

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?
0
AJS_Developer
Asked:
AJS_Developer
  • 4
  • 3
1 Solution
 
harfangCommented:
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°)
0
 
AJS_DeveloperAuthor Commented:
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")

Open in new window

0
 
AJS_DeveloperAuthor Commented:
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

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
AJS_DeveloperAuthor Commented:
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.
0
 
harfangCommented:
Great job AJS_Developer,

It really helps to have a boiled down version and actually *see* it happening. It's a bug, no doubt, YABOMS! (yet another bug...)

There is a quite simple workaround, see below.

> value 2 for 1900-01-01

Ah. That's YABOMS! You see, the original Excel team chose 1 to mean 1900-01-01. Good choice, no problem. Except that they believed 1900 to be a leap year... and Excel still does. Try 1900-02-29 anywhere else, it should be rejected. When the "Microsoft dates" became widely spread, e.g. in Access, it was no longer possible to modify that initial choice.

In other words, the Excel date system really starts only on 1900-03-01, day 61. And the true day 1 (in MS databases) is 1899-12-31.

Cheers!
(°v°)
        'Put results to new sheet
        Dim NewSheet As Worksheet
        Set NewSheet = Worksheets.Add
        ' YABOMS!
        ' The auto-select of the new sheet isn't fully registered
        ' and the auto-formatting of the CopyFromRecordset method
        ' will use the previously active sheet.
        ' Workaround (make it official):
        NewSheet.Activate

Open in new window

0
 
AJS_DeveloperAuthor Commented:
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.
0
 
harfangCommented:
> 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°)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now