Refresh and run Excel macro

  • Thread starter Thread starter nafflerbach
  • Start date Start date
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?
 
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'"
 
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?
 
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?
 
None that I am aware of. I also turned the calcultions to manual. Still no
good.
 
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?
 
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?
Click to expand...
 
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?
Click to expand...
 
Back
Top