Excel file with macro accessing file on SharePoint: Error 1004

Question

I have an excel file "Form" on SharePoint that contains a macro to update a dropdown list. The dropdown list is stored as a separate document in SharePoint as well ("List") and downloaded to the temporary folder when the update macro is used. The form accesses the list from there.

When the Form is opened in SharePoint and the update button is clicked, everything works perfectly fine.

When the Form is downloaded e.g. to the desktop and the update button is clicked, I encounter an inconsistent error:

Sometimes I do not have any problem, the List is downloaded to the temporary folder and can be accessed.

Sometimes I get an error message in Excel (1004) saying that the list is corrupted. When I open it from the temporary folder, it can actually not be read.

Does you have a suggestion which factor could be impacting here?

The issue is inconsistent across users downloading the same file from the SharePoint and also inconsistent within users (one person using the same pc opening the same file, sometimes working, sometimes not).

Appreciate any suggestions as I really cannot explain this! Thank you.

Answer 1

Addition: I have tried to park the masterlist file on one drive for business as well as google drive. With both the issue occurs as well. I have also used network outside my company environment - same issue.

Answer 2

Hi Kathrin,

Thanks for your update.

You can take the following steps to check the outcome:

a). Open a new private window in browser. Here are the steps: Open browser > right click the icon on the taskbar below > IE(Start InPrivate Browsing), Chrome(New incognito window), Firefox(New Private Window) > go to the library and download the file.

b). Clear the browser cache. Steps: Open browser > Ctrl+ Shift+ Delete > delete all the browsing cache > Restart the browser.

c). Using "Open with Explorer" feature in SharePoint to get the Excel file (only available in IE browser). Here are the step: Open IE browser > Go to SharePoint library > click "Return to classic SharePoint" on the lower left corner > click LIBRARY on the top ribbon > Open with Explorer > drag the file to local folder to check the outcome. Please refer to: https://support.microsoft.com/en-sg/kb/2629108.

If the issue persists, to analyze it in our side, I'd like to collect your tenant information and trace some logs. To protect your privacy, I've sent you a private message asking for it, you can access it via private message. 

Regards,

Jared

Answer 3

Hi Kathrin,

If you are still experiencing the issue, please share the information i mentioned in PM so we can better analyze the issue.

Regards,

Jared