VBA error handling

TomCon

Active Member
Joined
Mar 31, 2011
Messages
284
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
How do you set up to handle an error within an error handler?

Here is what i want to do. I try this statement. If it works, i'm done.
Set wbDest = Workbooks(Dest)

If it fails I want to go to an error handler that tries to open the file:
Workbooks.Open Filename:=dir_loc & Dest

And if that fails, i want to go to an error handler that creates the workbook:
Workbooks.Add
...

I paste the entire code below.

What is happening is that if the attempt to open the workbook errors, the VBA sub fails, and the ON ERROR GOTO just before that statement seems to be ignored.

How is this case property set up to work?

Sub Init_Books()
Src = "D4_p_TRK.xlsm"
Dest = "D4p_new4.xlsm"
Set wbSrc = Workbooks(Src)
On Error GoTo needopen
Set wbDest = Workbooks(Dest)
GoTo end_sub

needopen:
dir_loc = "C:\Users\xxxx\Documents\"
On Error GoTo create_it
ChDir dir_loc
Workbooks.Open Filename:=dir_loc & Dest 'Why does this not get handled by the "On Error GOTO create_it" statement?
Resume

create_it:
Workbooks.Add

ActiveWorkbook.SaveAs Filename:=dest_loc & Dest _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Resume

end_sub:
End Sub
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
You have asked for new workbook to created and saved as the 3rd option.
That is what this does
VBA Code:
    Dim Dest As String, dir_loc As String, wbDest As Workbook, fPath As String
    Dest = "filename.xlsm"
    dir_loc = "C:\whatever\"
    fPath = dir_loc & Dest

    On Error Resume Next
    Set wbDest = Workbooks(Dest)
    If Err.Number <> 0 Then
        If Dir(fPath) <> "" Then
            Workbooks.Open (fPath)
        Else
            Set wbDest = Workbooks.Add
            wbDest.SaveAs Filename:=fPath, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        End If
    End If
    On Error GoTo 0

Do you want the previously active workbook saved as Dest?
Do you want the original workbook saved first and then re-saved as Dest?
 

TomCon

Active Member
Joined
Mar 31, 2011
Messages
284
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Wow, perfect, thanks!!! I do not need the previously active WB saved or anything re-saved. The macro does exactly what i need to be done.

And i learned about the Dir() function which was very useful in this context (and may be in the future for me!), as well as a better way to handle the Error Handling.

Thanks again for your time!
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Thanks for your feedback (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,339
Messages
5,624,103
Members
416,011
Latest member
chengkoonwing

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top