FileSystemObject.GetFile Error "File Not Found" in Excel 2007

Morning!

I'm working on a macro that opens up all the Excel files within a directory, then copies and pastes specific cells into another worksheet which runs a statistical macro on the data. The copy/paste macro works fine in 2003, but not in 2007. It crashes during a loop using FileSystemObject.GetFile(filename) which is storing the file names into an array. The error it gives is Run Time 53 File Not Found, but it looks like it is grabbing the file name and trying to pass it as Nothing into the FSO.

Code:

Please Login or Register  to view this content.

Crashes on Set myFile = FSO.GetFile(CStr(varFileList(x)))

Any light that could be shed on this would be greatly appreciated,

Carl

Is the filename for that file funny? That is have spaces or strange characters in it? Is the filename and path too long and overflowing some buffer? Does this file need to open another file to get it's data and the file-not-found the attached file? Is the file hidden or it's attribute in the filesystem different than the others? Is the file already open and can't be opened again? When you open the file, using Excel, by hand does it work? Is your code looking for a specific sheet and has this sheet been renamed? Is it a timing issue where Windows OS is ahead of Excel?

Try the above. If it is a timing issue, put a DoEvents in your code to insure things are completed before starting the next operations.

I'd be looking at that individual file, it's name and file attributes to see what the problem is. I'd rename the file and try again.

I've tried renaming the files to just 1, 2, etc.. with a short file path, same issue. So, it's not the name of it as far as I can tell. The workbooks are all closed, not hidden, the code isn't looking for a specific sheet. When you open it by hand a message box pops up saying that it is in a different format than the file extension and to click yes to open it, but that should be taken care of in the actual opening sequence of the macro.

The later parts of the macro will open up each workbook and copy data over, but it has yet to reach that part. The part it crashes on is just peeping into the folder and grabbing the names of all the .xls files and putting them into an array (just the names of them).

When you hover over the Set myFile in debug mode it says myFile = Nothing. The CStr(varFileList(x)) ="1.xls" So it is picking up something, but not passing it.

It works in the 2003 version, so maybe it is a difference in the way 2007 handles the .getfile?

Thanks,

Carl

Yep, you were right about that. Inserted the file path and it fixed that line.

Please Login or Register  to view this content.

Now I get a Type Mismatch on the myResults(x) = strFilePath line.

So, I tried changing myResults over to a string array the passing the strFilePath into it but that gives Subscript out of Range. Not really sure how it would be out of range on the first value since it is a dynamic array.

Think I got it!

Please Login or Register  to view this content.

Thanks for all the help!

Carl