Create a Pivot Table in Excel from Access VBA Module

[njacovides]

Hi,
I have created an export function in MS Access to automatically export a table to MS Excel. Following the export, I am trying to create a pivot table in the excel workbook just created from access to a new worksheet. As a first step I have created the function to create the pivot table as a module in excel and it works just fine. I am now trying to migrate the module in Access, so that it runs as soon as the Excel workbook is created and it keeps reporting a run-time error '1004' for line 14.

Any help would be greatly appreciated!!

Function CreatePivot(fileName As String)
Dim myWorkbook As Excel.workbook
Dim WorkbookPath As String
Dim WSD As Excel.Worksheet
Dim WSPT As Excel.Worksheet
Dim PTCache As Excel.PivotCache
Dim PT As Excel.PivotTable
Dim PRange As Excel.Range
Dim FinalRow As Long
Dim FinalCol As Long

WorkbookPath = Application.CurrentProject.Path
Set myWorkbook = GetObject(fileName)

Set WSD = myWorkbook.Worksheets("pending faults (DATA)")
Set WSPT = myWorkbook.Sheets.Add(after:=Sheets(1), Type:=xlWorksheet, Count:=1)
WSPT.Name = "PivotTable"


' Delete any prior pivot tables
'For Each PT In WSPT.PivotTables
' PT.TableRange2.Clear
'Next PT

' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(myWorkbook.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, myWorkbook.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = myWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange)

' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSPT. _
Cells(2, 2), TableName:="PivotTable1")

' Turn off updating while building the table
PT.ManualUpdate = True

' Set up the row & column fields
PT.AddFields RowFields:=Array("days dalay"), _
ColumnFields:="Region"

' Set up the data fields
With PT.PivotFields("Region")
.Orientation = xlDataField
.Function = xlCount
.Position = 1
End With

' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True

' Turn instance of Excel over to end user and release
' any outstanding object references.
myWorkbook.UserControl = True
Set myWorkbook = Nothing

End Function

 

[xlbo]

you are working form Access rather than excel now so you need to set references to excel and find the excel object eg

dim objXl as object
dim xlWb as Excel.workbook
Set objXL = Getobject(, "Excel.Application")
set xlWb = objXL.activeworkbook

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[njacovides]

Thanks Geoff,
looks like its at least opened the workbook correctly. However I now get a run-time error 438 on the line where I try to determine the worksheet final row.
> FinalRow = WSD.Cells(myWorkbook.Rows.Count, 1).End(xlUp).Row

How would I call the worksheet "WSD" now that I am working in access? My feeling is that I am not calling the worksheet correctly but I can't figure out what is wrong.

N.

 

[njacovides]

Never mind...
Just worked it out. Many thanks again.

Rgds,
N.

 

[xlbo]

no worries - just for reference, once you have set an application object for excel, you can reference sheets etc just as if you were in excel VBA eg.

dim objXl as object
dim xlWb as Excel.workbook
Set objXL = Getobject(, "Excel.Application")
set xlWb = objXL.activeworkbook

with xlwb
.sheets("Sheetname").Range("RangeAddress").select
end with

all you are doing is adding another qualification in there (the excel application object)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question