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