VBA to pull data from multiple excel files @ sharepoint.

Burnt_Fingers

New Member
Joined
Feb 6, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi All,
I've been looking for quite some time to get some help about a tiny project I work on.
So the task is to connect to specified sharepoint folder that will contain unknown number of excel files. Out of each of those files I need to pull data from a fixed set of cells. I have my script up and running but im rather unsatisfied with its pace. Currently I have 15 files, im pulling data from 9 cells from each of them and it takes ~45sec to execute from double clicking the file to getting results. I'm afraid that if the fileCount will grow it will become rather unusable . It seems that it creates individual links to each cell and that is killing my performance. Is there any way to establish lasting connection to perform all the operations and close the connection afterwards instead of reconnecting to each cell individually ?
In case you ask - mapping to local drive is not an option. This script will be distributed globally to people with minimal or no proficiency with computers and OS .
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Watch MrExcel Video

Forum statistics

Threads
1,126,934
Messages
5,621,688
Members
415,851
Latest member
JohnZ1156

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top