Auto update an Excel file, save and close.

October 4, 2011 at 07:38:52
Specs: Windows 7
We have an Excel file that on open runs an SQL query, updates a pivot table, prints a pdf, saves itself, and closes. Everything works fine when the file is opened manually. However, when the file is opened with a Windows "Scheduled Task," it opens, but skips the Refresh steps to update the SQL and pivot data. The result is a PDF with a new date/time stamp, but unrefreshed data. Help!

See More: Auto update an Excel file, save and close.

Report •


#1
October 5, 2011 at 13:51:06
What mechanism are you using to run the Refresh?

Is it a Workbook_Open macro?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 5, 2011 at 14:37:34
Here's the long version of the entire code in "ThisWorkbook": (We have tried several less complex versions with the same results.)


Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub

Private Sub Workbook_Open()
ActiveWorkbook.Connections("Query from Parris").Refresh
ActiveWorkbook.Connections("Query from Parris1").Refresh
ActiveWorkbook.RefreshAll

For Each ws In ActiveWorkbook.Worksheets

For Each pt In ws.PivotTables

pt.RefreshTable

Next pt

Next ws

Application.DisplayAlerts = False 'IT WORKS TO DISABLE ALERT PROMPT


With ActiveSheet
.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="C:\Users\David\Dropbox\Parris\Sales Analysis Tools\Sales Analysis.pdf", _
OpenAfterPublish:=False
End With

'Filename = "C:\SalesSummary.pdf"
'SendKeys Filename & "{ENTER}", False
'ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
'"Adobe PDF on Ne06:", Collate:=True

ActiveWorkbook.Close SaveChanges:=True
Application.DisplayAlerts = True 'RESETS DISPLAY ALERTS
End Sub


Report •

#3
October 5, 2011 at 19:42:47
FYI...Before posting any more code in this forum, please click on the blue line at the end of this post and read the instructions found via that link.

I'd try some tests to see why/if it's skipping the Refresh.

Try sprinkling in a couple of MsgBox lines near the top of the code to see if they run:
e.g.

 
Private Sub Workbook_Open()
   MsgBox "Step 1...Do you see me?"
      ActiveWorkbook.Connections("Query from Parris").Refresh
   MsgBox "Step 2...Do you see me?"
     ActiveWorkbook.Connections("Query from Parris1").Refresh
   MsgBox "Step 3...Do you see me?"
     ActiveWorkbook.RefreshAll
   etc...

If the MsgBox's work, and the delay allows the Refreshes to execute, maybe you could add some "delay timers" via the Wait method to slow the code down.

It's just a thought...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
October 11, 2011 at 10:20:00
We tried the "Wait" method. It seems the "Wait" method makes Excel stop EVERYTHING and wait. Is there a Wait-until-action-is-complete-before-going-to-the-next-step method? We're totally at a loss on this one. We cannot make it work when the file opens as a Windows Scheduled Task.

Report •

#5
October 11, 2011 at 12:18:35
Does your answer imply that the delays caused by the message boxes allowed the Refresh to occur?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
October 12, 2011 at 11:29:03
When we launch the spreadsheet using Windows Scheduled Task, the refreshes do not work with the "wait" method or with the message boxes. With the message boxes, the time updates (same as without msg boxes), the routine pauses until the user clicks "ok" on the msg boxes, but the refresh does not work. When we manually launch the spreadsheet, everything runs perfectly.

Report •

#7
October 12, 2011 at 16:05:07
key is what account is being used for the scheduled task. It should equal or exceed the rights of the user that can run the excel sheet fine.

Answers are only as good as the information you provide.
How to properly post a question:
Sorry no tech support via PM's


Report •

#8
October 12, 2011 at 17:36:40
Running Scheduled Task using admin rights and as a user with admin rights. no luck.

Report •

#9
October 13, 2011 at 08:08:35
logon as this user. run the file. same results as with scheduler?

might want to review the event log for scheduled task errors that may give a clue to what is going on.

Answers are only as good as the information you provide.
How to properly post a question:
Sorry no tech support via PM's


Report •

#10
October 13, 2011 at 09:23:01
Windows Login, SQL driver login, Scheduled Event login, and Excel creator all same user with admin rights. Does not work. No task errors on event log.

Report •

#11
October 14, 2011 at 17:12:37
I don't see an answer to my question in #9

Answers are only as good as the information you provide.
How to properly post a question:
Sorry no tech support via PM's


Report •

#12
October 15, 2011 at 05:13:05
If we log on as the user and run the file it works fine. If we run the same file using the scheduler with the same user and passwords it does not work. I even went back into the schedule task properties and double check it was using the same user. Also FYI, this does not work on any of our test computers using the task scheduler in Windows 7 Pro or NT.

Report •

#13
October 15, 2011 at 06:15:03
wanderer:

Just jumping in to make sure that you noticed the subtlety of the issue:

If I understand the OP correctly, in both cases, manual or scheduled, the Excel file opens and the macro is "executed" - with a twist:

When the Scheduler is used, the SQL query line do not appear to be executing, therefore the Pivot Tables do not get refreshed.

gigageek:

Have you been able to determine if it is only the query lines that do not execute? In other words, we know that the pivot table are not showing any new data since the queries do not execute, but do you have a way of determining whether the pt.RefreshTable section is executed?

Perhaps you could add a MsgBox line inside that loop to see if that section is executing. That would narrow the problem down to only the query section and maybe help narrow the scope of the problem.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
October 18, 2011 at 10:49:29
if the same user account that manually works, should also work when scheduled. There isn't anything in the scheduler that I know of that makes a difference. If the sql query isn't being executed I would be looking at either

parsing each line in the script to put output to a file so you can read errors
or
review the scheduler log

This maybe of help
http://support.microsoft.com/kb/308558

Answers are only as good as the information you provide.
How to properly post a question:
Sorry no tech support via PM's


Report •


Ask Question