Open consecutive files causes a failure (from vba)

[adammsu1983]

Any ideas? When i open (and subsequently close) too many files in a row, it fails.

Specifically, after having opened and closed 4 files, it fails on the 5th.

any help?

-Adam

[john456852]

fix your computer
clear some memory from the task manager
permanately delete some things from control panel

[adammsu1983]

is that just sarcasm? they are small file, like 50 kb.

I wasn't clear:
I have a file that is using VBA to open and close other files. after the 4th one, it fails.

[Andrew-R]

Does it give an error message of any sort?

[adammsu1983]

it simply gives an error:
Could not open ftp://XXXXX/file.xlsx

the code is simple also:

 FileToOpen = "ftp://anonymous@SERVERNAME/" & CurrentPage & ".xlsx"
 Application.Workbooks.Open (FileToOpen)

  BLAH BLAH BLAH

Windows(CurrentPage & ".xlsx").Activate
ActiveWorkbook.Close False

i use a for loop to go thru a list, that changes the "CurrentPage" variable above. It took me a sec to realize it, but it always fails after it loops 4 times.

Also, as a side note, i looked in the FTP logs, it doesn't even hit the server... so it seems it fails before it even tries the 5th time

[Andrew-R]

Bit of a puzzler - if you download the first 5 files to a local drive and try to open them from there then does it still fail?

[adammsu1983]

good idea. maybe i can download the files into a temp directory and open them from there. i'll give it a try.

[adammsu1983]

so i thought i could find code to copy the files over simply, but i am unable. the code i have tried works for copying files on my PC, but won't use the FTP file locations.

i tried successfully on my local :

Sub Copy_One_File()
    FileCopy "C:\Users\XXXX\Test.xls", "C:\Users\XXXX\destination\Test.xls"
End Sub

any ideas for a code that would work with an FTP file location?

---------- Post added at 02:05 PM ---------- Previous post was at 01:57 PM ----------

As a test, i just copied them directly to my folder, and ran it that way.... Same result, opens the 1st few and then fails on the 4th or 5th. Sad thing is that I can pull 4 or 5 no problem, and then 4 or 5 more without issue, as long as i start the macro over again. It almost seems like some sort of cache thing, but i can't locate anything on that (assuming that is the issue)

---------- Post added at 02:14 PM ---------- Previous post was at 02:05 PM ----------

this is gonna drive me nuts. without doing anything really, it now will go through 15 loops or so before failure.... even on the FTP side.

it definitely appears to be an excel bug at this point... PITA!

[adammsu1983]

So on a whim, i added

ON error resume next

and it will continue through the loops after the "phantom error". So what i have done now is rigged the loop to write a "1" to a cell if the file open was successful. When i get the "phantom error" it simply sees that the "success flag" isn't set and backs up one. its a pain since i have to hit the "ok" button like 10 times to get thru the loops, but at least it finishes....