Gerald Higgins
Well-known Member
- Joined
- Mar 26, 2007
- Messages
- 9,258
Hi, I'm still trying to teach myself VBA, thanks in advance for any help.
I have an application where I need to open a number of files in sequence, copy data out of them, and paste that data into the main file.
Some of those files may not exist, and if that happens I want to store the file name in a text string which I can then present at the end to say "These files failed".
I hope to end up doing this as a loop, but before I get there, I'm just trying to make it work for a single file, and I'm having trouble with the error handling.
The code is essentially this (I've slimmed it down slightly to simplify for posting purposes)
What this is MEANT to do is -
- open another file (the file name is held in SourceFile)
- copy data in that other file
- paste that data into the main file
- close the other file without saving it
- move on to the next file, which I'll do later by adding a loop
- IF the source file does not exist, then go into the error handling part by storing the file name in FileError, and display a message box saying that.
- IF the source file DOES exist, the error handling part should be bypassed.
When I test this on a single file that does exist, all of this works, EXCEPT it enters the Error Handling part at the end, displaying the message box with the file name as if it did not exist, but it does exist and the data has been copied out of it.
What am I doing wrong ?
I'm guessing my OnError and Resume statements are in the wrong place, or maybe I'm doing something even more stupid.
Any ideas ?
Thanks again for any assistance.
I have an application where I need to open a number of files in sequence, copy data out of them, and paste that data into the main file.
Some of those files may not exist, and if that happens I want to store the file name in a text string which I can then present at the end to say "These files failed".
I hope to end up doing this as a loop, but before I get there, I'm just trying to make it work for a single file, and I'm having trouble with the error handling.
The code is essentially this (I've slimmed it down slightly to simplify for posting purposes)
Code:
On Error GoTo FileOpenError
Workbooks.Open Filename:=SourceFile
Range("B16:U115").Copy
Windows("[Filename].xlsm").Activate
Sheets("Data1").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(2).Activate
ActiveWorkbook.Close
GoTo FileOpenErrorBypass:
FileOpenError:
FileError = FileError & SourceFile & ". "
MsgBox ("File Error " & FileError)
Resume NextFile
FileOpenErrorBypass:
Resume NextFile
NextFile:
What this is MEANT to do is -
- open another file (the file name is held in SourceFile)
- copy data in that other file
- paste that data into the main file
- close the other file without saving it
- move on to the next file, which I'll do later by adding a loop
- IF the source file does not exist, then go into the error handling part by storing the file name in FileError, and display a message box saying that.
- IF the source file DOES exist, the error handling part should be bypassed.
When I test this on a single file that does exist, all of this works, EXCEPT it enters the Error Handling part at the end, displaying the message box with the file name as if it did not exist, but it does exist and the data has been copied out of it.
What am I doing wrong ?
I'm guessing my OnError and Resume statements are in the wrong place, or maybe I'm doing something even more stupid.
Any ideas ?
Thanks again for any assistance.