Update pivot tables with task scheduler

[Gtrain]

Hi guys,
I am fairly new to cutting some vb, so some help would be greatly appreciated.

Ok my problem, I have a reporting tool which which generates pivot tables into excel but these need manually updating. I would like to setup a job in task scheduler to open the spreadsheet update pivot table/s, save,run a macro which will email the spreadsheet to a distribution list then exit.

i thought about having the email on close/save, but then i figured when i send it and someone else opens it, the same thing will happen when they open close etc.

I had a go at this, but clearly failed,

Dim objExcel
Dim wks As Worksheet
Dim pvt As PivotTable
Set objExcel = CreateObject("Excel.Application")
strExcel = "C:\test.xls"
objExcel.Visible=True
objExcel.Application.DisplayAlerts = False
objExcel.Workbooks.Open strExcel
For Each wks In Worksheets
For Each pvt In wks.PivotTables
pvt.RefreshTable
Next pvt
Next wks
msgbox "test"
objExcel.Application.DisplayAlerts = True
objExcel.ActiveWorkbook.Save
objExcel.Quit

i just put the msg box in there to see whether the field updated, i also didn't have a clue how to run the macro.

Any help would be greatly appreciated.
Thanks
gareth

[XLGibbs]

YOu can refresh all pivot tables/external data in a workbook by

ThisWorkbook.RefreshAll

No need to do each one individually in a loop.

your code is fine to create the excel object and open the file....but instead of your loop, just open the file and have that workbook do a RefreshAll

[VBA]
Dim objExcel as Object
Dim wks As Worksheet
Dim pvt As PivotTable
Dim wb as Workbook
Set objExcel = CreateObject("Excel.Application")
strExcel = "C:\test.xls"
objExcel.Visible=True
objExcel.Application.DisplayAlerts = False
Set Wb = objExcel.Workbooks.Open strExcel
With wb
.RefreshAll
.Close TRUE
End with

objExcel.Quit[/VBA]


I am not familiar with task scheduler, my point was just for simplicity...use RefreshAll to save code and time.

[Gtrain]

hmm no go, I also have the task scheduler under control, thanks

I get an error when i run saying
line:1
char:14
error: expected end of statement
code: 800A0401
source: Microsoft VBscript compilation error

any thoughts
I tried changing a few things without any success?

[XLGibbs]

Sorry, I can't debug remotely. "it doesn't work" just doesn't give me enough.

Line 1: char 14 seems to be a good place to start....not sure what you changed, or what you didn't, or what the other errors were indicated. The syntax I used was excel VBA, but I presume that you are not using Excel.

In VB the syntax would indeed be different as parts of the language in VB won't be as native as VBA would be (particularly with object references).

What is this code actually running in? because this isn't so much an excel question, but perhaps a VS 2005? or VBScript process question from another application?

[Gtrain]

Understandable mate, i have actual dropped this in to a notepad doc and saved as a vbs.

[XLGibbs]

well, in VBScript, there really isn't the same declaration syntax, so where I added that...it shouldn't be there.

It will be an object by default.

Rather, line one char 14 would be just after the text "Dim ObjExcel"

Remove "as Object" and see where it heads.
Dim objExcel , wb

Set objExcel = CreateObject("Excel.Application")
strExcel = "C:\test.xls"
objExcel.Visible=True
objExcel.Application.DisplayAlerts = False
Set Wb = objExcel.Workbooks.Open strExcel
With wb
.RefreshAll
.Close True
End With

objExcel.Quit