VBA CopyFromRecordset Format Issue

Question

When I use the CopyFromRecordset method the Excel sheet has the first 10 records in column E as dates even after I explicitly format the column as currency. Column B was having the same issue but is formatted correctly after I added the formatting code.

I know I can loop through the records to fix this, but does anyone know why this is happening? I'm using an Excel template file that already has columns B, C and E formatted as currency. So, the CopyFromRecordset method must be changing it for the first 10 cells, but then it ignores the explicit formatting from the code?

xlSheet.Range("A4").CopyFromRecordset rst
rst.Close
xlSheet.Range("B:C,E:E").NumberFormat = "$#,##0.00"

It has the same outcome if I change it to this:

xlSheet.Range("A4").CopyFromRecordset rst
rst.Close
xlSheet.Range("B:C").NumberFormat = "$#,##0.00"
xlSheet.Range("E:E").NumberFormat = "$#,##0.00"

Answer 1

Interesting, I think the issue was the new workbook. It appears that the code you provided only creates a new, blank workbook. If you intend to open a template file, you need to specify the template file path as below.

Sub CopyDataFromRecordsetToExcel()
    Dim xlApp As Object ' Excel.Application
    Dim xlWorkbook As Object ' Excel.Workbook
    Dim xlSheet As Object ' Excel.Worksheet

    Dim TemplatePath As String
    ' Define the path to your personal template file
    TemplatePath = ".....\yourTemp.xltx"

    ' Create a new Excel application and workbook
    Set xlApp = CreateObject("Excel.Application")
    ' Open a new workbook based on the template
    Set xlWorkbook = xlApp.Workbooks.Add(TemplatePath)
    Set xlSheet = xlWorkbook.Worksheets(1)
    xlApp.Visible = True
    
end Sub