Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Network
Hi,
I have this code ' Load Three sheets iFilesUsedCounter = 0 Do While iFilesUsedCounter < 9 ' Open AFMA WorkBook sFileName = AFMAFiles(iFileCounter).sFileName ActiveWorkbook.Save 'Close and save Averaging file Workbooks.Open sAFMAPathName & sFileName iFilesUsedCounter = iFilesUsedCounter + 3 Windows(sFileName).Activate Application.CutCopyMode = False 'Clear Clipboard ActiveWindow.Close SaveChanges:=False 'Close without saving AFMA file iFileCounter = iFileCounter - 1 Loop Which works on a good day. If the network is running slow is get a 'Unable to Open file' or 'The file is corrupt & Cannot be opened' Is their a way to check if the file is fully open before closing it and checking if the file is fully closed before opening the next one Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Network
I'm not sure if this will work or not - but when you close the active window,
the name of the active window is going to change. So you may be able to use code like this after the .Close statement (2nd from last) Do While ActiveWorkbook.Name = sFileName DoEvents Loop "MarkS" wrote: Hi, I have this code ' Load Three sheets iFilesUsedCounter = 0 Do While iFilesUsedCounter < 9 ' Open AFMA WorkBook sFileName = AFMAFiles(iFileCounter).sFileName ActiveWorkbook.Save 'Close and save Averaging file Workbooks.Open sAFMAPathName & sFileName iFilesUsedCounter = iFilesUsedCounter + 3 Windows(sFileName).Activate Application.CutCopyMode = False 'Clear Clipboard ActiveWindow.Close SaveChanges:=False 'Close without saving AFMA file iFileCounter = iFileCounter - 1 Loop Which works on a good day. If the network is running slow is get a 'Unable to Open file' or 'The file is corrupt & Cannot be opened' Is their a way to check if the file is fully open before closing it and checking if the file is fully closed before opening the next one Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Network
Hi
been having lots of other problems, but I've got back to it. I've added this to the code ' Close WorkBook with out saveing Windows(sFileName).Activate Application.CutCopyMode = False 'Clear Clipboard Do While ActiveWorkbook.Name < sFileName DoEvents Loop ActiveWindow.Close SaveChanges:=False Do While ActiveWorkbook.Name < ThisWorkbook.Name DoEvents Loop ' Update pointers iFilesUsedCounter = iFilesUsedCounter + 1 iFileCounter = iFileCounter - 1 And it has fixed my problem Thanks MarkS "JLatham" wrote: I'm not sure if this will work or not - but when you close the active window, the name of the active window is going to change. So you may be able to use code like this after the .Close statement (2nd from last) Do While ActiveWorkbook.Name = sFileName DoEvents Loop "MarkS" wrote: Hi, I have this code ' Load Three sheets iFilesUsedCounter = 0 Do While iFilesUsedCounter < 9 ' Open AFMA WorkBook sFileName = AFMAFiles(iFileCounter).sFileName ActiveWorkbook.Save 'Close and save Averaging file Workbooks.Open sAFMAPathName & sFileName iFilesUsedCounter = iFilesUsedCounter + 3 Windows(sFileName).Activate Application.CutCopyMode = False 'Clear Clipboard ActiveWindow.Close SaveChanges:=False 'Close without saving AFMA file iFileCounter = iFileCounter - 1 Loop Which works on a good day. If the network is running slow is get a 'Unable to Open file' or 'The file is corrupt & Cannot be opened' Is their a way to check if the file is fully open before closing it and checking if the file is fully closed before opening the next one Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Network
Glad it's working for you now. Your solution also brings to mind another
possible way of doing it: you could count the number of workbooks that are open (use Workbooks.Count) before doing the save/close and use a Do While loop that loops until Workbooks.Count < equal the earlier number. Often there are several ways to accomplish a task in Excel. "MarkS" wrote: Hi been having lots of other problems, but I've got back to it. I've added this to the code ' Close WorkBook with out saveing Windows(sFileName).Activate Application.CutCopyMode = False 'Clear Clipboard Do While ActiveWorkbook.Name < sFileName DoEvents Loop ActiveWindow.Close SaveChanges:=False Do While ActiveWorkbook.Name < ThisWorkbook.Name DoEvents Loop ' Update pointers iFilesUsedCounter = iFilesUsedCounter + 1 iFileCounter = iFileCounter - 1 And it has fixed my problem Thanks MarkS "JLatham" wrote: I'm not sure if this will work or not - but when you close the active window, the name of the active window is going to change. So you may be able to use code like this after the .Close statement (2nd from last) Do While ActiveWorkbook.Name = sFileName DoEvents Loop "MarkS" wrote: Hi, I have this code ' Load Three sheets iFilesUsedCounter = 0 Do While iFilesUsedCounter < 9 ' Open AFMA WorkBook sFileName = AFMAFiles(iFileCounter).sFileName ActiveWorkbook.Save 'Close and save Averaging file Workbooks.Open sAFMAPathName & sFileName iFilesUsedCounter = iFilesUsedCounter + 3 Windows(sFileName).Activate Application.CutCopyMode = False 'Clear Clipboard ActiveWindow.Close SaveChanges:=False 'Close without saving AFMA file iFileCounter = iFileCounter - 1 Loop Which works on a good day. If the network is running slow is get a 'Unable to Open file' or 'The file is corrupt & Cannot be opened' Is their a way to check if the file is fully open before closing it and checking if the file is fully closed before opening the next one Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL SLOW - USING NETWORK ON SELECT FUNCTIONS | Excel Discussion (Misc queries) | |||
Excel slow to save to network drive | Excel Discussion (Misc queries) | |||
How to speed up slow network access in office 2003 | Excel Discussion (Misc queries) | |||
Opening files from Network resource extremely slow | Excel Discussion (Misc queries) | |||
Excel Slow saving across Network. | Excel Discussion (Misc queries) |