I want to know if there is a way to set multiple files to open, refresh,
save, and close. I have 38 files that need to refresh when I update my
Access database.

#1 We've set the files to NOT refresh upon opening for control purposes.
Evidently there's been problems with this in the past.
#2 I've already tried a macro but when I run it Excel tries to save before
the refresh is complete.
#3 So basically I want a "go button", something I can kick-off and it will
go through all 38 files and do the refreshes. I know a little SQL and my
co-worker knows a little VBA.

Thanks in advance for your help.

What query? I've already run the queries in my Access database and I just
want to refresh all my Excel files.

"K Dales" wrote:

The querytable (the result range of the query) has a property .Refreshing
that can let you know if the query is still running or is done. So you
should be able to incorporate this into your macro to make sure the query is
done before moving on to save the file.

Assuming you are looping somehow through your files, the code could contain
lines like the ones below:

Dim MyQuery as QueryTable
' Loop through files....
With ThisWorkbook.Sheets("QuerySheet").QueryTables(1)
.Refresh
While .Refreshing
DoEvents
WEnd
' Save and close the workbook, then next file...

One added suggestion: I hate to just leave a loop waiting for some process
to finish (what if I lose the network connection, or something else prevents
the query from finishing...) so I often set an end time as a timeout, then
use it as part of the loop:

Timeout = Now()+Timevalue("00:15:00")
While Refreshing and Now()<=Timeout
...

HTH

"Vanessa" wrote:

I want to know if there is a way to set multiple files to open, refresh,
save, and close. I have 38 files that need to refresh when I update my
Access database.

#1 We've set the files to NOT refresh upon opening for control purposes.
Evidently there's been problems with this in the past.
#2 I've already tried a macro but when I run it Excel tries to save before
the refresh is complete.
#3 So basically I want a "go button", something I can kick-off and it will
go through all 38 files and do the refreshes. I know a little SQL and my
co-worker knows a little VBA.

Thanks in advance for your help.

The querytable (the result range of the query) has a property .Refreshing
that can let you know if the query is still running or is done. So you
should be able to incorporate this into your macro to make sure the query is
done before moving on to save the file.

Assuming you are looping somehow through your files, the code could contain
lines like the ones below:

Dim MyQuery as QueryTable
' Loop through files....
With ThisWorkbook.Sheets("QuerySheet").QueryTables(1)
.Refresh
While .Refreshing
DoEvents
WEnd
' Save and close the workbook, then next file...

One added suggestion: I hate to just leave a loop waiting for some process
to finish (what if I lose the network connection, or something else prevents
the query from finishing...) so I often set an end time as a timeout, then
use it as part of the loop:

Timeout = Now()+Timevalue("00:15:00")
While Refreshing and Now()<=Timeout
...

HTH

"Vanessa" wrote:

I want to know if there is a way to set multiple files to open, refresh,
save, and close. I have 38 files that need to refresh when I update my
Access database.

#1 We've set the files to NOT refresh upon opening for control purposes.
Evidently there's been problems with this in the past.
#2 I've already tried a macro but when I run it Excel tries to save before
the refresh is complete.
#3 So basically I want a "go button", something I can kick-off and it will
go through all 38 files and do the refreshes. I know a little SQL and my
co-worker knows a little VBA.

Thanks in advance for your help.