Provide an error message in macro if file not found.

[kylesom]

I have a bit of VBA that copys a range of data in one worksheet, opens another worksheet at a fixed network location, and pastes the data.

I've had a couple of instances where users have tried to use the macro outside of the network, and so get VBA errors.

How can I add a check at the start of the code to look to see if the file location exists, and return an error message if it doesnt?

The bit of code I use to open the network worksheet, paste data in, and close it is as follows:

'Specify Database Location!
        Set JDB = Workbooks.Open("R:\_Admin\Timesheets\Database\Timesheet Swap File {DO NOT MOVE}.xls")
    
            With JDB.Worksheets("Input")
             .Range("A" & .Range("A" & Rows.Count).End(xlUp).Offset(1).Row).PasteSpecial Paste:=xlValues
            End With
            
        JDB.Save
        JDB.Close
        
        Set JDB = Nothing

 

[JackDanIce]

Include the lines in red to your macro and try:

'Specify Database Location!
On Error Resume Next
        Set JDB = Workbooks.Open("R:\_Admin\Timesheets\Database\Timesheet Swap File {DO NOT MOVE}.xls")
If JDB Is Nothing Then
  MsgBox "Incorrect location, file not found. Macro stopping"
  Exit Sub
End If
 
            With JDB.Worksheets("Input")
             .Range("A" & .Range("A" & Rows.Count).End(xlUp).Offset(1).Row).PasteSpecial Paste:=xlValues
            End With
 
        JDB.Close savechanges:=True 
        Set JDB = Nothing

 

[Ruddles]

You can test for the presence of a file:-

If Dir("R:\_Admin\Timesheets\Database\Timesheet Swap File {DO NOT MOVE}.xls")="" Then
[COLOR=black]  MsgBox "Incorrect location, file not found. Macro stopping"
  Exit Sub
End If
[/COLOR]

 

[kylesom]

Both worked great, thanks!