Refresh SQL Query via VBA?

[TK92]

Hi all,

I have a 3d party add on that refreshes pivot tables even when the file is closed. However, it cannot refresh a SQL query output that feeds some of my pivot tables. I manually need to refresh the SQL Query table every morning.

Would VBA be a good approach to take to try to auto refresh a SQL query table? Say once a night at say 3am? If so, can anyone provide the code for doing so, or a source where I can find that code? I have never used VBA before.

-Thanks!

[dflak]

I literally do this kind of thing in my sleep every morning; well, my computer at work does.

Pardon me if some of this is a bit overkill, but I do these things on an “industrial scale” so I developed things to be “data driven” so I don’t have to change any code.

There are two pieces of information you need.

One is how to run the task scheduler. You may need admin permissions to do this. There are several ways to run a task. Attached is a document that describes the system I use.

Basically, you fill in a script with the directory, excel spreadsheet name and the macro within the spreadsheet that you want to run. The script uses this information to run the macro in the target workbook.

Once you fill this information in, the script will run the target workbook every time it is opened. So if you need to see it or edit it, open excel, navigate to the script and hold down the SHIFT key while opening it. This temporarily turns off the on open macro that does the magic.

The script also creates a command line string that you can use in the task scheduler. Copy and paste this string in notepad for use when you set up the task scheduler.

It is important that both the script and the target workbook are in trusted locations. Do a web search on Excel Trusted Locations to find out how to do this.

The attached word document has some additional instructions on how to schedule a task.

The Windows task scheduler runs the script and the script runs the macro in the target workbook.

Now you will need a macro for the workbook in which you want to refresh the data. This is very simple. Go to the Data Ribbon and click on connections. Get the connection name you want.

Press F11 to open the macro editor and insert a module (normal module – not a class module).

Copy and paste the following code and replace the connection name where indicated. Yes, the quotes around the name are needed.

Please Login or Register  to view this content.

In this case, RefreshData is the name of the macro you enter into the script.

This assumes that you don’t have to change the SQL everyday like passing it a date as a parameter. There’s a way to do that but I won’t get unto it unless it’s necessary.

[dflak]

I forgot to mention: Cell B9 in the script is the pathname to the Excel Executable on your system. Shown is for Office 2013 on a Windows 7 system. Change this if your Excel.exe lives somewhere else.

I suggest you make a copy of the Script first and then rename it something like Run XYZ Report.xlsm. Then open the copy and fill in the data. The name of the script gets incorporated into the command string you paste into the task scheduler. If you wind up like me, you'll have dozens of scripts, so giving them descriptive names is a good idea.

[TK92]

Thank you so much for the detailed reply. I will give this a go over the next few days and let you know how it goes.

-Thank you!

[TK92]

Hi again,

Sorry, I didn't meant to leave you hanging with no reply after you put so much effort into your post.

Unfortunately I am very new to automation, VBA, scripts, task scheduler, etc. so your explanation unfortunately flew over my head. Thank you for trying though!