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

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

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

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