I am using Excel to analyse data exported from an Access database. Originally
I automated Excel from Access. This worked fine, but in order to provide more
flexibility I decided to avoid hard coding the macro code into Access and
move it into Excel and store the name of the macro to run in my Access
Reports table. I originally created the macros in Personal.xls and this
worked fine, but following suggestions in the newsgroup, moved the code to an
xla file. This is held on a central fileshare and is opened by Access after
the query data has been exported and prior to the relevant macro being run.

However I now get an error when the Excel macro runs €œRuntime error 91 €“
Object variable or With block variable not set€� on the €œintLastRow = €¦€� line
of code.

I have tried various ways of setting the reference to the Excel application
object but to no avail. The frustrating thing is that it worked fine when it
was in Personal.xls! I am sure it is a stupid error, but I just can't see it
for looking!

Thanks in anticipation

Access Export Code
Public Sub cmdExport_Click()
'*** Export Selected Report and Run Excel Macro (If Specified) ***
On Error Resume Next
Dim strReport As String, intReport As Integer, varParam1 As Variant
strReport = Me!subfrmReport!txtReport
intReport = Me!subfrmReport!cboReport
varParam1 = Me!subfrmReport!txtParameter1
If intReport = 2 Then '***Query ***
DoCmd.OutputTo acOutputQuery, strReport, acFormatXLS, strReport &
".xls", True
If Not IsNull(varParam1) Then '*** Run Macro Specified ***
Dim xlAppn As Excel.Application
Set xlAppn = CreateObject("Excel.Application")
With xlAppn
.Workbooks.Open (oApp.cCommonFilesPath & "MyDatabase.xla")
.Run (varParam1)
End With
Set xlAppn = Nothing
End If
End If
End Sub


MyDatabase.xla
Sub MyMacro
Dim xlApp As Excel.Application
Dim intLastRow As Long, strRange As String
Set xlApp = GetObject(, "Excel.Application")

With xlApp
.ScreenUpdating = False
intLastRow = .ActiveSheet.UsedRange.Rows.Count
strRange = "quniAccountsExport!R1C1:R" & intLastRow & "C16"
.ActiveWorkbook.PivotCaches.Add(SourceType:=xlData base, SourceData:= _
strRange).CreatePivotTable TableDestination:="", TableName:= _
"Financial Accounts", DefaultVersion:=xlPivotTableVersion10
.ActiveSheet.PivotTableWizard
TableDestination:=.ActiveSheet.Cells(3, 1)
.ActiveSheet.PivotTables("Financial Accounts").NullString = "0"
€¦etc

--
Peter Schmidt
Ross-on-Wye, UK