problem with excel refreshing Via macro

P

pando

hello all

i have a master excel file that i wrote a macro that will open anther excel file
the second file has pivot tables and other tables link Via odbc to SQL server
the problem is that after i write "activeworkbook.refreshall
and then "activeworkbook.save " i recieved a msgbox from excel as following
"This action will cancel a pendingrefresh data command do u what to continue? yes /no.

i know how to disable the msgbox but it does not refresg the info if i press yes or no
the strange thing is ehn i run a local macro on the file it works

I think the problem happen when i do "activeworkbook.save

i looked up the excel help
"When I click the Refresh button, a message tells me that my macro changes will be lost
This message appears when you have made changes to a macro in the Visual Basic Editor and have also changed the copy of your workbook in the Microsoft Script Editor

To save the changes to your macro, do the following:
Click No
Switch to the Visual Basic Editor window
Export any modules you have changed.(how do i do it
In the workbook, click Refresh on the Refresh toolbar
Click Yes
Import the modules to restore the changes to your macro. .(how do i do it

please answer me ASAP

Thanks in advance
 
R

Rob van Gelder

Sounds like your refresh is running asynchronously (background refresh)
Try playing the the "background refresh" option for your data sources.

If you lookup "RefreshAll" in help, you'll see what I'm referring to.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


pando said:
hello all,

i have a master excel file that i wrote a macro that will open anther excel file.
the second file has pivot tables and other tables link Via odbc to SQL server.
the problem is that after i write "activeworkbook.refreshall"
and then "activeworkbook.save " i recieved a msgbox from excel as following :
"This action will cancel a pendingrefresh data command do u what to continue? yes /no."

i know how to disable the msgbox but it does not refresg the info if i press yes or no.
the strange thing is ehn i run a local macro on the file it works.

I think the problem happen when i do "activeworkbook.save "

i looked up the excel help :
"When I click the Refresh button, a message tells me that my macro changes will be lost.
This message appears when you have made changes to a macro in the Visual
Click to expand...
Basic Editor and have also changed the copy of your workbook in the
Microsoft Script Editor.
 
T

Tom Ogilvy

Actually, refreshall makes no mention of the backgroundquery setting. It is
an argument to the refresh method for both the querytable and pivottable
objects if you wanted to loop through your tables to refresh them, or it is
a parameter for the querytable and pivottable objects which you can set so
you can continue to use refreshall. As Rob suggested, probably currently
set to false.
 
R

Rob van Gelder

In my version (XL2000), the help for RefreshAll is as follows:
Remarks
Objects that have the BackgroundQuery property set to True are refreshed in
the background.

Is this different in later / earlier versions?
 
T

Tom Ogilvy

From Excel 97 for RefreshAll

Refreshes all external data ranges and PivotTables in the workbook.

Syntax

expression.RefreshAll

expression Required. An expression that returns a Workbook object.

Remarks

The refresh order is undefined.
 
Top