Share via

Excel export Column wrong format

Anonymous
2018-04-10T01:20:30+00:00

In the code below I'm setting Column E format for currency, "$#,##0.00;-$#,##0.00", but for some reason when I open the excel export the data is showing up with a Date format.

Would anyone have any idea why?

    With xlSheet

    .Name = "Agency Summary"

        .Cells.Font.Name = "Calibri"

        .Cells.Font.Size = 10

       'heading format

        .Range("A1:G1").Cells.Font.Bold = True 'bold

        .Range("A1:G1").Interior.Color = RGB(31, 73, 125)  'back color

        .Range("A1:G1").Font.Color = RGB(255, 255, 255) 'font color

        .Range("A1:G1").Font.Size = 11

        'Set column widths

        .Columns("A").ColumnWidth = 45

        .Columns("B").ColumnWidth = 45

        .Columns("C").ColumnWidth = 14

        .Columns("D").ColumnWidth = 14

        .Columns("E").ColumnWidth = 14

        .Columns("F").ColumnWidth = 14

        .Columns("G").ColumnWidth = 14

        'currency format

        .Columns("D").NumberFormat = "$#,##0.00;-$#,##0.00"

        .Columns("E").NumberFormat = "$#,##0.00;-$#,##0.00"

        .Columns("F").NumberFormat = "$#,##0.00;-$#,##0.00"

        .Columns("G").NumberFormat = "$#,##0.00;-$#,##0.00"

        For cols = 0 To rs3.Fields.Count - 1

            .Cells(1, cols + 1).Value = rs3.Fields(cols).Name

        Next

        'Copy data from recordset to sheet

        .Range("A2").CopyFromRecordset rs3

        End With

    rs3.MoveFirst

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Vijay A. Verma 104.9K Reputation points Volunteer Moderator
    2018-04-11T18:24:30+00:00

    Did you try debugging it?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2018-04-10T23:12:30+00:00

    Down below you are using

    Columns("E").NumberFormat = "m/d/yyyy"

    Shouldn't it be

    .Columns("E").NumberFormat = "$#,##0.00;-$#,##0.00"

    That's an entirely different Worksheet. The error here is on Worksheet (1), that code is for Worksheet (3).

    Was this answer helpful?

    0 comments
  3. Vijay A. Verma 104.9K Reputation points Volunteer Moderator
    2018-04-10T19:59:16+00:00

    Down below you are using

    Columns("E").NumberFormat = "m/d/yyyy"

    Shouldn't it be

    .Columns("E").NumberFormat = "$#,##0.00;-$#,##0.00"

    Was this answer helpful?

    0 comments
  4. Anonymous
    2018-04-10T13:18:05+00:00

    I honestly can't tell where the problem is but every time I run the code and export my data Column E ends up as Date instead of the format I wrote down.

    Other than that everything works exactly as it should.

    Below is the entire part of the code that calls the Excel app and formats the columns:

    Set xlApp = Excel.Application

        restoreSheetsInNewWorkbook = xlApp.SheetsInNewWorkbook

        xlApp.SheetsInNewWorkbook = 3

        xlApp.Visible = False

        Set xlBook = xlApp.Workbooks.Add

        Set xlApp = Excel.Application

        restoreSheetsInNewWorkbook = xlApp.SheetsInNewWorkbook

        xlApp.SheetsInNewWorkbook = 3

        xlApp.Visible = False

        Set xlBook = xlApp.Workbooks.Add

        Set xlSheet = xlBook.Worksheets(1)

        With xlSheet

        .Name = "Agency Summary"

            .Cells.Font.Name = "Calibri"

            .Cells.Font.Size = 10

           'heading format

            .Range("A1:G1").Cells.Font.Bold = True 'bold

            .Range("A1:G1").Interior.Color = RGB(31, 73, 125)  'back color

            .Range("A1:G1").Font.Color = RGB(255, 255, 255) 'font color

            .Range("A1:G1").Font.Size = 11

            'Set column widths

            .Columns("A").ColumnWidth = 45

            .Columns("B").ColumnWidth = 45

            .Columns("C").ColumnWidth = 14

            .Columns("D").ColumnWidth = 14

            .Columns("E").ColumnWidth = 14

            .Columns("F").ColumnWidth = 14

            .Columns("G").ColumnWidth = 14

            'currency format

            .Columns("D").NumberFormat = "$#,##0.00;-$#,##0.00"

            .Columns("E").NumberFormat = "$#,##0.00;-$#,##0.00"

            .Columns("F").NumberFormat = "$#,##0.00;-$#,##0.00"

            .Columns("G").NumberFormat = "$#,##0.00;-$#,##0.00"

            For cols = 0 To rs1.Fields.Count - 1

                .Cells(1, cols + 1).Value = rs1.Fields(cols).Name

            Next

            'Copy data from recordset to sheet

            .Range("A2").CopyFromRecordset rs1

            End With

        rs1.MoveFirst

        Set xlSheet = xlBook.Worksheets(2)

        With xlSheet

        .Name = "Invoice Summary"

            .Cells.Font.Name = "Calibri"

            .Cells.Font.Size = 10

            'heading format

            .Range("A1:F1").Cells.Font.Bold = True 'bold

            .Range("A1:F1").Interior.Color = RGB(31, 73, 125)  'back color

            .Range("A1:F1").Font.Color = RGB(255, 255, 255) 'font color

            .Range("A1:F1").Font.Size = 11

            'Set column widths

            .Columns("A").ColumnWidth = 35

            .Columns("B").ColumnWidth = 8

            .Columns("C").ColumnWidth = 12

            .Columns("D").ColumnWidth = 9

            .Columns("E").ColumnWidth = 9

            .Columns("F").ColumnWidth = 12

            'currency format

            .Columns("C").NumberFormat = "$#,##0.00;-$#,##0.00"

            .Columns("D").NumberFormat = "$#,##0.00;-$#,##0.00"

            .Columns("E").NumberFormat = "$#,##0.00;-$#,##0.00"

            .Columns("F").NumberFormat = "$#,##0.00;-$#,##0.00"

            For cols = 0 To rs2.Fields.Count - 1

                .Cells(1, cols + 1).Value = rs2.Fields(cols).Name

            Next

            'Copy data from recordset to sheet

            .Range("A2").CopyFromRecordset rs2

            End With

        rs2.MoveFirst

        Set xlSheet = xlBook.Worksheets(3)

        With xlSheet

            .Name = "Raw Data"

            .Cells.Font.Name = "Calibri"

            .Cells.Font.Size = 10

            'Set column widths

            .Columns("A").ColumnWidth = 11.5

            .Columns("B").ColumnWidth = 8

            .Columns("C").ColumnWidth = 39

            .Columns("D").ColumnWidth = 39

            .Columns("E").ColumnWidth = 9.75

            .Columns("F").ColumnWidth = 9.5

            .Columns("G").ColumnWidth = 15.75

            .Columns("H").ColumnWidth = 11

            .Columns("I").ColumnWidth = 9

            .Columns("J").ColumnWidth = 6.5

            .Columns("K").ColumnWidth = 4

            .Columns("L").ColumnWidth = 9.5

            'Alignment

            .Columns("A:L").HorizontalAlignment = xlLeft

            'Format columns

            .Columns("G").NumberFormat = "$#,##0.00;-$#,##0.00"

            .Columns("H").NumberFormat = "$#,##0.00;-$#,##0.00"

            .Columns("F").NumberFormat = "$#,##0.00;-$#,##0.00"

            .Columns("E").NumberFormat = "m/d/yyyy"

            .Columns("I").NumberFormat = "m/d/yyyy"

            'Titles format

            .Range("A1:L1").Cells.Font.Bold = True 'bold

            .Range("A1:L1").Interior.Color = RGB(31, 73, 125)  'back color

            .Range("A1:L1").Font.Color = RGB(255, 255, 255) 'font color

            .Range("A1:L1").Font.Size = 11

            For cols = 0 To rs3.Fields.Count - 1

                .Cells(1, cols + 1).Value = rs3.Fields(cols).Name

            Next

            'Copy data from recordset to sheet

            .Range("A2").CopyFromRecordset rs3

        End With

         rs3.MoveFirst

    Was this answer helpful?

    0 comments
  5. Vijay A. Verma 104.9K Reputation points Volunteer Moderator
    2018-04-10T03:53:45+00:00

    I did test your code (I had to modify though) in Excel and it is working fine. Where exactly are you facing the issue?

    Was this answer helpful?

    0 comments