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

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

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.


--
Regards,
Tom Ogilvy


"Rob van Gelder" wrote in message
...
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" wrote in message
...
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,