How To Check If File Exists In Office 365 Sharepoint Using Excel Vba

Checking the existence of a file in Office 365 SharePoint can be done using Excel VBA, and this section will show you how. By following the steps provided below, you can enhance your Office 365 proficiency and efficiently check for the existence of files.

To perform this check, you need to enable the Microsoft XML library in the VBA editor. This step is essential to ensure the smooth execution of the VBA code that will follow.

Next, you will need to evaluate the validity of the hyperlink associated with the file you want to check. The provided VBA code will help you determine whether the file exists in Office 365 SharePoint by checking the validity of the hyperlink.

Here is the code snippet you can use:


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) ' assuming 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

Make sure to replace Range("A1") with the relevant range that contains the hyperlinks you want to check. You can modify it to loop through multiple columns or the entire column.

When a hyperlink is invalid (i.e., the file does not exist on SharePoint), the adjacent cell will display “404 NOT FOUND”. If the hyperlink is valid, nothing will be displayed.

You can customize this code further based on your specific requirements, such as pasting links for all the files you want to check in SharePoint and evaluating the results.

Remember to adjust the code to fit your workbook structure and SharePoint site URL. This approach provides a workaround since there is no direct method to check file existence in SharePoint from Excel VBA.

Enabling the Microsoft XML library in the VBA editor

Before proceeding with the file existence check, you need to enable the Microsoft XML library in the VBA editor. This step is necessary to ensure the smooth execution of the VBA code that will follow.

To enable the Microsoft XML library, follow these steps:

  1. Open the VBA editor in Excel by pressing Alt + F11.
  2. Click on Tools in the menu bar, then select References.
  3. In the References window, scroll down and locate the entry for Microsoft XML, v3.0 or a higher version.
  4. Check the box next to the entry to enable the library, then click OK.

Once you have enabled the Microsoft XML library, you will be able to use the necessary VBA code to check the validity of the file hyperlink in Office 365 SharePoint.

Example :

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) ‘ assuming 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

Use the provided VBA code to check the validity of the hyperlink associated with the file you want to verify. This code uses the Microsoft XML library to send a HEAD request to the file URL and retrieves the status code and status text. If the status code is 404, the adjacent cell will display “404 NOT FOUND,” indicating that the file does not exist in Office 365 SharePoint. If the status code is anything else, the adjacent cell will display the corresponding status code and status text.

Remember to replace Range("A1") with the relevant range in your workbook that contains the hyperlinks you want to check. You can modify the code to loop through multiple columns or the entire column, depending on your needs. Additionally, ensure that you adjust the code to fit your workbook structure and SharePoint site URL.

Hyperlink Result
https://example.com/file1 200 OK
https://example.com/file2 404 NOT FOUND
https://example.com/file3 200 OK

Use the table above as an example of the expected output. Customize the code further based on your specific requirements, such as pasting links for all the files you want to check in SharePoint and evaluating the results.

This approach provides a workaround since there is no direct method to check file existence in SharePoint from Excel VBA. By enabling the Microsoft XML library and using the provided VBA code, you can efficiently check if a file exists in Office 365 SharePoint using Excel.

Checking the validity of the file hyperlink

Once the Microsoft XML library is enabled, you can proceed with checking the validity of the file hyperlink using the provided VBA code. This step is crucial to ensure the accuracy of the file existence check in Office 365 SharePoint.

The VBA code provided in this section allows you to evaluate the validity of the hyperlink associated with the file you want to check. By executing the code, you can determine whether the file exists in Office 365 SharePoint or if it returns an error.

The code works by looping through the specified range of cells that contain hyperlinks. For each cell, the code checks if there is a hyperlink present and then uses the Microsoft XML library to send a request to the URL of the hyperlink. Based on the response received, the code updates the adjacent cell with either the status code and status text or an error message if the hyperlink is invalid.

When the file does not exist on SharePoint, the adjacent cell will display “404 NOT FOUND”, indicating the absence of the file. Conversely, if the link is valid, the adjacent cell will remain empty.

Meet the Author

Abdul Rahim has been working in Information Technology for over two decades. Learn how Abdul got his start as a Tech Blogger , and why he decided to start this Software blog. If you want to send Abdul a quick message, then visit his contact page here.