Unable to read file

[Guest]

Hi,
In VBA I have this line
Workbooks.Open sAFMAPathName & sFileName

when in auto it gives a error message 'Unable to read file' when I single
step (F8), it works fine.
This section of the code comes from another excel VBA code block which works
fine, if a bit slow.

Thanks

 

[Tim Williams]

If it works fine when stepping through then most likely there is some delay
in creating or releasing the file you're trying to open.
Where does it come from ?

Tim

 

[Guest]

Hi Tim,
from a remote server, but some work fine and others don't

MarkS

 

[Tim Williams]

Do the files which give problems fail consistently ?

Tim

 

[Guest]

Yes Its always the same ones but opening them and resaving them doesn't help

 

[Guest]

No thats no right, The first one always loads fine and the next one has the
problem

 

[Tim Williams]

Without seeing the code it's hard to say exactly what the problem might be.
Try putting a small delay before opening each file and see if that fixes it.

Tim.

 

[Guest]

Hi Tim,
Have tried a 10 second delay no improvement

Do While iFilesUsedCounter <> 9
' Open AFMA WorkBook
sFileName = AFMAFiles(iFileCounter).sFileName
Debug.Print sAFMAPathName & sFileName
Workbooks.Open sAFMAPathName & sFileName
Cells.Select
Selection.Copy
ThisWorkbook.Activate
Sheets("AFMA Loader").Select
Cells.Select
ActiveSheet.Paste
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows(sFileName).Activate
Application.CutCopyMode = False 'Clear Clipboard
ActiveWindow.Close saveChanges:=False

some other stuff to put it in the right place do it again until we have a
complete set

 

[Tim Williams]

If you comment out all the copy/paste stuff can you just open and close the
files in a loop ?
Might help to simplify the situation for debugging.

You do have two different counters, right? iFilesUsedCounter and
iFileCounter ?


Tim