[RESOLVED] Message box if error

[Magic Man]

Hi Folks,

New to the forum. Very familiar with excel VBA but new to VBS.

I've created a code that activates and excel VBA macro. However it's reliant on me being connected to a network (which isn't always possible)

I'd like to add in an error code if the vbs can't access the workbook. If that error occurs, I'd like a message box that gives me the option to abort the script.

Here's my code:

Option Explicit

Dim xlApp, xlBook

Set xlApp = CreateObject("Excel.Application")
'~~> Change Path here
Set xlBook = xlApp.Workbooks.Open("\\network.grp\basfap\BS-HBA\ROPA\Admin_stuff\BACK UP.xlsm", 0, True) 
'This is the network path. If there's an error I'd like the message box stating something along the lines of "You're not connected to the network" with an "abort" button that exits the script. 
xlApp.Run "CopyMyFolder"
xlBook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing

 msgbox"BACK UP IS COMPLETE", 0+64, "Back up from Network to local drive"

I look forward to your replies.

[OptionBase1]

https://stackoverflow.com/questions/...error-handling

[Magic Man]

Thanks I read that and made several attempts, I couldn't work out where any error coding was meant to go in my code.

MM

[jdelano]

VBS uses the same error handling as VBA. Put an On Error Resume Next before the attempt to open the workbook. Then check if Err > 0 after the open statemen (edit if true then there was a problem opening the workbook. Display a message and exit the code.

[Magic Man]

jdelano,

Thanks for pointing me in the right direction,

Figured it out. (and threw in a new line into the msgbox while I was at it)

If anyone else wants the answer, here's my code:

Option Explicit

Dim xlApp, xlBook

Set xlApp = CreateObject("Excel.Application")
'~~> Change Path here
On Error Resume Next
Set xlBook = xlApp.Workbooks.Open("\\network.grp\basfap\BS-HBA\ROPA\Admin_stuff\BACK UP.xlsm", 0, True)
If Err.Number >0 Then
msgbox"There was a problem opening workbook" & vbNewLine & "Are you connected to the network?", 0+16
wscript.quit
End If 
xlApp.Run "CopyMyFolder"
xlBook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing

 msgbox"BACK UP IS COMPLETE", 0+64, "Back up from Network to local drive"

VBS and VBA both have similarities, but as is often the case, it's the little idiosyncracies that throw you

[jdelano]

Absolutely they can. You're welcome.