Refresh and run Excel macro

N

nafflerbach

I have a table of data that changes daily and I need to update and save a
linked Excel spreadsheet with the new data. The spreadsheet is complex with
multiple tabs and formatting and therefore I cannot just export the table and
save. What I need to do from Access is open the Excel file, Refresh All and
then run one additional Excel Macro. Here is what I have so far but it fails
at the macro:

Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Workbooks.Open "\\File Name.xls"
objExcel.Application.Visible = True
‘Need to refresh data before macro runs, not able to refresh on open.
objExcel.Run "File Name.xls!RefreshMacro Name"
objExcel.Run "File Name.xls!Macro Name"
objExcel.Quit

Can anyone help?
 
K

Ken Snell

If you truly have spaces in the filename and macro name, then you need to
delimit those names with ' characters:

objExcel.Run "'File Name.xls'!'RefreshMacro Name'"
 
N

nafflerbach

Thanks Ken...There are no spaces in the actual file or macro names. So the
Excel file opens and the following message appears "This action will cancel a
pending Refresh Data command. Continue?
 
K

Ken Snell

Do you have some VBA code / macro running on the workbook's Open event? or
some other workbook event? Do you have formulas in the worksheet that are
being recalculated/refreshed upon opening?
 
N

nafflerbach

None that I am aware of. I also turned the calcultions to manual. Still no
good.
 
K

Ken Snell

What is the security setting for macros on the EXCEL application where
you're running the code? Does the file ask if the macros should be trusted
when the file opens?
 
D

De Jager

nafflerbach said:
I have a table of data that changes daily and I need to update and save a
linked Excel spreadsheet with the new data. The spreadsheet is complex
with
multiple tabs and formatting and therefore I cannot just export the table
and
save. What I need to do from Access is open the Excel file, Refresh All
and
then run one additional Excel Macro. Here is what I have so far but it
fails
at the macro:

Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Workbooks.Open "\\File Name.xls"
objExcel.Application.Visible = True
‘Need to refresh data before macro runs, not able to refresh on open.
objExcel.Run "File Name.xls!RefreshMacro Name"
objExcel.Run "File Name.xls!Macro Name"
objExcel.Quit

Can anyone help?
 
J

joelgeraldine

jjkjjkjkj

Ken Snell said:
Do you have some VBA code / macro running on the workbook's Open event? or
some other workbook event? Do you have formulas in the worksheet that are
being recalculated/refreshed upon opening?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top