I have a folder with many Excel files. All of them have data connections that connect to a database. I want to write a macro that refreshes all the data connections for all the files in the folder. Can anyone point in the right direction to write this macro? Is this even possible?
Asked
Active
Viewed 780 times
0
-
2I haven't done this, but I had a former coworker tell me that for this type of work he would loop through and open all files in the folder individually, update the links as part of the loop, then close each once updated. Not sure how efficient that was, but it was his approach. – Cyril May 01 '18 at 19:42
-
1If I had to do this, I would probably 1)[Loop through all the files in a directory](https://stackoverflow.com/questions/27793259/loop-through-workbooks-in-the-same-folder-and-do-the-same-excel-task-for-all-vba) 2)[open each one](https://stackoverflow.com/questions/19157385/how-to-open-a-workbook-specifying-its-path) 3) [Run the `.RefreshAll()` method of the currently open workbook](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/workbook-refreshall-method-excel) 4) [save and close the workbook](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/workbook-close-method-excel) – JNevill May 01 '18 at 21:17
-
Does .RefreshAll() refresh the pivot table or the data connection? – Airbum88 May 02 '18 at 14:18