Solved

CopyFromRecordset bug in Excel

Posted on 2009-10-29
7
768 Views
Last Modified: 2012-05-08
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
Comment
Question by:AJS_Developer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 58

Expert Comment

by:harfang
ID: 25699934
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
 

Author Comment

by:AJS_Developer
ID: 25707643
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
 

Author Comment

by:AJS_Developer
ID: 25707939
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:AJS_Developer
ID: 25708122
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
 
LVL 58

Accepted Solution

by:
harfang earned 500 total points
ID: 25708506
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
 

Author Closing Comment

by:AJS_Developer
ID: 31647826
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
 
LVL 58

Expert Comment

by:harfang
ID: 25716545
> 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question