If statement to check skip to "else" if path does not exist (open)

[sdoppke]

Hi everyone I have the below macro that needs help checking if the file to open is opens or not, if it does not then needs to skip to the else.

Thanks anyone for any help.

Sub Place_Path()
Dim wb As Workbook
ThisFile = Worksheets("MyStoreInfo").Range("C2")
Area = Worksheets("MyStoreInfo").Range("E8")
Region = Worksheets("MyStoreInfo").Range("F8")
District = Worksheets("MyStoreInfo").Range("C8")
M0nth = Worksheets("Schedule Dashboard").Range("F3")
fPath = ("[URL]http://abcdefg/[/URL]" & Area & "/" & Region & "/" & District & "/" & M0nth & "Schedule" & ThisFile & ".xlsx")
sstring = Replace(fPath, " ", "%20")
Sheets("MyStoreInfo").Range("G2") = sstring
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = Workbooks.Open(Filename:=sstring)
'Here i need to create something here that tries to open the above "sstring", If it does not exist then the "Else" statement below
For Each wb In Application.Workbooks            ' Loop through each workbook
        If wb.Name <> ThisWorkbook.Name Then        ' Exclude this workbook
            For Each ws In wb.Sheets                ' Loop through each worksheet of each workbook
                If ws.Name = "Week 1" Then
                Sheets("Week 1").Select
                Sheets("Week 1").Range("AT1").Copy
                ThisWorkbook.Activate
                Sheets("Schedule Dashboard").Select
                Range("W11").Select
                ActiveSheet.Paste
            End If
        Next ws
    End If
Next wb
'Else Sheets("Schedule Dashboard").Range("W11") = "have not"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[jbeaucaire]

Something like this:

Option Explicit
 
Sub Place_Path()
Dim wb As Workbook
Dim ThisFile As String, Area As String, Region As String
Dim District As String, M0nth As String, fPath As String, sString As String
 
With Sheets("MyStoreInfo")
    ThisFile = .Range("C2")
    Area = .Range("E8")
    Region = .Range("F8")
    District = .Range("C8")
    M0nth = Worksheets("Schedule Dashboard").Range("F3")
    fPath = ("http://abcdefg/" & Area & "/" & Region & "/" & District & "/" & M0nth & "Schedule" & ThisFile & ".xlsx")
    sString = Replace(fPath, " ", "%20")
    .Range("G2") = sString
End With
 
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 
If Len(Dir(sString)) > 0 Then    'this verifies the file exists
    Set wb = Workbooks.Open(Filename:=sString)
    wb.Sheets("Week 1").Range("AT1").Copy _
        ThisWorkbook.Sheets("Schedule Dashboard").Range("W11")
    wb.Close False
Else
    Sheets("Schedule Dashboard").Range("W11") = "have not"
End If
 
Application.ScreenUpdating = True
End Sub

 

[sdoppke]

Something like this:

Option Explicit
 
Sub Place_Path()
Dim wb As Workbook
Dim ThisFile As String, Area As String, Region As String
Dim District As String, M0nth As String, fPath As String, sString As String
 
With Sheets("MyStoreInfo")
    ThisFile = .Range("C2")
    Area = .Range("E8")
    Region = .Range("F8")
    District = .Range("C8")
    M0nth = Worksheets("Schedule Dashboard").Range("F3")
    fPath = ("http://abcdefg/" & Area & "/" & Region & "/" & District & "/" & M0nth & "Schedule" & ThisFile & ".xlsx")
    sString = Replace(fPath, " ", "%20")
    .Range("G2") = sString
End With
 
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 
If Len(Dir(sString)) > 0 Then    'this verifies the file exists
    Set wb = Workbooks.Open(Filename:=sString)
    wb.Sheets("Week 1").Range("AT1").Copy _
        ThisWorkbook.Sheets("Schedule Dashboard").Range("W11")
    wb.Close False
Else
    Sheets("Schedule Dashboard").Range("W11") = "have not"
End If
 
Application.ScreenUpdating = True
End Sub

you rock thanks!