On Error - Skip When File Not Found

[redbaron06]

Hi all,

I have been tinkering with this code for a few weeks now trying to get an On Error script that works for the macros to continue when a file is not found (w/o a error mesage).

Basically the code opens a closed workbook, saves it in a new format and copies from that new format to the active worksheet. Many times, the file, purposely, will be absent. I would like both modules to skip, but for some reason I keep sending it into a loop and/or freezing excel. There are 72 modules, most of them simlar to the ones below, that are called on when the file parameters change.

Thanks in advance!

-Terry

First Module: tried replacing with
On Error GoTo 0
Exit Sub
InvalidInput:
Resume Next
End Sub

Sub DoGetData8()
Dim strFile As String
Dim strRange As String
strFile = Range("B51").Value
GetDataFromClosedWorkbook strFile, "A4:N200", Range("A1600"), True
End Sub
'
Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange As String, _
    TargetRange As Range, IncludeFieldNames As Boolean)
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
Dim TargetCell As Range, i As Integer
    dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};" & _
        "ReadOnly=1;DBQ=" & SourceFile
    Set dbConnection = New ADODB.Connection
   [COLOR=darkred]On Error GoTo InvalidInput[/COLOR]
    dbConnection.Open dbConnectionString ' open the database connection
    Set rs = dbConnection.Execute("[" & SourceRange & "]")
    Set TargetCell = TargetRange.Cells(1, 1)
    If IncludeFieldNames Then
        For i = 0 To rs.Fields.Count - 1
            TargetCell.Offset(0, i).Formula = rs.Fields(i).Name
        Next i
        Set TargetCell = TargetCell.Offset(1, 0)
    End If
    TargetCell.CopyFromRecordset rs
    rs.Close
    dbConnection.Close ' close the database connection
    Set TargetCell = Nothing
    Set rs = Nothing
    Set dbConnection = Nothing
    [COLOR=darkred]On Error GoTo 0[/COLOR]
[COLOR=darkred]   Exit Sub[/COLOR]
[COLOR=darkred]InvalidInput:[/COLOR]
[COLOR=darkred]   MsgBox "The source file or source range is invalid!", _[/COLOR]
[COLOR=darkred]       vbExclamation, "Get data from closed workbook"[/COLOR]
End Sub

Second Module:

Tried to cheat and put On Error GoTo ErrHandler3: at the top of the code and then
End Sub
ErrHandler3:
Resume Next
End Sub
at the bottom. To no avail.

Sub SaveTheFile15()
TangoAlpha = Range("B58").Value
TangoDelta = Range("E58").Value
If Range("I58").Value <> "" Then
Workbooks.Open Filename:=Range("I58").Value
Workbooks(TangoDelta).Activate
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs TangoAlpha, FileFormat:=56
Application.DisplayAlerts = True
ActiveWorkbook.Close
Else: End If
End Sub

Thanks!

 

[Richard Schollar]

Hi

Just use Dir to test the presence of a file - in simple terms:

Dim strFile As String
 
strFile = Dir("C:\Somefolder\SomeFile.xls")
 
If strFile = "" Then  
  'file not found - do something
Else
  'file found - run macros
End if

 

[redbaron06]

Thanks for the advice Richard.

Do you think this might work, as the cell value of B46 is the directory and it is subject to change? I am not sure if I need to tell it to do something other then end sub?

Dim strFile As String
Dim strRange As String
strFile = Dir(Range("B46").Value)
If strFile = "" Then
[COLOR=darkred]End Sub
Else
[/COLOR]GetDataFromClosedWorkbook strFile, "A4:N200", Range("A600"), True
End If
End Sub

 

[Richard Schollar]

You can only have one End Sub per macro so try doing it like this:

Dim strFile As String
Dim strRange As String
strFile = Dir(Range("B46").Value)
If strFile = "" Then Exit Sub

'assuming file exists, next line will be executed:
GetDataFromClosedWorkbook strFile, "A4:N200", Range("A600"), True
End Sub

 

[redbaron06]

Works great! Thank you so much for the help!