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

Problem fixed. There was a small typo later down in the Pivot Table code. The
error message I was getting and the line that was flagged as a problem was a
red herring. Shame the Excel VBA syntax checker didn't spot it and flag it
up!!
--
Peter Schmidt
Ross-on-Wye, UK


"Pete" wrote:

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

Hi Pete,

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!

It may be, that there is no active sheet yet when your code runs. An add-in has no
active sheets, so if there is no other file open yet,

intLastRow = .ActiveSheet.UsedRange.Rows.Count

fails.

What you could do is use an OnTime event to schedule your macro to be run, containing
the offending code. That way, Excel can first finish whatever it needs to load.

Sub myMacro()
Application.OnTime Now, "MyMacroContinued"
End Sub

Sub MyMacroContinued()
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

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com