Hello Experts,
I have a folder containing 33 excel files and all of them are Pivot Tables in one sheet.
All files are linked to One external Data Source in Access which gets updated once a month.
Please advise if all 33 pivot tables in 33 files can be updated (Refreshed) using a macro.
Best Regards/VKS
Yes. Try this: Will refresh and update every workbook in a directory. Just edit the directory line:
Please Login or Register to view this content.
Shelton A.
If Helpful, Add Reputaion!
Hello Shelton,
Thanks a lot for your time & my apologies for the delay in response. Since the folder is on work machine, I had to wait till I came back to work.
Pasted the folder on desktop and tried to run the code
Within a second got the dialogue box that All Pivot Tables are updated but none got updated and noticed that “\” was missing at the end of directory name & changed it to
sPath = "C:\Users\92281\Desktop\Mar\"
This time I got a run time error 429 : ActiveX component cant create object on line
For Each Sheet In ThisWorkbook.worksheets
Please note the only change that I have made is changing the file path.
Am I missing something. Please Advise.
Best Regards/VKS
Since you have only 1 sheet in each file lets bypass that line of code and tell me what it does. If you make it to the end but with no update then we may have to insert a line to open the main document until all workbooks have updated and then close it.
Please Login or Register to view this content.
Hi,
This time I got run time error 1004 : Refresh Table Method Of Pivot Table Class failed & the line is
Pivot.RefreshTable
Thanks once again
Best Regards/VKS
Hmmm I would need to see two of your workbooks. This can often happen if apostrophes are in your code somewhere in ascii format or if a name to a table is spelled wrong. Either way I would need to see a couple of workbooks to isolate the issue as the code runs fine for me.
Hello Shelton,
Was travelling and couldn't respond earlier. Thanks for your time and suggestion but it is not possible for me to share the original files.
You already have rep to say thank you for your effort and time.
Best Regards/VKS
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks