Exporting from Access to Excel formatting

I am exporting a table from Access to Excel and when I do that the cell format is "General" so it can go out to 8 decimal places.  I need to format some of the columns so the cells are in the Number format to 2 decimal places.  Here is my code so far:  

Dim vTdate
    Dim vFileName As String
    Dim vCurrDate As String
     
    vCurrDate = Month(Date) & "_" & Day(Date) & "_" & Year(Date)
         
    vFileName = "MER Report" & " " & vCurrDate
           
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tblSummaryTotals", "c:\Temp\" & vFileName & ".xlsx", True
   
    Dim filePath As String
    filePath = "c:\Temp\" & vFileName & ".xlsx"
    Dim xl As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Set xl = CreateObject("Excel.Application")
    Set xlBook = GetObject(filePath)
    xl.Visible = True
    xlBook.Windows(1).Visible = True
    Set xlSheet = xlBook.Worksheets(1)
           
    With xlSheet.Range("A1:AI1").Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThick
    End With
           
    With xlSheet.Range("A1:AI100")
        .HorizontalAlignment = xlLeft
        .Font.Size = 10
        .Font.Name = "Arial"
             
    End With
   
    xlSheet.Range("A1:AI1").Font.Bold = True
    xlSheet.Range("A1:AI1").Font.Size = 11
    xlSheet.Cells.Columns.AutoFit
    xlBook.Save
    xl.Quit
    Set xl = Nothing
    Set xlBook = Nothing
    Set xlSheet = Nothing

Thanks,
Scott
Scott PalmerData AnalystAsked:
Who is Participating?
 
Dale FyeCommented:
I believe what you are looking for is something like:

xlSheet.Range("A2:A200").NumberFormat = "#,##0.00"

if you don't know the maximum row number, you can use something like:

lngLastRow = xlSheet.Range("A2").End(-4121).row
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Create a query that formats your data as needed, and then export that query instead of the table.
1
 
NorieVBA ExpertCommented:
Which fields/columns do you need to format?

By the way, you shouldn't use both CreateObject and GetObject.

If the workbook you've exported to is open use GetObject, if it isn't use CreateObject followed by Workbooks.Open to open it.
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott PalmerData AnalystAuthor Commented:
Thanks Dale.

Scott
0
 
Dale FyeCommented:
Scott,

If that worked for you, don't forget to close your question by selecting the response or responses that helped you solve your problem
0
 
Dale FyeCommented:
agree with Norie,

You should probably replace:

Set xlBook = GetObject(filePath)

with

Set xlBook = xl.workbooks.open(filepath)
0
 
Scott PalmerData AnalystAuthor Commented:
Sorry, that I had done that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.