Macro to Check if file exists on SharePoint

[sdoppke]

Hi everyone, I'm hoping youi could help me to fix (or recreate) my macro to check my sharepoint site to see if a file exists. The file name is always different so the macro has been built this way:

Sub CheckIfexists()
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")
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(fldr = "[URL]http://abcdefg/sites/retail/123/[/URL]" & Area & "/" & Region & "/" & District & "/" & M0nth & "Schedule" & ThisFile & ".xlsx") Then
MsgBox "File is there!"
Exit Sub
Else
MsgBox "No File!!"
End If
End Sub

The problem im having is, it always sais there is not file, when there is a file posted? Ive noticed that "Distric" portion of the file name usually has 2 words, like "Northern California". So with that, the file path reads with a %20 in between the 2 words. However my built file path above does not insert that %20 into the path. Maybe thats my problem?

Thanks in advacne for any help

sd

 

[sdoppke]

Hi everyone, I'm hoping youi could help me to fix (or recreate) my macro to check my sharepoint site to see if a file exists. The file name is always different so the macro has been built this way:

Sub CheckIfexists()
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")
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(fldr = "[URL]http://abcdefg/sites/retail/123/[/URL]" & Area & "/" & Region & "/" & District & "/" & M0nth & "Schedule" & ThisFile & ".xlsx") Then
MsgBox "File is there!"
Exit Sub
Else
MsgBox "No File!!"
End If
End Sub

The problem im having is, it always sais there is not file, when there is a file posted? Ive noticed that "Distric" portion of the file name usually has 2 words, like "Northern California". So with that, the file path reads with a %20 in between the 2 words. However my built file path above does not insert that %20 into the path. Maybe thats my problem?

Thanks in advacne for any help

sd

good morning everyone Does anyone have any ideas or direction on this topic?

sd

 

[sdoppke]

good morning everyone Does anyone have any ideas or direction on this topic?

sd

Hi everyone, Im still struggling with the above. Would anyone be able to point me in the right direction?

Thank you so much in advance!

sd

 

[sdoppke]

Ive tried other ways after countless hours of searching, here is my latest attempt to no avail:

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()
Dim sstring As String
ThisFile = Worksheets("Sheet1").Range("A1")
Area = Worksheets("Sheet1").Range("A2")
Region = Worksheets("Sheet1").Range("A3")
District = Worksheets("Sheet1").Range("A4")
M0nth = Worksheets("Sheet1").Range("A5")
fPath = ("[URL]http://infonet.abcdefg/[/URL]" & Area & "/" & Region & "/" & District & "/" & M0nth & "Schedule" & ThisFile & ".xlsx")
sstring = Replace(fPath, " ", "%20")
    If FileFolderExists(sstring) Then
        MsgBox "File exists!"
    Else
        MsgBox "File does not exist!"
        Sheets("Sheet1").Range("C2") = sstring
    End If
End Sub

I even have the Else feature loading cell C2 with the path to verify there are no mistakes.

Still says no file when it is there?

really appreciate anyone taking a look at this and offering help.

sd

 

[Achillius]

One of the ways I found to do it is to attempt to open the file, and then check if there's a change in the Active Workbook's name. You need to use On Error Resume Next, otherwise it will throw an error when the file isn't found.

    On Error Resume Next
    Application.Workbooks.Open (fileName)
    newBook = ActiveWorkbook.Name
    If originalBook = newBook Then
        [the file does not exist, do something]
    Else
        [the file exists, do something else]
    End If
    On Error Goto 0

 

[Technowolf]

Okay! I know this thread is old as dirt, but I've been fighting with this exact issue for days and have been all over the net and forums until sick without any elegant solution to this. I'll say, Achillius's method DOES work, but if you're having to check for several files you quickly run into 'On Error Resume Next' not working. I think I may have come up with something better though. I'll post what I'm doing here, and I'm sure someone can take this and streamline it to their own means.

First off, there simply isn't a good way to check if a file exists on a sharepoint site. However, what you can check for is if a hyperlink is valid. Which in the end will accomplish the same thing.

For this code to work, in your VBA editor, you must go to Tools > References and enable Microsoft XML V3 (or above).

The below code as, I have it, will check the hyperlink in Range("A1"), but it's a loop so you can change Range("A1") to Range("A:A") or Range("B:B") etc. to check every link in the column. If the link is invalid (ie, the file is not on the sharepoint), in the next cell over beside each link it'll display "404 NOT FOUND". If the link is valid (meaning the file DOES exist on the sharepoint) then it'll display nothing. From here you should be able to spin off your own code, have your macro paste links for all the files you want to check in sharepoint in column A, then look for 404 NOT FOUND's or count blanks or what-have-you. Like I said, there are plenty of ways to streamline this, but this should get you started.

Option Explicit

Sub CheckHyperlinks()
    Dim oColumn As Range
    Set oColumn = Range("A1") ' replace this with code to get the relevant column
    Dim oCell As Range
    For Each oCell In oColumn.Cells
        If oCell.Hyperlinks.Count > 0 Then
            Dim oHyperlink As Hyperlink
            Set oHyperlink = oCell.Hyperlinks(1) ' I assume only 1 hyperlink per cell
            Dim strResult As String
            strResult = GetResult(oHyperlink.Address)
            oCell.Offset(0, 1).Value = strResult
        End If
    Next oCell
End Sub

Private Function GetResult(ByVal strUrl As String) As String
    On Error GoTo ErrorHandler
    Dim oHttp As New MSXML2.XMLHTTP30
    oHttp.Open "HEAD", strUrl, False
    oHttp.send
    GetResult = oHttp.Status & " " & oHttp.statusText
    Exit Function
ErrorHandler:
    GetResult = "Error: " & Err.Description
End Function

Private Function GetColumn() As Range
    Set GetColumn = ActiveWorkbook.Worksheets(1).Range("A:A")
End Function

Credit for the above code goes to Gary McGill over at Stackoverflow for his answer to the thread Sort dead hyperlinks in Excel with VBA? - Stack Overflow
Hopefully someone finds this helpful!

 

[Erni76]

Okay! I know this thread is old as dirt, but I've been fighting with this exact issue for days and have been all over the net and forums until sick without any elegant solution to this. I'll say, Achillius's method DOES work, but if you're having to check for several files you quickly run into 'On Error Resume Next' not working. I think I may have come up with something better though. I'll post what I'm doing here, and I'm sure someone can take this and streamline it to their own means.

First off, there simply isn't a good way to check if a file exists on a sharepoint site. However, what you can check for is if a hyperlink is valid. Which in the end will accomplish the same thing.

For this code to work, in your VBA editor, you must go to Tools > References and enable Microsoft XML V3 (or above).

The below code as, I have it, will check the hyperlink in Range("A1"), but it's a loop so you can change Range("A1") to Range("A:A") or Range("B:B") etc. to check every link in the column. If the link is invalid (ie, the file is not on the sharepoint), in the next cell over beside each link it'll display "404 NOT FOUND". If the link is valid (meaning the file DOES exist on the sharepoint) then it'll display nothing. From here you should be able to spin off your own code, have your macro paste links for all the files you want to check in sharepoint in column A, then look for 404 NOT FOUND's or count blanks or what-have-you. Like I said, there are plenty of ways to streamline this, but this should get you started.

Option Explicit

Sub CheckHyperlinks()
    Dim oColumn As Range
    Set oColumn = Range("A1") ' replace this with code to get the relevant column
    Dim oCell As Range
    For Each oCell In oColumn.Cells
        If oCell.Hyperlinks.Count > 0 Then
            Dim oHyperlink As Hyperlink
            Set oHyperlink = oCell.Hyperlinks(1) ' I assume only 1 hyperlink per cell
            Dim strResult As String
            strResult = GetResult(oHyperlink.Address)
            oCell.Offset(0, 1).Value = strResult
        End If
    Next oCell
End Sub

Private Function GetResult(ByVal strUrl As String) As String
    On Error GoTo ErrorHandler
    Dim oHttp As New MSXML2.XMLHTTP30
    oHttp.Open "HEAD", strUrl, False
    oHttp.send
    GetResult = oHttp.Status & " " & oHttp.statusText
    Exit Function
ErrorHandler:
    GetResult = "Error: " & Err.Description
End Function

Private Function GetColumn() As Range
    Set GetColumn = ActiveWorkbook.Worksheets(1).Range("A:A")
End Function

Credit for the above code goes to Gary McGill over at Stackoverflow for his answer to the thread Sort dead hyperlinks in Excel with VBA? - Stack Overflow
Hopefully someone finds this helpful!

This worked as a charm. An exceptional example of "Thinking outside of the box". BIG THANK YOU for your efforts to resolve this problem!