OK, here we go:
Create a stored procedure that looks something like this:
Create a new Excel spreadsheet.
Create a new data source, like this:
Since this is a demo, we're going to use the 'sa' password and we're going to hard code it. I know that's not best practice, if anyone here used that in production you'd get a smack in the back of the head. But the point here is to demo the technique in as few steps as possible
Connect to the first table that you see. Don't worry, we're going to change it in a minute. You might need to change the database here.
Use the same name in all three locations (trust me). Remove all spaces from the name (again, trust me). Click Finish.
The Import Data dialog will pop up. Change the worksheet location to be =$A$3
Click Properties, then choose the Definition tab. Edit the Command Type, The Command Text, and choose 'save password' (again, not best practice)
Now, type ALT+F8 to open the macro dialog. Name it RefreshQuery and click Create
Enter in the text that you see below and close the VBA window
ALT + F8 again to open the Macro dialog. Click options and enter R in the shortcut text box (R for Refresh)
Enter 4750 in cell B1 and CTRL+R to Refresh in cell C1, and we're done. Press CTRL+ R and you should get the result below. Please try, and give me a comment below if I've missed anything.
