Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update link cells without open up source document files | Excel Worksheet Functions | |||
How to open a list of excel files and get update, then close it? | Excel Discussion (Misc queries) | |||
Can't Open Excel Files with Macros | Excel Discussion (Misc queries) | |||
Open workbook-macros enabled, opening another with macros | Excel Programming | |||
Macro Size Limit / open macros with macros? | Excel Programming |