FileToOpen Error Handling

[CrYoZ]

Evening All,

First post here so hopefully this hasn't been answered before (google wasn't really helpful), I am trying to run some error handling when using FileToOpen. What my code does is when the macro is run, it asks the user to enter a branch id, saves as a dim, then another box comes up and asks for a customer number and saves as a dim. These two are then used to finished a web address where the file will be located.

My code below works fine if excel finds the file location but when it doesn't i just get an Excel msg box with "We cannot connect to 'http://etc etc", when ok is pressed the MVB end or debug options come up. What i would like to happen is a MsgBox to appear stating, "Q File not available. Please confirm and re-run macro" and then the macro to exit.

I have messed around with On Error handling but can't seem to get the placement right of the If statements to accomplish the outcome.

Hope i have explained ok......... Any help would be greatly appreciated.

Current Code is:

Sub Module2_Open_QFile_BladeServer()


'
' Opens Q file from Blade Server
'


'


Dim custnum As String
Dim branchid As String


branchid = InputBox("Please Enter the Branch ID, this consists of three letters and one number xxx0, make sure you use lower case only! ie. dar1, bri1, too1, etc...", "Enter the Branch ID for your branch...")
custnum = InputBox("Please Enter the Customer Number in a 6 digit format... ie 130508 or 011778", "Enter the Customer Number to run the BO Report on...")
MsgBox ("You have entered " & custnum & " in " & branchid & ", press 'OK' to run the report..")



FileToOpen = "http://(work server location)/reports/" & branchid & "/q" & custnum & ".txt"

If FileToOpen <> False Then
Workbooks.OpenText FileToOpen, Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=True, OtherChar:="~", FieldInfo:=Array(Array(1, 2 _
), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 2), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), _
Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array( _
28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), _
Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array( _
41, 1), Array(42, 1), Array(43, 1)), TrailingMinusNumbers:=True

End If

End Sub

 

[gallen]

Hello and welcome:

Just catch any error which means the attempt has failed:

Changes in red

Sub Module2_Open_QFile_BladeServer()

' Opens Q file from Blade Server

Dim custnum As String
Dim branchid As String

    branchid = InputBox("Please Enter the Branch ID, this consists of three letters and one number xxx0, make sure you use lower case only! ie. dar1, bri1, too1, etc...", "Enter the Branch ID for your branch...")
    custnum = InputBox("Please Enter the Customer Number in a 6 digit format... ie 130508 or 011778", "Enter the Customer Number to run the BO Report on...")
    MsgBox ("You have entered " & custnum & " in " & branchid & ", press 'OK' to run the report..")
    
   [COLOR=#ff0000][B] On Error GoTo errHandle[/B][/COLOR]
    
    FileToOpen = "http://(work server location)/reports/" & branchid & "/q" & custnum & ".txt"
    
    If FileToOpen <> False Then
        Workbooks.OpenText FileToOpen, Origin:=xlMSDOS, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=True, OtherChar:="~", FieldInfo:=Array(Array(1, 2 _
        ), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 1), Array(11, 2), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
        , 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), _
        Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array( _
        28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), _
        Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array( _
        41, 1), Array(42, 1), Array(43, 1)), TrailingMinusNumbers:=True
        
    End If

[COLOR=#ff0000][B]Exit Sub
[/B][/COLOR]
[COLOR=#ff0000][B]errHandle:
[/B][/COLOR]
[COLOR=#ff0000][B]    MsgBox "Error Opening File " & FileToOpen & vbNewLine & "Please check and try again", vbCritical, "Error"[/B][/COLOR]
    
End Sub

 

[CrYoZ]

Many thanks gallen. Works a dream