Macros to Open & Update Files

[Guest]

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

 

[Guest]

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

 

[Guest]

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 ;-)

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
.

 

[GregR]

Diane, try adding a DoEvents, before the save.

Greg

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 ;-)

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
.

 

[Ron de Bruin]

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