Refresh and run Excel macro

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'"

--

Ken Snell
http://www.accessmvp.com/KDSnell/



"nafflerbach" wrote in message
...
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?

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?

"Ken Snell" wrote:

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'"

--

Ken Snell
http://www.accessmvp.com/KDSnell/



"nafflerbach" wrote in message
...
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?


.

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?

--

Ken Snell
http://www.accessmvp.com/KDSnell/



"nafflerbach" wrote in message
...
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?

"Ken Snell" wrote:

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'"

--

Ken Snell
http://www.accessmvp.com/KDSnell/



"nafflerbach" wrote in message
...
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?


.

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

"Ken Snell" wrote:

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?

--

Ken Snell
http://www.accessmvp.com/KDSnell/



"nafflerbach" wrote in message
...
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?

"Ken Snell" wrote:

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'"

--

Ken Snell
http://www.accessmvp.com/KDSnell/



"nafflerbach" wrote in message
...
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?


.



.

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?

--

Ken Snell
http://www.accessmvp.com/KDSnell/


"nafflerbach" wrote in message
...
None that I am aware of. I also turned the calcultions to manual. Still
no
good.

"Ken Snell" wrote:

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?

--

Ken Snell
http://www.accessmvp.com/KDSnell/



"nafflerbach" wrote in message
...
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?

"Ken Snell" wrote:

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'"

--

Ken Snell
http://www.accessmvp.com/KDSnell/



"nafflerbach" wrote in message
...
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?


.



.

"nafflerbach" wrote in message
...
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?

jjkjjkjkj

"Ken Snell" a écrit dans le message de
groupe de discussion : ...
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?

--

Ken Snell
http://www.accessmvp.com/KDSnell/



"nafflerbach" wrote in message
...
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?

"Ken Snell" wrote:

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'"

--

Ken Snell
http://www.accessmvp.com/KDSnell/



"nafflerbach" wrote in message
...
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?


.