Export report to excel then format it

[nosferatu26]

Hello,

I have been searching online for a while now and and im coming up dry.
I have a button in my report that when clicked, exports that report to excel and opens the file.

Here is the code for the button:

 Dim strWorkSheetPath As String
    strWorkSheetPath = "C:\Users\" & GetUserName() & "\Desktop"
    strWorkSheetPath = strWorkSheetPath & "acbPartLists.xls"
    DoCmd.OutputTo acOutputReport, "Active ACB Part Lists", acFormatXLS, strWorkSheetPath, -1

This works but ideally I want the excel sheet to be formatted a certain way to look nicer. I Then went into the excel sheet and recorded a macro of what I wanted to do for the time being and it looks like this:

 Cells.Select
    ActiveWindow.SmallScroll Down:=0
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    Range("A3").Select
    Rows("2:2").RowHeight = 63.75
    Range("A2:F2").Select
    Selection.Font.Bold = True
    With Selection.Font
        .Name = "Arial"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("B7").Select

What I cant figure out is how to automatically apply code like that to the excel sheet after exporting it from access. I am relatively new to doing things like this and if anyone can help me out it would be greatly appreciated!

 

[GinaWhipp]

Take a look at...
http://btabdevelopment.com/export-a-table-or-query-to-excel/

You will see the formatting portion it should assist in adjusting your code.

Side note, if it's always the same why not just create a Template then export to the Template and copy and rename the file. That way no formatting needed the Template will remain to be reused.

 

[sneuberg]

The code in the reference provide by GinaWhipp shows some ways to format the cell but does not open an existing excel file which I believe you will have to do.

https://bytes.com/topic/access/answers/770441-format-excel-document-access shows code that open a file. This with GinaWhips link should get you started.