Create Excel Pivot Table from Access module

[LittlePhil via AccessMonster.com]

Someone please help before i start to cry.

I'm trying to export from Access to Excel, then create a new excel sheet with
a pivot table to display the data held in columns A:P. I get the error
message "Run Time error 91: Object variable or with block variable not set"
on the "CREATE PIVOT" line below and can't find a way round.

Please someone. Help!

Option Compare Database
Option Explicit

Public Sub ftnMonitoring()

Dim varFileName As String
Dim varCurrentFile As Object
Dim varExcelApplication

varFilename = "C:\MyFile.xls"

'EXPORT DATA
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyQuery",
varFileName, True

'OPEN EXCEL HIDDEN
Set varExcelApplication = CreateObject("Excel.Application")
varExcelApplication.Visible = False

'OPEN DOCUMENT
Call Shell("EXCEL """ & varFileName, vbMinimizedFocus)
Set varCurrentFile = GetObject(varFileName & ".xls")

'RENAME SHEET
varCurrentFile.ActiveSheet.Name = "MyData"

'ADD NEW SHEET
varCurrentFile.Sheets.Add

'RENAME SHEET
varCurrentFile.ActiveSheet.Name = "PivotSheet"

'CREATE PIVOT
varExcelApplication.ActiveWorkbook.PivotCaches.Add (SourceType:=1, SourceData:
="'MyData'!A:P").CreatePivotTable TableDestination:="'PivotSheet'!R3C1",
TableName:="WhyDontYouWork", DefaultVersion:=10

'SAVE AND CLOSE
varCurrentFile.Save
varCurrentFile.Application.Quit

End Sub

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200702/1

[LittlePhil via AccessMonster.com]

Ok so instead of crying i took a break; I've created some template files
which i copy and paste, then transfer data into; these have pivot tables all
set up and ready to go all i need to do now is convert;

ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh

into access vba, so for my previous example this would be;

varCurrentFile.ActiveSheet.PivotTables("WhyDontYou Work").PivotCache.Refresh

The problem seems to lie in that i cannot define a pivotcache; any ideas
anyone?

Thanks very much for reading!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200702/1

[LittlePhil via AccessMonster.com]

:-/

I don't get it - i tried this again today and it now works?! i got an error
message up to yesterday saying i had to define a pivotcache in my access
module....

oh well - thanks for reading!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200702/1