CopyFromRecordset Converting Currency to Dates

DCrake

Remembered
Local time
Today, 21:30
Joined
Jun 8, 2005
Messages
8,626
This is the situation

1. Template Excel workbook with a preformatted worksheet set to currency in the desired range.

This is copied to a new workbook using Filecopy.

2. Access opens up the workbook using late binding
3. Selects a worksheet
4. Uses CopyFromRecordset to copy the contents of a crosstab query containing currency values in the columns to the named worksheet.

5. Save the workbook and Quit

6. Open the workbook, got to the worksheet and all the currecny values have been converted to dates.

This is not consistant thoughout the export process, I have other worksheets that employ the same method of populating them (CopyFromRecordset).

As a workaround I even copied the crosstab to a table and made sure that all the fields were set to currency. Then used this instead of the corsstab query. This also failed.

Any ideas on how to prevent it?

Just an update:

I created a newe worksheet in the workbook and ran it in the new worksheet and it dod not error. This leads me to think that there is some sort of corruption in the worksheet/workbook. Is this possible in Excel? Never come accross this before.
 
Last edited:
Corruption is quite common in excel workbooks, especially if they contain a lot of activex controls, but I've come across it even in small workbooks.
 
The way I recall it, if Excel worksheet has any cells that were specifically formatted to be something else, it would override any incoming content's formatting. I had a situation where some of my columns weren't coming out in the right way and that was fixed by either clearing out the formatting or using explicit formatting.
 
There are no ActiveX controls in it, however, was just about to post that I have now identified and resolved the issue.

Offending Code:

Code:
         For x = 250 To 4 Step -1
               If xlsheet.range("B" & x).Value = "" And xlsheet.range("A" & x).Value = "" Then
                  xlsheet.range("B" & x).EntireRow.Delete
              End If
            Next 

                xlsheet.range("C4:Q250").SELECT
                xlsheet.range("C4:C250").NumberFormat = "$#,##0.00"
                xlsheet.range("B6:B250").SELECT
                xlsheet.range("B6:B250").NumberFormat = "0"

Solution:
Code:
                xlsheet.range("C4:Q250").SELECT
                xlsheet.range("C4:C250").NumberFormat = "$#,##0.00"
                xlsheet.range("B6:B250").SELECT
                xlsheet.range("B6:B250").NumberFormat = "0"

            For x = 250 To 4 Step -1
               If xlsheet.range("B" & x).Value = "" And xlsheet.range("A" & x).Value = "" Then
                  xlsheet.range("B" & x).EntireRow.Delete
              End If
            Next


As the size of the recordset will vary I had a block of rows that wuld never be exceeded by the size of the recordset. Beneath this block was the formulas for summing the columns. The idea was to copy in the recordset immediately below the column headings. Then perfrom a backwards loop deleing all the blank rows. Then reformatting the finished block as currency.

This was causing the rows to loose there predefined format allowing Excel to pick the data type. By reversing the code it did not try to do this and the cells contents were preseved as currency.
 
I would have just put a few lines of code into add the SUM formula programmatically and then you wouldn't need to delete empty rows, you can also delete your select statements as they don't do anything.
 

Users who are viewing this thread

Back
Top Bottom