Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Automation/Using xla file
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Excel with automation | Excel Discussion (Misc queries) | |||
how do i password protect an .xls file? how do i unlock it for automation. e.g. want to unlock and access a .xls from another .xls macro. | Excel Worksheet Functions | |||
Automation: Create Word file from Excel | Excel Programming | |||
Excel automation in VB.net | Excel Programming | |||
Excel Com Automation | Excel Programming |