Simple Sharepoint list refresh macro from Excel 365 taking very long to refresh.

Question

Simple Sharepoint list refresh macro from Excel 365 taking very long to refresh.

Aik Koon Wee 1

Hi,
I'm using Excel 365 with a simple macro that gets triggered on workbook open event to refresh query connection for pulling data from Sharepoint list. Once the data is refreshed, another macro module is called to crunch these data. However, there is this problem of very slow crawling connection refresh with Sharepoint that sometimes ended up in 'Runtime error '1004'. An unexpected error has occurred.'. This error will happen at the refresh line in the macro.

If there is no error, the time taken to refresh can take between 5min to whopping > 10min. This data is from a sharepoint listing view which is already customized to filter out some rows within Sharepoint and the final rows of data that gets queried into the excel sheet is only 367 rows.

I hope to be able to understand what could be the possible causes so that i can reach out to my companies IT team to try to improve it. At the same time, if there are other reasons that i can resolved from my end, i would very much like to try it out.
Thank you.

Li Zhang_MSFT 1,566 Reputation points

2021-02-04T02:12:04.923+00:00

Hi @Aik Koon Wee ,

I am researching your issue and I will reply you as soon as possible.
Elsie Lu_MSFT 9,806 Reputation points

2021-02-12T02:52:45.41+00:00

If the answer helps you, please click "Accept Answer" so that users who have the same question can refer to it.
Thank you for your support and understanding.

2 answers

Your answer

Li Zhang_MSFT 1,566 Reputation points

2021-02-04T02:12:04.923+00:00

Hi @Aik Koon Wee ,

I am researching your issue and I will reply you as soon as possible.
Elsie Lu_MSFT 9,806 Reputation points

2021-02-12T02:52:45.41+00:00

If the answer helps you, please click "Accept Answer" so that users who have the same question can refer to it.
Thank you for your support and understanding.

Answer 1

Li Zhang_MSFT 1,566

Hi @Aik Koon Wee ,

Here are some tips to improve your VBA macro performance:

1.Turn off everything but the essentials in VBA.

2.Disable Office animations through system settings.

3.Disable Office animations through registry settings.

4.Remove unnecessary selects.

5.Use the With statement to read object properties.

6.Use ranges and arrays.

For more information, please refer to:

https://techcommunity.microsoft.com/t5/excel/9-quick-tips-to-improve-your-vba-macro-performance/m-p/173687

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Li Zhang_MSFT 1,566 Reputation points

2021-02-05T08:16:24.11+00:00

Hi @Aik Koon Wee ,

Is there any progress on this thread?

If you need further assistance, please feel free to reply.
Aik Koon Wee 1 Reputation point

2021-02-05T09:07:59.637+00:00

Hi @Li Zhang_MSFT
Thanks for responding but the solutions suggested does not seemed to be targeting at the question. I'm hoping to check on a work around for slow Sharepoint list connection or timeout connection and not on speeding up macro. My apologies if the question was not asked clearly in the 1st place.

Besides this, you seemed to be responding to the wrong requestor?
Li Zhang_MSFT 1,566 Reputation points

2021-02-09T11:23:14.887+00:00

Hi @Aik Koon Wee ,

Sorry to respond the wrong requestor. Do you have many queries？

You could refer to the following article:

https://www.myonlinetraininghub.com/excel-forum/power-query/slow-to-refresh-and-load

Note: Microsoft is providing this information as a convenience to you. The sites are not controlled by Microsoft. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link.
Aik Koon Wee 1 Reputation point

2021-02-15T07:52:02.237+00:00

Hi @Li Zhang_MSFT ,
The article suggested turning off background data which i had done with no improvement. The 2nd suggestions on buffering the table does not help as it is meant for tables that are already imported into excel. In my case, the slow refreshing happened as the data was being imported and i only have 1 sharepoint list connection setup in the spreadsheet.

I came across this forum related to direct query to sharepoint listing instead of importing them.

100377

Do you think this will help though direct query to sharepoint listing seemed to be not available yet.

Thank you.
Li Zhang_MSFT 1,566 Reputation points

2021-02-19T09:37:12.267+00:00

Hi @Aik Koon Wee ,

In the link you provided, direct query is applied to PowerBI, I am not professional for PowerBI and I couldn't provide related advice.

Answer 2

d1g1t4Lnrg 6

If you are using a sharepoint list then use the export from there and you will have a query to pull your list of data.

Just use a start up event to run the refresh on data, or you can setup this to refresh on open.

Also use the workbook event to open your "ECT2sover"

Share via

Simple Sharepoint list refresh macro from Excel 365 taking very long to refresh.

2 answers

Your answer