Export from Access to Excel and Run Macro already saved

[epifinygirl]

I am new to VBA writing so bear with me here. I have been searching the web but I keep finding answers in pieces which is more confusing!

What I am trying to do is export a table from Access to Excel and run a macro (it's a formatting macro). My issue is that when Access exports the table, it resaves it and I lose the macro (The spreadsheet will be accessible to anyone so I can't just save it in the Personal workbook). When I try to copy the VBA from the macro to Access, I receive an error saying "Compile Error: Method or Data Not Found"

Private Sub OpenExcelExport()
Dim appExcel As Excel.Application
Dim appWB As Excel.Workbook
Set appExcel = GetObject("My Excel File")
With appExcel
.Visible = True
Columns("D:F").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Style = "Currency"
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Range("A1:A3").Select
    Selection.Font.Bold = True
    Range("A5:F5").Select
    Selection.Font.Bold = True
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("D5").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(2, 0).Activate
    Set yRng = Range("D5", Selection.Offset(1, 0))
    Selection = WorksheetFunction.Sum(yRng)
    Selection.Font.Bold = True
        Range("E5").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(2, 0).Activate
    Set yRng = Range("E5", Selection.Offset(1, 0))
    Selection = WorksheetFunction.Sum(yRng)
    Selection.Font.Bold = True
        Range("F5").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(2, 0).Activate
    Set yRng = Range("F5", Selection.Offset(1, 0))
    Selection = WorksheetFunction.Sum(yRng)
    Selection.Font.Bold = True
    Range("A5").Select
    Cells.Select
    Cells.EntireColumn.AutoFit
End Sub

[Tim Botsford]

As you have found, Access overwrites the previous file when you export each time.
The Personal.wks would have been the right place to store the macro, BUT as you indicate, this is NOT the right place for 'workgroup' macros.
But use that idea and make a workbook as a 'workgroup' file (so no one can change it, or make it read-only). Save it in a shared folder that everyone that needs this macro has access. Save the macro in that workbook.
Then each person simply has to have that file open when needing to run the macro. If you assign a shortcut key sequence, they can execute that macro.
If you/they need this worksheet open at all times, their Excel autostart settings could cause it to open when starting Excel.
Just some ideas. Maybe others exist.