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 PalmerReprting AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
NorieAnalyst Assistant Commented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott PalmerReprting AnalystAuthor Commented:
Thanks Dale.

Scott
0
Dale FyeOwner, Developing Solutions LLCCommented:
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 FyeOwner, Developing Solutions LLCCommented:
agree with Norie,

You should probably replace:

Set xlBook = GetObject(filePath)

with

Set xlBook = xl.workbooks.open(filepath)
0
Scott PalmerReprting AnalystAuthor Commented:
Sorry, that I had done that.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.