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

Hi Kathrin,

Please make sure your desktop Office apps have installed the latest update. Here are the steps: Open Excel > File > Account > Product Information > Update Options > Update Now.

Besides, you can try to repair the corrupted Excel files after clicked update button. Here are the steps: Open Excel > File > Open > Browse > select the problematic Excel file > click the dropdown arrow in the "Open" button > select "Open and repair". Please refer to: Repair a corrupted workbook.

If the issue persists, please let us know the following information so we can assist you further:

1. The screenshot of the error message Excel (1004).
2. The detailed steps you create the macro, update the dropdown list, store as a separate document in SharePoint and download to the temporary folder. Some screenshots would help us better understand the scenario so we can reproduce the issue.
3. The SharePoint service you are using. SharePoint Online or On-premise SharePoint.

Regards,

Jared

Answer 2

Hi Kathrin,

Any update would be appreciated.

Regards,

Jared

Answer 3

Dear Jared,

I have checked, my desktop Office apps do have installed the latest update. Furthermore, the issue occurs inconsistently, so sometimes I would experience it, sometimes I would not.

The files are apparently not downloaded completely to my temporary folder (if the problem occurs, I find them having a size of 40-43 kb only, while the original size on SharePoint is more than 200 kb), that's why Excel says they are corrupted:  

(those with full size I downloaded via the same mechanism earlier and did not have any issue, also worked with the same file Masterlist_SG_ER.xlsx previously)

This is a screenshot of the error message:

We are using SharePoint Online.

The macro code is the following:

The macro is working, as it does not have any issues in most cases. The link is pointing to the masterlist in SharePoint Online (same Site as the "form" but different folder, access cannot be a problem).

Thank you very much.

Best regards,

Kathrin

Answer 4

Hi Kathrin,

Thanks for clarifying that the issue is caused by the incomplete downloading for the files from SharePoint, this could happen if the Excel file contains lots of VBA codes. Therefore, if you download the file directly from SharePoint library, to get the files completely, you can try using OneDrive for Business (included in Office 365 desktop apps suite) to sync SharePoint library. Here are the steps:

Go to SharePoint library that contains the target file > click "Sync" on the top ribbon > when the desktop OneDrive for Business launched, click Sync Now > after the sync process completed, click Show my files > check if the Excel file synced completely and can work properly. Please refer to: Set up your computer to sync SharePoint files or OneDrive for Business files when you’re not using Office 365.

Regards,

Jared

Answer 5

Dear Jared,

the file downloaded to the temporary folder does not contain any VBA codes. It is an .xlsx document and pure raw data. 

OneDrive for Business is not really a solution here, as we do not want the users to download/sync the content of the whole SharePoint library. That is why we use the download to temp folder only for the required master list (otherwise it is hundreds of lists, and it is hundreds of users).

Is there any other explanation while the file is downloaded sometimes completely, sometimes not (and for some users never completely downloaded)? Network connection is stable all the time.

Thank you,

Kathrin