How do I declare an Excel add-in in Access?

  • Thread starter Thread starter Hennie
  • Start date Start date
H

Hennie

Good Day,

I need to transfer data to Excel from Access to do some statistics. I ran a
macro in Excel to do some Descriptive Analysis and received to following:

Application.Run "ATPVBAEN.XLA!Descr", ActiveSheet.Range("d3:d1876"),
ActiveSheet.Range("$H$63"), "C", False, True

In order to be able to do an Application.Run it seems to me that I need to
declare the add-in "ATPVBAEN.XLA" file first. I tried to declare it as
follow:

Dim xlApp As Application

and set it as follow:

Const adhcXLSName = "ATPVBAEN.XLA"
Const adhcXLSPath = "C:\Program Files\Microsoft
Office\Office10\Library\Analysis\"

Set xlApp = GetObject(adhcXLSPath & adhcXLSName)

If I run the program I receive the following message:

Run-time error '-2147467259 (80004005)

Automation error
Unspecified error

Thank you for your time.

Hennie
 
Check near the end of http://support.microsoft.com/?id=198571

Sub xlAddin()
Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")

' Opens the add-in, which is in the Analysis folder of the
' Excel Library Directory.
objExcel.workbooks.Open (objExcel.Application.librarypath & _
"\Analysis\atpvbaen.xla")

' Runs the AutoOpen macro in the add-in
objExcel.workbooks("atpvbaen.xla").RunAutoMacros (xlAutoOpen)
MsgBox objExcel.Application.Run("atpvbaen.xla!lcm", 5, 2)
objExcel.Quit
Set objExcel = Nothing
End Sub
 
Good day,

I copied the Sub and tested it as it was given. The it works fine but when
I try to add the additional line to it to do the statistics I run in to
problems.

I tried to mimic the Application .Run procedure As indicated furter on and
get error messages as indicated in both instances. I do not knw why I get
those error messages.

Thank you'

Hennie


Sub xlAddin()


Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")
Const LibraryPath = "C:\Program Files\Microsoft
Office\Office10\Library\Analysis\"


' Opens the add-in, which is in the Analysis folder of the
' Excel Library Directory.
objExcel.Workbooks.Open (objExcel.Application.LibraryPath & _
"\Analysis\atpvbaen.xla")

' Runs the AutoOpen macro in the add-in
objExcel.Workbooks("atpvbaen.xla").RunAutoMacros (xlAutoOpen)
MsgBox objExcel.Application.Run("atpvbaen.xla!lcm", 5, 2)

"No problem. Give a message box with a value of 10. No errors.


The following statement was obtained by running a macro in Excel to do
Desciptive Analysis.

"" Error message indicates "Object variable or with bloc variable not set.


objExcel.Application.Run "atpvbaen.xla!Descr",
objExcel.ActiveSheet.Range("e3:e18"), objExcel.ActiveSheet.Range("$H$6"),
"C", False, True

'Error message indicates "Compile error Expected ="

objExcel.Application.Run("atpvbaen.xla!Descr",
objExcel.ActiveSheet.Range("e3:e18"), objExcel.ActiveSheet.Range("$H$6"),
"C", False, True)

objExcel.Quit
Set objExcel = Nothing
End Sub
 
Unfortunately, my Excel Automation examples are all at the office.

I'm not sure you can use ActiveSheet in Automation: remember, your sheet
isn't really active (Access is)

See whether replacing that with something like Sheets("name of sheet") or
Sheets(number) works.
 
I replaced the "Activesheet" with various statements I could think of but
still received error messages and some information windows. Below is an
extract of the code that deals with this section. The rest of the code
works fine regarding formatting and transfer of data from Access to Excel.

I have listed the changes and the error or information I received below each
attempt.

This analysis is repeated about 65 times. (5 times per sample. 4 quarterly
and once annually)

Thank you.

Hennie

Dim objExcel As Excel.Application
Dim objXL As Object
Dim objWkb As Object
Dim objSht As Object

Const conSHT_NAME = "RSP"
Const LibraryPath = "C:\Program Files\Microsoft
Office\Office10\Library\Analysis\"

' Opens the add-in, which is in the Analysis folder of the
' Excel Library Directory.
Const conWKB_NAME = "G:\My Documents\Lennox\Misa.xls"

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open (objExcel.Application.LibraryPath & _
"\Analysis\atpvbaen.xla")
'#####################################################################

With objXL
.Visible = True

Set objWkb = .Workbooks.Open(conWKB_NAME) 'Misa.xls

Set objSht = objWkb.Worksheets(conSHT_NAME) 'RSP

objWkb.Windows("RSP").Visible = True

With objSht

'Do Descriptive Analysis with available data

objExcel.Application.Run "ATPVBAEN.XLA!Descr",
objWkb.Worksheets("RSP").Range("E3:E18"), _
objWkb.Worksheets("RSP").Range("$H$3"), "C", True, True

Information Window "Descriptive Statistics - Input must be a contiguous
reference.

objExcel.Application.Run "ATPVBAEN.XLA!Descr", objSht.Range("E3:E18"), _
objSht.Range("$H$3"), "C", True, True

Information Window "Descriptive Statistics - Input must be a contiguous
reference

objExcel.Application.Run "ATPVBAEN.XLA!Descr",
objXL.objWkb.Windows("RSP").Range("E3:E18"), _
objXL.objWkb.Windows("RSP").Range("$H$3"), "C", True, True

Error Message Object doesn't support this property or method

objExcel.Application.Run "ATPVBAEN.XLA!Descr",
objWkb.Windows("RSP").Range("E3:E18"), _
objWkb.Windows("RSP").Range("$H$3"), "C", True, True

Error Message Run-time error '9'. "Subscript out of range"


objExcel.Application.Run "ATPVBAEN.XLA!Descr", RSP.Range("E3:E18"), _
RSP.Range("$H$3"), "C", True, True

Error Message "Object required"


objExcel.Application.Run "ATPVBAEN.XLA!Descr",
objWkb.Worksheets(conSHT_NAME).Range("E3:E18"), _
objWkb.Worksheets(conSHT_NAME).Range("$H$3"), "C", True, True

Error Message Run-time error '9'. "Subscript out of range"


End With
End With
 
Afraid I don't know anything about that specific function, so I'm not sure
whether the errors are caused by calling it inappropriately, or by errors in
the automation.
 
Back
Top