If file exists then cell value equals "Yes"

[sdoppke]

Morning everyone, I am trying to look up a file path (http to check if a file exists, if it does a folrmula in a cell will return a value if True and antoher value if False. I thinking a functin and a UDf will do the trick, but dont know where to start. To make things harder the file path will never be the same. each file path is created by different cell values. See below:

ThisFile = Worksheets("MyStoreInfo").Range("C2")
Area = Worksheets("MyStoreInfo").Range("E8")
Region = Worksheets("MyStoreInfo").Range("F8")
District = Worksheets("MyStoreInfo").Range("C8")
M0nth = Worksheets("Dashboard").Range("O8")
("[URL]http://abcdefgwebsite/[/URL]" & Area & "/" & Region & "/" & District & "/" & M0nth & "Schedule" & ThisFile & ".xlsx")

I really appreciate any direction anyone give me

sd

 

[sdoppke]

Morning everyone, I am trying to look up a file path (http to check if a file exists, if it does a folrmula in a cell will return a value if True and antoher value if False. I thinking a functin and a UDf will do the trick, but dont know where to start. To make things harder the file path will never be the same. each file path is created by different cell values. See below:

ThisFile = Worksheets("MyStoreInfo").Range("C2")
Area = Worksheets("MyStoreInfo").Range("E8")
Region = Worksheets("MyStoreInfo").Range("F8")
District = Worksheets("MyStoreInfo").Range("C8")
M0nth = Worksheets("Dashboard").Range("O8")
("[URL]http://abcdefgwebsite/[/URL]" & Area & "/" & Region & "/" & District & "/" & M0nth & "Schedule" & ThisFile & ".xlsx")

I really appreciate any direction anyone give me

sd

I was thinking a function like so might get me started?

Function FileExists(FullpathName As String) As Boolean
ThisFile = Worksheets("MyStoreInfo").Range("C2")
Area = Worksheets("MyStoreInfo").Range("E8")
Region = Worksheets("MyStoreInfo").Range("F8")
District = Worksheets("MyStoreInfo").Range("C8")
M0nth = Worksheets("Dashboard").Range("O8")
If Dir("[URL]http://abcdefgwebsite[/URL]" & Area & "/" & Region & "/" & District & "/" & M0nth & "Schedule" & ThisFile & ".xlsx") <> "" Then
    FileExists = True
Else
    FileExists = False
End If
End Function

 

[sdoppke]

Ive tried the above function with:

IF(FileExists,"have","have not") as the formula, with no luck...

Any ideas?

sd

 

[sdoppke]

Calling all geniuses any ideas on this?

sd

 

[sdoppke]

Would someone be able to take a look at this, to help come up with a solution?

Thank you so much in advance for any help with this.
sd

 

[sdoppke]

I have tried this sub and function to test with a messge box, but even though the file is there, it returns a false answer? Any ideas?

Public Function FileFolderExists(strFullPath As String) As Boolean
    On Error GoTo EarlyExit
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
    
EarlyExit:
    On Error GoTo 0
End Function
Public Sub TestFileExistence()
ThisFile = Worksheets("MyStoreInfo").Range("C2")
Area = Worksheets("MyStoreInfo").Range("E8")
Region = Worksheets("MyStoreInfo").Range("F8")
District = Worksheets("MyStoreInfo").Range("C8")
M0nth = Worksheets("Dashboard").Range("O8")
    If FileFolderExists("abcdefg/[URL="http://infonet.t-mobile.com/sites/retail/WFM/"]sites/retail/WFM/[/URL]" & Area & "/" & Region & "/" & District & "/" & M0nth & "Schedule" & ThisFile & ".xlsx") Then
        MsgBox "File exists!"
    Else
        MsgBox "File does not exist!"
    End If
End Sub

I would really appreciate any help

sd

 

[RichardMGreen]

Try this (untested):-

ThisFile = Worksheets("MyStoreInfo").Range("C2")
Area = Worksheets("MyStoreInfo").Range("E8")
Region = Worksheets("MyStoreInfo").Range("F8")
District = Worksheets("MyStoreInfo").Range("C8")
M0nth = Worksheets("Dashboard").Range("O8")
fname=("http://abcdefgwebsite/" & Area & "/" & Region & "/" & District & "/" & M0nth & "Schedule" & ThisFile & ".xlsx")
if fileexists(fname)=true then
     'do whatever you need to here
else
     'do something else
endif

This will assign the complete file and path the the variable fname

to check if the file exists then try:-

Private Function FileExists(fname) As Boolean
'----- Returns TRUE if the file exists -----
    Dim x As String
    x = Dir(fname)
    If x <> "" Then FileExists = True Else FileExists = False
End Function

This will work on files held on networks, I'm not entirely sure what will happen if you're checking a website.

You can then test if FileExists is true or not (i.e. the file exists) and

 

[sdoppke]

Try this (untested):-

ThisFile = Worksheets("MyStoreInfo").Range("C2")
Area = Worksheets("MyStoreInfo").Range("E8")
Region = Worksheets("MyStoreInfo").Range("F8")
District = Worksheets("MyStoreInfo").Range("C8")
M0nth = Worksheets("Dashboard").Range("O8")
fname=("http://abcdefgwebsite/" & Area & "/" & Region & "/" & District & "/" & M0nth & "Schedule" & ThisFile & ".xlsx")
if fileexists(fname)=true then
     'do whatever you need to here
else
     'do something else
endif

This will assign the complete file and path the the variable fname

to check if the file exists then try:-

Private Function FileExists(fname) As Boolean
'----- Returns TRUE if the file exists -----
    Dim x As String
    x = Dir(fname)
    If x <> "" Then FileExists = True Else FileExists = False
End Function

This will work on files held on networks, I'm not entirely sure what will happen if you're checking a website.

You can then test if FileExists is true or not (i.e. the file exists) and

First, thank you so much for the help!! I have tried this, and get hung up on:

 x = Dir(fname)

says is a bad file name or number (runtime error)

Any ideas?

sd

 

[sdoppke]

Try this (untested):-

ThisFile = Worksheets("MyStoreInfo").Range("C2")
Area = Worksheets("MyStoreInfo").Range("E8")
Region = Worksheets("MyStoreInfo").Range("F8")
District = Worksheets("MyStoreInfo").Range("C8")
M0nth = Worksheets("Dashboard").Range("O8")
fname=("http://abcdefgwebsite/" & Area & "/" & Region & "/" & District & "/" & M0nth & "Schedule" & ThisFile & ".xlsx")
if fileexists(fname)=true then
     'do whatever you need to here
else
     'do something else
endif

This will assign the complete file and path the the variable fname

to check if the file exists then try:-

Private Function FileExists(fname) As Boolean
'----- Returns TRUE if the file exists -----
    Dim x As String
    x = Dir(fname)
    If x <> "" Then FileExists = True Else FileExists = False
End Function

This will work on files held on networks, I'm not entirely sure what will happen if you're checking a website.

You can then test if FileExists is true or not (i.e. the file exists) and

Hi Richard i modified your function and have not used the sub to try to get a cell to return true or false. but no luck any ideas?

The function was changed to:

Public Function FileExists() As Boolean
'----- Returns TRUE if the file exists -----
ThisFile = Worksheets("MyStoreInfo").Range("C2")
Area = Worksheets("MyStoreInfo").Range("E8")
Region = Worksheets("MyStoreInfo").Range("F8")
District = Worksheets("MyStoreInfo").Range("C8")
M0nth = Worksheets("Dashboard").Range("O8")
Dim x As String
x = Dir("[URL]http://abcwebsite/[/URL]" & Area & "/" & Region & "/" & District & "/" & M0nth & "Schedule" & ThisFile & ".xlsx")
    
    If x <> "" Then FileExists = True Else FileExists = False
    
End Function

and the cell formula is:

=IF(FileExists,"have","have not")

Thanks again for any help.

sd