VBA On Error and opening files

[Gerald Higgins]

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)

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.

 

[Fluff]

I'd do it like

   Dim Wbk As Workbook
   
   Set Wbk = Nothing
   On Error Resume Next
   Set Wbk = Workbooks.Open(Filename:=SourceFile)
   On Error GoTo 0
   
   If Not Wbk Is Nothing Then
      Range("B16:U115").Copy

    Windows("[Filename].xlsm").Activate

    Sheets("Data1").Activate
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

      Wbk.Close False
   Else
      FileError = FileError & SourceFile & ". "
      MsgBox ("File Error " & FileError)
   End If

 

[RoryA]

I'd use Dir to test for the file before trying to open it.

 

[Gerald Higgins]

Fluff - thank you ! I'll have a go at that.
RoryA - thank you too ! How exactly would you " . . . use Dir to test for the file . . . " ?

 

[RoryA]

Something like:

   If Dir(Sourcefile) <> vbnullstring then
dim wb as workbook
set wb = 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

      wb.Close False

Else
   FileError = FileError & SourceFile & ". "
end if

 

[Gerald Higgins]

Thanks again guys, much appreciated !

Fluff - as far as I could tell there was no instruction to actually open the target file in your suggestion, so I got it to work like this

   On Error Resume Next

   Workbooks.Open Filename:=SourceFile

   Set Wbk = Workbooks.Open(Filename:=SourceFile)

This seems to work correctly for both cases, i.e. the target file existing, and the target file not existing.
If it exists, the file is opened, data is copied out, file is closed, and it moves on to the next file.
If it does not exist, the error handling section is activated, and then it moves on to the next file.
All good, thanks very much !

RoryA - I have not tried your's yet, but I aim to, thanks again for your suggestion.
Do you think it is materially different from Fluff's ?
I don't know enough about VBA to recognise this myself.

 

[Fluff]

The workbook is opened by the line of code directly under the line you added in red. I would recommend removing that line in red as it could very easily cause problems.

Rory's suggestion is a lot better as it checks to see if the file exists before trying to open it.

 

[Gerald Higgins]

Fluff, thanks, my mistake sorry !
I didn't appreciate that this line

   Set Wbk = Workbooks.Open(Filename:=SourceFile)

was actually opening the file.
I mean I thought it might be, but I was getting an error condition, but it turned out this was because the target file has worksheet protection on it, and I had forgotten to insert the line of code to turn it off

So this variation works as well . . .

   Set Wbk = Workbooks.Open(Filename:=SourceFile)
ActiveSheet.Unprotect [PASSWORD]

I'll try RoryA's solution as well, but in the meantime, I think I have at least one workable solution.

Thanks again !

 

[Fluff]

Glad it's working & thanks for the feedback