none
Error Handling when excel vba code does not find the file RRS feed

  • Question

  • Hi Below is my code:

    file1 = Dir("Q:\Budget\Historical Budgets\" & new_dept_folder & "\*.xls*")
    col = 2
    col_new = 3

    Application.DisplayAlerts = False
    While (file1 <> "")

            filename = Left$(file1, 6)

            ' Open the newly selected workbook
            Set wb = Workbooks.Open("Q:\Budget\Historical Budgets\" & new_dept_folder & "\" & file1)

            udds = filename & " - " & wb.Sheets("Budget").Range("J1").Value

            ThisWorkbook.Sheets(1).Cells(1, col).Value = udds

            For x = LBound(data_new) To UBound(data_new)

                wb.Sheets("Budget").Select
                Range(data_new(x)).Select
                Selection.Copy
                ThisWorkbook.Sheets(1).Cells(x + 5, col_new).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

            Next x

             ' Close the current workbook
            wb.Close SaveChanges:=False

            On Error GoTo Errhandler
            Set wb_old = Workbooks.Open("Q:\Budget\Historical Budgets\" & old_dept_folder & "\" & file1)   <--- Need Error handling on this line (i.e. if I get error here)

            For x = LBound(data) To UBound(data)

                wb_old.Sheets("Budget").Select
                Range(data(x)).Select
                Selection.Copy
                ThisWorkbook.Sheets(1).Cells(x + 5, col).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,   SkipBlanks:=False, Transpose:=False

            Next x
            Application.CutCopyMode = False

            ' Close the current workbook
                
            On Error Resume Next
            wb_old.Close SaveChanges:=False
            
    Errhandler:
            
            For x = LBound(data) To UBound(data)
            ThisWorkbook.Sheets(1).Cells(x + 5, col).Value = 0
            
            Next x

            ' Select the next file in the dir array
            file1 = Dir
            col = col + 5
            col_new = col_new + 5

    Wend

    Everytime I run the code the errhandler which I don't need. Could you please help me on this.


    Regards, Hitesh

    Wednesday, April 15, 2015 8:48 PM

All replies

  • Hi,

    You could check whether the file is exist, then open it.

    If Dir(path)<>”” Then

    There are three On Error Statements, On Error GoTo <line>, On Error Resume Next and On Error GoTo 0.

    More information, please refer to:

    # How to Use "On Error" to Handle Errors in a Macro

    https://support.microsoft.com/en-us/kb/141571?wa=wsignin1.0

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, April 16, 2015 6:42 AM
    Moderator
  • Hi Starain,

    What's wrong with this line in my code:

    On Error GoTo Errhandler

            Set wb_old = Workbooks.Open("Q:\Budget\Historical Budgets\" & old_dept_folder & "\" & file1)   <--- Need Error handling on this line (i.e. if I get error here)

    Errhandler:
            
            For x = LBound(data) To UBound(data)
            ThisWorkbook.Sheets(1).Cells(x + 5, col).Value = 0
            
            Next x


    Regards, Hitesh

    Thursday, April 16, 2015 3:18 PM
  • Hi Hitesh,

    I don’t understand what you mean? Your code is ok. Do you mean it throws error? (Go to Errhandler)

    What’s the value of “Q:\Budget\Historical Budgets\" & old_dept_folder & "\" & file1”?

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 17, 2015 1:53 AM
    Moderator