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 varExcelApplica tion

varFilename = "C:\MyFile. xls"

'EXPORT DATA
DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel9, "MyQuery",
varFileName, True

'OPEN EXCEL HIDDEN
Set varExcelApplica tion = CreateObject("E xcel.Applicatio n")
varExcelApplica tion.Visible = False

'OPEN DOCUMENT
Call Shell("EXCEL """ & varFileName, vbMinimizedFocu s)
Set varCurrentFile = GetObject(varFi leName & ".xls")

'RENAME SHEET
varCurrentFile. ActiveSheet.Nam e = "MyData"

'ADD NEW SHEET
varCurrentFile. Sheets.Add

'RENAME SHEET
varCurrentFile. ActiveSheet.Nam e = "PivotSheet "

'CREATE PIVOT
varExcelApplica tion.ActiveWork book.PivotCache s.Add(SourceTyp e:=1, SourceData:
="'MyData'!A:P" ).CreatePivotTa ble TableDestinatio n:="'PivotSheet '!R3C1",
TableName:="Why DontYouWork", DefaultVersion: =10

'SAVE AND CLOSE
varCurrentFile. Save
varCurrentFile. Application.Qui t

End Sub

--
Message posted via AccessMonster.c om
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.Piv otTables("Pivot Table2").PivotC ache.Refresh

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

varCurrentFile. ActiveSheet.Piv otTables("WhyDo ntYouWork").Piv otCache.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.c om
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.c om
http://www.accessmonster.com/Uwe/For...ccess/200702/1