By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Export from Access to Excel and Run Macro already saved

P: 19
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"

Expand|Select|Wrap|Line Numbers
  1. Private Sub OpenExcelExport()
  2. Dim appExcel As Excel.Application
  3. Dim appWB As Excel.Workbook
  4. Set appExcel = GetObject("My Excel File")
  5. With appExcel
  6. .Visible = True
  7. Columns("D:F").Select
  8.     Selection.Copy
  9.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
  10.         :=False, Transpose:=False
  11.     Application.CutCopyMode = False
  12.     Selection.Style = "Currency"
  13.     Rows("1:1").Select
  14.     Selection.Delete Shift:=xlUp
  15.     Range("A1:A3").Select
  16.     Selection.Font.Bold = True
  17.     Range("A5:F5").Select
  18.     Selection.Font.Bold = True
  19.     Cells.Select
  20.     Cells.EntireColumn.AutoFit
  21.     Range("D5").Select
  22.     Selection.End(xlDown).Select
  23.     ActiveCell.Offset(2, 0).Activate
  24.     Set yRng = Range("D5", Selection.Offset(1, 0))
  25.     Selection = WorksheetFunction.Sum(yRng)
  26.     Selection.Font.Bold = True
  27.         Range("E5").Select
  28.     Selection.End(xlDown).Select
  29.     ActiveCell.Offset(2, 0).Activate
  30.     Set yRng = Range("E5", Selection.Offset(1, 0))
  31.     Selection = WorksheetFunction.Sum(yRng)
  32.     Selection.Font.Bold = True
  33.         Range("F5").Select
  34.     Selection.End(xlDown).Select
  35.     ActiveCell.Offset(2, 0).Activate
  36.     Set yRng = Range("F5", Selection.Offset(1, 0))
  37.     Selection = WorksheetFunction.Sum(yRng)
  38.     Selection.Font.Bold = True
  39.     Range("A5").Select
  40.     Cells.Select
  41.     Cells.EntireColumn.AutoFit
  42. End Sub
Aug 13 '10 #1

✓ answered by 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.

Share this Question
Share on Google+
1 Reply


P: 1
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.
Aug 13 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.