CODEWITHSUNDEEP
excelvbaerror-handling
XCELLGUY
XCELLGUY

Reputation: 319

Excel VBA Error handler cannot find file... how to skip it?

A file path is sent as a string to my code below from a program written in C# and my code below runs.

My code below looks up a different file path in column C of an excel workbook called "RT_CMM_Data_File_Paths.xlsx"

My code below throws an error when the file path in column C is mis-typed. Specifically, the file extension was mistyped as ".xlxs" instead of ".xlsx"

I basically don't want my program to ever stop. So I tried to make an error handler that will record the file path that caused the error and then close out my macro so that the C# program can send the next file path.

However, instead, my code displays a message box "cannot find the file path" or something... When I click ok... my program finishes (basically doing nothing else) and closes and the C# program successfully sends the next file path just like i need it to... I don't want to click ok... how do I fix this?

Sub RT_CMM_DATA_COMPILER(Path As String)

    Dim ArrNames
    Dim wkbTemp As Workbook
    Dim Table As Workbook

    ArrNames = Array("X-Axis", "Y-Axis", "Z-Axis", "Flatness", _
        "Length-X", "Length-Y", "Length-Z", "Length_X", "Length_Y", _
        "Length_Z", "Length", "Angle", "Angle-XY", "Angle-XZ", "Angle-YX", _
        "Angle-YZ", "Angle-ZX", "Angle-ZY", "Radius", "Diameter", "Flatness", _
        "Straightness", "Parallelism", "Perpendicular", "Circularity")

    currentData_filePath = Path

    On Error GoTo ErrHandler

    Workbooks.Open Filename:=currentData_filePath
    Set wkbTemp = ActiveWorkbook


    watchFolders_list = "S:\PED (Production Engineering Department)\bla...bla...bla...\RT_CMM_Data_File_Paths.xlsx"
    Workbooks.Open Filename:=watchFolders_list
    Set watchFolders_list = ActiveWorkbook

    watchFolders_list.Activate

    lastShtRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
    Table_Path = WorksheetFunction.VLookup(Left_dataPath, ActiveSheet.Range("A2:C" & lastShtRow), 3, False)

    Workbooks.Open Filename:=Table_Path
    Set Table = ActiveWorkbook     


    Application.DisplayAlerts = False
    wkbTemp.Saved = True
    Table.SaveAs Filename:=Table_Path
    Table.Close SaveChanges:=False

    Workbooks.Close

Exit Sub

ErrHandler:

    Workbooks.Open Filename:="S:\PED (Production Engineering Department)\bla...bla...bla...\Error_Log.xlsx"

    Set ErrorLog = ActiveWorkbook
    ErrorLog.Activate
    unusedRow = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Row
    Range("A" & unusedRow).Value = currentData_filePath
    Application.DisplayAlerts = False
    ErrorLog.Saved = True
    ErrorLog.Save
    Workbooks.Close

    Resume Next

End Sub

Upvotes: 0

Views: 1215

Answers (2)

Louis
Louis

Reputation: 3632

If you want your program to never stop, no matter what, you can simply change the type of error handling from this:

On Error GoTo ErrHandler

To this:

On Error Resume Next

So the final code will look like this:

Dim ArrNames
    Dim wkbTemp As Workbook
    Dim Table As Workbook

    ArrNames = Array("X-Axis", "Y-Axis", "Z-Axis", "Flatness", _
        "Length-X", "Length-Y", "Length-Z", "Length_X", "Length_Y", _
        "Length_Z", "Length", "Angle", "Angle-XY", "Angle-XZ", "Angle-YX", _
        "Angle-YZ", "Angle-ZX", "Angle-ZY", "Radius", "Diameter", "Flatness", _
        "Straightness", "Parallelism", "Perpendicular", "Circularity")

    currentData_filePath = Path

    On Error Resume Next

    Workbooks.Open Filename:=currentData_filePath
    Set wkbTemp = ActiveWorkbook


    watchFolders_list = "S:\PED (Production Engineering Department)\bla...bla...bla...\RT_CMM_Data_File_Paths.xlsx"
    Workbooks.Open Filename:=watchFolders_list
    Set watchFolders_list = ActiveWorkbook

    watchFolders_list.Activate

    lastShtRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
    Table_Path = WorksheetFunction.VLookup(Left_dataPath, ActiveSheet.Range("A2:C" & lastShtRow), 3, False)

    Workbooks.Open Filename:=Table_Path
    Set Table = ActiveWorkbook     

    'Add here somenthing to check if there were errors

    Application.DisplayAlerts = False
    wkbTemp.Saved = True
    Table.SaveAs Filename:=Table_Path
    Table.Close SaveChanges:=False

    Workbooks.Close


End Sub

Upvotes: 0

Badja
Badja

Reputation: 875

If you don't want to click 'OK' and are happy with the risk

ErrHandler:

Application.DisplayAlerts = False       
Application.EnableEvents = False        

Workbooks.Open Filename:="S:\PED (Production Engineering Department)\bla...bla...bla...\Error_Log.xlsx"

Set ErrorLog = ActiveWorkbook
ErrorLog.Activate
unusedRow = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Row
Range("A" & unusedRow).Value = currentData_filePath
Application.DisplayAlerts = False
ErrorLog.Saved = True
ErrorLog.Save
Workbooks.Close

Application.DisableAlerts = True
Application.EnableEvents = True

Resume Next

Upvotes: 0

Related Questions