I am developing an application for a state agency that links to a SharePoint list. End users fill out a form and attach files. The files are stored in SharePoint and links to the files are stored in an XML field called "List of Attachments". Here is an example of the field contents:

<div class="ExternalClass681B39FBB6184E9B8810AAD6C9E67989">
        <li><a href="RedactedSharePointURL/ADV%20280210%202022%20002.pdf">ADV 280210 2022 002</a></li>
        <li><a href="RedactedSharePointURL/AFD%202802102%202022%20002.pdf">AFD 2802102 2022 002</a></li>
        <li><a href="RedactedSharePointURL/SPF%20280210%202022%20002.pdf">SPF 280210 2022 002</a></li>
        <li><a href="RedactedSharePointURL/TC%20280210%202022%20002.xlsm?d=wb41845cda38b48d4a6240e9e59f7031a">TC 280210 2022 002</a></li>
The application loads the content of the field into an MSXML2.DOMDocument60 object and creates a separate record in a table for each link. The links are stored in a Hyperlink field.

Click image for larger version. 

Name:	FILE_ITEM.png 
Views:	11 
Size:	7.0 KB 
ID:	47794

One of the files will always be an Excel macro-enabled spreadsheet containing data for a contract. The code below is how I open the file. The LookUp.GetFileLink() method extracts the href from the Hyperlink field for the specified file. The SheetName variable is the name of the worksheet that I'm getting the data from.

    Dim xApp As Excel.Application
    Dim xBook As Excel.Workbook
    Dim xSheet As Excel.Worksheet
    Set xApp = New Excel.Application
    Set xBook = xApp.Workbooks.Open(LookUp.GetFileLink(FILE_ITEM_ID))
    Set xSheet = xBook.Worksheets(SheetName)

This was working fine last week. It failed when I tested on 5/10/2022. What's happening now is that when VBA opens the spreadsheet it can only see one worksheet and the name of that has the same name as the file. The are no sheets in the file that are named the same as the file, so that suggests to me that there is something relating to permissions or security that is preventing VBA from seeing the actual worksheets. I am able to download the file from SharePoint and see the 4 visible worksheets when I open it in Excel. There are also 16 hidden worksheets, so the total count should be 20. Here are my queries in the Immediate window:

TC 280210 2022 002.xlsm


TC 280210 2022 002
The app is being developed in Access 365 using an on-site Win10 computer. I copied it to my personal Win11 laptop running Access 2019. The same code used to work on my laptop before, but it is now also failing.

Any suggestions would be greatly appreciated.

Thank you