Create Excel Pivot tables from MS Access





Have you tried creating a crosstab query? That may do what you want. If you
want to control Excel, while inside Access, try this sub:

Option Compare Database
Option Explicit ' Use this to make sure your variables are defined
' One way to be able to use these objects throughout the Module is to
Declare them here, and not in a Sub
Private objExcel As Excel.Application
Private xlWB As Excel.Workbook
Private xlWS As Excel.Worksheet

Sub Rep()
Dim strFile As String
strFile = "C:\MyExcelWorkbook.xls"
‘Of course, this is just an example; put the actual path to your actual file

' Opens Excel and makes it Visible
Set objExcel = New Excel.Application
objExcel.Visible = True

' Opens up a Workbook
Set xlWB = objExcel.Workbooks.Open(strFile)

' Sets the Workseet to the last active sheet - Better to use the commented
version and use the name of the sheet.
Set xlWS = xlWB.ActiveSheet

' Set xlWS = xlWB("Sheet1")
With xlWS ' You are now working with the Named file and the named worksheet

' Your Excel code begins here…you can record a macro and make the process
super easy!!

End With
' Close and Cleanup

xlWB.SaveAs xlSaveFile
Set xlapp = Nothing

End Sub

Make sure to set a reference to Excel.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question