Export query to Excel and create pivot table

[Chris]

Please help or provide me with the code. My objective is
to export a query to Excel then once the data transfers to
Excel, I want to have the new workbook automatically open
and create a pivot table. I have already written the code
in Excel VBA to create the Piv Tbl, I just need to know
how to open the workbook and attach this code to the Open
Workbook event????

 

[Guest]

-----Original Message-----
Please help or provide me with the code. My objective is
to export a query to Excel then once the data transfers to
Excel, I want to have the new workbook automatically open
and create a pivot table. I have already written the code
in Excel VBA to create the Piv Tbl, I just need to know
how to open the workbook and attach this code to the Open
Workbook event????
.

Chris,

In Access, use the GetObject function;

Dim xlapp as Object
Dim xlbook as Object

Set xlapp = CreateObject("Excel.Application")
Set xlbook = xlapp.workbooks.Open("**Name of
Spreadsheet**")

' Make Excel and Workbook Visible
xlapp.Application.Visible = True
xlapp.Parent.Windows(1).Visible = True

You can then use the required code in Access by using the
reference to xlapp (use the with statement)

All rows of code should be preceded with a fullstop

With xlapp.Application

.cells(1, 1) = "This Text will appear in Cell A1"

' Save the Workbook
.Activeworkbook.Save

End With

Set xlbook = Nothing
Set xlapp = Nothing

Any constants used within Excel e.g. xlTop should be
replaced with the relevant value used in excel, e.g. -4162

I hope this helps.

Steve.