Building Excel Pivot Tables in Access

[PatrickJohnson]

So, I have a report I send out monthly. The end user needs both the source data and a pivot table summary, and both must be in Excel. So my thought was to export the source query from Access to an Excel sheet, then use Access vba to build a pivot from the data. Then the problems began.

First off, let me say that there will be some solutions I know will be posted, so i want to rebut the ones that will not apply:

1) I cannot build an excel template pivot that uses the access database as it's source. The user(s) the report is for do not have network security access to the location the database resides in.

2) The pivot table in Access sucks. Up to the Pivot table creation point my code takes about 5 to 15 seconds. When using the pivot table view of the query, the query alone takes longer. And I can't get rid of the totals line under each entry.

3) I've already recorded a macro in Excel in an attempt to modify it for Access, but even though this seems to be a common practice for many, I can't find a single explanation on the object methods that says "If the excel vba command is x, in Access it would be y." I've tried some obvious substitutions, such as replacing "ActiveSheet" with my current worksheet variable, "ActiveWorkbook" with my current workbook variable, etc. But it gets all the way to the pivot creation, then throws the "Do you want to replace the content of cells..." error in excel referring to the source sheet???

So now that those are out of the way, how do you get access vba to build the table? I've tried everything I know.

 

[Guus2005]

I created a database and added a table PARAM and a form Form1

form1 has two buttons, one to export the table to a new spreadsheet and one to do the magic.

Most of the magic was copied from Excel where i used the Macro Recorder to follow my moves. Then i copied the code from Excel to Access and changed ActiveWorkbook to wbExcel and ActiveWorksheet to wsExcel, two objects i had already created.

I also added a reference to the Excel Object library.

Try it out and let me know.

You still get a number of messages, but it is a start.

HTH

 

[PatrickJohnson]

Nope, same issue:

"Do you want to replace the contents of the destination cells in PARAM?"

It build the new pivot, but then attempts to place new data on the original sheet. Did the same thing when i tried the same process.

 

[ak_ls]

I suggest to do it this way. It involves copying VBA code to Excel workbook' module1. You may find it interesting if you are doing it first time. Basically you are creating a Excel workbook that has VBA Code, a Button and Data copied from Access tables.

1) First write Excel VBA code to create the Required Pivot Table.
This VBA code should work with the data copied from the Access to the Excel's Sheet1.
Store this Excel VBA code in an Access table's Memo field.

2) In Access Application, create an Excel object. Keep it invisible.
Set it's security level to trust VB project.
Open a new workbook with it.
By default the new workbook has sheet1 and Module1.
Copy the Excel VBA code stored in the access table's Memo field to Excel's Module1.

You can use following Access code to do it.

Sub AddCodeToModule1()
Dim MyExcelCode as String
MyExcelCode=Get it from Access table's Memo Field
MyExcel.VBE.ActiveVBProject.VBComponents("Module1").CodeModule.AddFromString (ExcelCode)
End Sub

3) Next write Access code to create a Button on the sheet1 and assocaite this button to "CreatePivot" macro whose code has been copied to module1. Also place some instruction below this button to use it to create Pivot table.

4) Transfer the required data to sheet1.

5) Prompt filedialog to Save the Excel object to users folder.

In this solution, user is required to click on the Button to create the pivot table.

I think you can make this solution work.

Ashok

 

[PatrickJohnson]

Thanks for the reply. I will give this a shot. However, I have to admit I don't like sprouting up modules and code in other files. When I run a routine, I would prefer all the code be performed within the Access application. There has to be a better way than remaking a new file every time.

And I'm also highly frustrated that I still haven't been able to find any resources on migrating code between the office applications. Why has this never been brought up?