Macros to Open & Update Files
 

I have about 100 excel spreadsheets that need to be opened and updated on a
regular basis. The update function takes about 30 seconds or so.

Here's what I have:

ChDir "\\DISK 1\Groups"
Workbooks.Open Filename:= _
"\\DISK 1\Groups\FILE1.xls", UpdateLinks:=3
ActiveWorkbook.Save
ActiveWorkbook.Close

It seems that this Macro is not working due to the fact that it tries to
save BEFORE the update actually completes. And therefore gets help up &
stops (never gets to Close) Basically what I want to do is have it open the
file, update the file, close & the proceed to the next file I specify.
[Note: THe code I got was from using the Record Macro function & opening,
updating, saving & closing the file]

Any help would be greatly appreciated.

Regards,
Diane

Macros to Open & Update Files
 

hi, you can try pauseing the macro code untill the update
is done
Application.Wait (Now() + TimeValue("0:00:02"))
I don't know how long. the above example is a 2 second
pause. you may have to play with the numbers.
good luck
-----Original Message-----
I have about 100 excel spreadsheets that need to be
opened and updated on a
regular basis. The update function takes about 30
seconds or so.

Here's what I have:

ChDir "\\DISK 1\Groups"
Workbooks.Open Filename:= _
"\\DISK 1\Groups\FILE1.xls", UpdateLinks:=3
ActiveWorkbook.Save
ActiveWorkbook.Close

It seems that this Macro is not working due to the fact
that it tries to
save BEFORE the update actually completes. And therefore
gets help up &
stops (never gets to Close) Basically what I want to do
is have it open the
file, update the file, close & the proceed to the next
file I specify.
[Note: THe code I got was from using the Record Macro
function & opening,
updating, saving & closing the file]

Any help would be greatly appreciated.

Regards,
Diane
.

Macros to Open & Update Files
 

I thought of that too, but I would be making a guess as to how long an update
would actually take - sometimes it's 10 seconds ... sometimes it could be 45
depending on traffic & file size. I was hoping for a more reliable solution
(if there is one ;-)

" wrote:

hi, you can try pauseing the macro code untill the update
is done
Application.Wait (Now() + TimeValue("0:00:02"))
I don't know how long. the above example is a 2 second
pause. you may have to play with the numbers.
good luck
-----Original Message-----
I have about 100 excel spreadsheets that need to be
opened and updated on a
regular basis. The update function takes about 30
seconds or so.

Here's what I have:

ChDir "\\DISK 1\Groups"
Workbooks.Open Filename:= _
"\\DISK 1\Groups\FILE1.xls", UpdateLinks:=3
ActiveWorkbook.Save
ActiveWorkbook.Close

It seems that this Macro is not working due to the fact
that it tries to
save BEFORE the update actually completes. And therefore
gets help up &
stops (never gets to Close) Basically what I want to do
is have it open the
file, update the file, close & the proceed to the next
file I specify.
[Note: THe code I got was from using the Record Macro
function & opening,
updating, saving & closing the file]

Any help would be greatly appreciated.

Regards,
Diane
.

Macros to Open & Update Files
 

Diane, try adding a DoEvents, before the save.

Greg
"Diane Alsing" wrote in message
...
I thought of that too, but I would be making a guess as to how long an
update
would actually take - sometimes it's 10 seconds ... sometimes it could be
45
depending on traffic & file size. I was hoping for a more reliable
solution
(if there is one ;-)

" wrote:

hi, you can try pauseing the macro code untill the update
is done
Application.Wait (Now() + TimeValue("0:00:02"))
I don't know how long. the above example is a 2 second
pause. you may have to play with the numbers.
good luck
-----Original Message-----
I have about 100 excel spreadsheets that need to be
opened and updated on a
regular basis. The update function takes about 30
seconds or so.

Here's what I have:

ChDir "\\DISK 1\Groups"
Workbooks.Open Filename:= _
"\\DISK 1\Groups\FILE1.xls", UpdateLinks:=3
ActiveWorkbook.Save
ActiveWorkbook.Close

It seems that this Macro is not working due to the fact
that it tries to
save BEFORE the update actually completes. And therefore
gets help up &
stops (never gets to Close) Basically what I want to do
is have it open the
file, update the file, close & the proceed to the next
file I specify.
[Note: THe code I got was from using the Record Macro
function & opening,
updating, saving & closing the file]

Any help would be greatly appreciated.

Regards,
Diane
.

Macros to Open & Update Files
 

Try
http://www.rondebruin.nl/copy4.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Diane Alsing" wrote in message ...
I have about 100 excel spreadsheets that need to be opened and updated on a
regular basis. The update function takes about 30 seconds or so.

Here's what I have:

ChDir "\\DISK 1\Groups"
Workbooks.Open Filename:= _
"\\DISK 1\Groups\FILE1.xls", UpdateLinks:=3
ActiveWorkbook.Save
ActiveWorkbook.Close

It seems that this Macro is not working due to the fact that it tries to
save BEFORE the update actually completes. And therefore gets help up &
stops (never gets to Close) Basically what I want to do is have it open the
file, update the file, close & the proceed to the next file I specify.
[Note: THe code I got was from using the Record Macro function & opening,
updating, saving & closing the file]

Any help would be greatly appreciated.

Regards,
Diane