472,952 Members | 2,825 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,952 software developers and data experts.

Create Excel Pivot Table from Access module

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"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyQuery",
varFileName, True

Set varExcelApplication = CreateObject("Excel.Application")
varExcelApplication.Visible = False

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

varCurrentFile.ActiveSheet.Name = "MyData"


varCurrentFile.ActiveSheet.Name = "PivotSheet"

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


End Sub

Message posted via AccessMonster.com

Feb 24 '07 #1
2 13429
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

Thanks very much for reading!

Message posted via AccessMonster.com

Feb 25 '07 #2

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

oh well - thanks for reading!

Message posted via AccessMonster.com

Feb 26 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: John | last post by:
Is there a way to code the button that's available in the query window--microsoft excel icon that exports to excel. I know transferspreadsheet will do this---but I want the query, which is in a...
by: Alexander Mandl | last post by:
Hello I have a pivot table in an Access form and open the pivot Table (Excel OLE Object) from within Access. In the Excel Table is a macro (signed) (in teh open event) running when opening the...
by: Jerome Ranch | last post by:
I consider myself an Excel PT wizard of sorts, but now I have a situation with so much infromationthat I need to categorize and summarize, that I will use access to manage it. Interestingly,...
by: Jerome Ranch | last post by:
Okay So I've got pivot tables setup in Access 2003. Only about 30K records in the current 2005 databases...the pivots summarize the info in a number of nice ways. I need to get the pivot tables...
by: Decreenisi | last post by:
Hi, I have to convert an existing spreadsheet for rejects into an access database. My problem is I use a load of lookup tabels in excel. How do I approch this in access. Also, just a general...
by: benchpolo | last post by:
I have data extracted from Access db to Excel with a pivot table. Somehow, I am having issues with the pivot table were it doesnt update the totals. For example, the first extract i did in Access...
by: drt | last post by:
NEDERLANDS: Hallo, Ik heb eigen functies gemaakt in access, die werken perfect in de access query. Zodra ik echter vanuit excel een draaitabel maakt naar de access query (als een externe...
by: Gary Dunne | last post by:
Hi All, I'm struggling to find the correct syntax for creating a Pivot table in an excel file via VB . Net 2005 The Excel help file is not particularly helpful where parameters are concerned...
by: redman08 | last post by:
I have produced an Excel (2007) worksheet, with which I want to create a Pivot Table. This would all be done from a module run on an Access 2007 database. Please can anyone supply some simple...
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.