Gtrain
12-15-2007, 03:18 PM
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
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