Refresh All vs. Refresh All Data Connections

Do the SQL and Bing Queries show up as data connections? If they do, then I don't understand why they are not refreshing. What do the connection strings for these look like?

Hi dflak,

Thank you for the response. Yes, they are showing up as connections. After spending sometime researching this morning, I have changed the VBA code to the following:

Please Login or Register  to view this content.

Things I changed:

1. I added a statement for refreshing all connections rather than refreshing all. I still kept refresh all to refresh the pivot tables after connections get updated (not sure if I needed to).
2. I added a delay of 5 seconds. Another forum post I saw recommended trying this because sometimes the macro might run too fast, and reach the end of the code before the refresh is finished.
3. I unchecked "Enable Background Refresh" from the connection properties as in another thread someone mentioned this too could be preventing a proper refresh.


I will report back tomorrow morning if it ran properly or not, for anyone else who has this problem or might be interested.

Another thing to check is under connections properties, the default is to run the query in the background. I almost always turn this off.

@TK92

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

Originally Posted by TK92

Okay, reporting back, although a few days late. All the changes above that you and I mentioned seemed to do the trick. The next challenge is getting the task scheduler to run when I'm logged off. But, that is another issue.

-Thanks!

Just so happens I do that a lot. So much so, that I created a system. The way it works is that the task scheduler runs a script and the script runs the target file.

They way I am set up is that I have a script directory where I keep all my scripts. In my case I put it under AppData since it's "out of the way" and just seems like a good place to collect miscellaneous stuff. All my target files are under C:/Users/dflak/Local - I have a subdirectory for each of my reports here. You can set up your system any way you want, but make sure that the directory containing the scripts and the directory containing the reports are trusted locations.

When you want to set up a report to run, make a copy of the script template and give it a name like Run Report XLZ.xlsm. Then open the copy. The first time you open the script, no macros will fire since the first three rows in column B are blank. Otherwise when the script is opened, it has an on open event that runs it. If you need to look at the script after filling it in, then open Excel, navigate to it in Excel and hold down the SHIFT key while opening it. Otherwise, the script will run the target program.

You may want to open the script without running it to check cell B6. This has a status. There are basically three statuses:
"The program ran successfully at mm/dd/yy hh:mm"
"File XYZ cannot be found" - you have a bad path
An error number and message if the script failed.

In the script, fill in cells B2:B4 with the path name to the target spreadsheet (I usually copy and paste this), the name of the target worksheet (copy and paste too) and the name of the macro you want to run in the target spreadsheet. I usually call this Executive since it's the macro in the target worksheet that runs all the other macros in the spreadsheet.

Next check cell B9. This may vary depending on the version of Excel you have. Change this in the template if you have a different path to the Excel executable so when you make copies they have the correct information here and you only have to do this once. Do keep this in mind. If you update to a newer version of Excel, you may have to change your scripts and tasks.

Cell B10 is the local path to the script. The script figures this out on its own. You don't have to change this.

Cell B11 is the key cell. Copy and paste this string into notepad. When you get to the part of the task scheduler where it asks for an action, select run a program and paste this string in. The task scheduler will tell you that this looks like it has an argument, and it does, so click OK.

That's it! The task scheduler will open the script. The script has an on open event which runs the code it needs to open the target workbook and execute the specified macro.

The following article explains what I just described with some pictures. At the very bottom of the article is a link that downloads a copy of the script template.

http://www.utteraccess.com/wiki/inde...a_Macro_to_Run

Oh and a bit more on organization. I created a folder in the task scheduler called MyTasks to keep my tasks separate from all the others that windows runs. I have a bat file with the following code

Please Login or Register  to view this content.

This bat file produces a list of tasks, when they are scheduled to run next and if they are disabled.

The task scheduler is fairly ignorant of its environment. It doesn't know about mapped drives and needs a full, absolute path to just about any file it uses. It does not know of other programs that are running. I also found out that you have to be logged in if you want to use Outlook to do mail functions. Outlook doesn't actually have to be open and running, but you do need to be logged in. I've not had problems with other programs so far. I haven't tried Word or PowerPoint.

And a reminder. When you change your login password, you will have to change the password in each of your scripts. I put the password in notepad and copy it. Then I right click each script, select properties and close it and paste the password in. At the moment I have 23 tasks I have to do this to. Now you know why I came up with an assembly line process.