By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,640 Members | 1,581 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,640 IT Pros & Developers. It's quick & easy.

Create Excel Pivot Table from Access module

P: n/a
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

Feb 24 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Feb 25 '07 #2

P: n/a
:-/

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

Feb 26 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.