Excel 2010 - Schedule AutoRefresh MS Query

**6409123** · 01-16-2011, 09:09 PM

Hi,

I have more than 80 huge excel files with Pivot Tables built using get data from external source(oracle server). Every month, I have to open each of these files and click on refresh to get updated data. In short, I would like to schedule an auto-refresh of each of these files so I can save more than 15 hours a month.
Can someone please help write a VB code that will do help me accomplish this?

You can use followings in your answer.
I am using Excel 2010.
File Name: FTP_SUMMARY, Sheet called "DATA" imports data from external source using MS Query.
There are 4 pivot tables on 4 sheets created from DATA sheet.
Sheet1: Settled Months, Sheet2: Summary, Sheet3: PMPM Summary, Sheet4: Membership

I would like to Refresh this file on 14th of every month.

I will really really appreciate for your help.

thanks

Thanks for your time.

**snb** · 01-17-2011, 05:38 AM

I assume you will open Excel at least once a day.

You can put those workbooks in the same workspace, e.g "E:\refreshing.xlw"
In this example I assume that each file's name in this workspace starts with "refresh " (I used 'refresh 001.xls', 'refresh 002.xls', etc)

Please Login or Register  to view this content.

You can store this macro in the Workbook module of personal.xls.

**6409123** · 01-17-2011, 09:48 PM

Originally Posted by snb

I assume you will open Excel at least once a day.

You can put those workbooks in the same workspace, e.g "E:\refreshing.xlw"
In this example I assume that each file's name in this workspace starts with "refresh " (I used 'refresh 001.xls', 'refresh 002.xls', etc)

Please Login or Register  to view this content.

You can store this macro in the Workbook module of personal.xls.

Actually, After I store the macro, I do not want to open these files again. They should get refreshed on the 14th of every month. Also, I am very new to Macros. Can you please explain how do I store macro? is it right click on sheet name/ view code and paste it. It will be really helpful if you can write the code using the naming conventions given in the question.

thanks