How do I use an Excel libray in Access

[henniec]

Good day,

I am transfering data to an Excel workbook and need to do several Statistical analysis with the data. I need to declare the library and activate it.

I am not sure how the start it in code so that I can run a desciptive analysis in Excel for Access.

The Run function I need to do as created while running a macro in Excel is as follow.

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

Would appreciate help with the Dim statement and anythiing else that will allow me to complete the above statement.

Thanks.

Hennie

 

[Mike555]

To add references to libraries, go into the VBA Window and go to Tools > References and place a check box in the libraries you need.

--
Mike

Why make it simple and efficient when it can be complex and wonderful?

 

[henniec]

The Excel referene has been checked but it does not do the Application.run function.

It must be something similar as
Dim objXL as object
Dim objWbk as object

Thanks.

Hennie

 

[Mike555]

Make sure ALL Excel references are checked.

--
Mike

Why make it simple and efficient when it can be complex and wonderful?

 

[henniec]

Good Day,

I have checked all reference for Excel. I do think that I made an error as to refering it as a library . It is in fact an add-in file.

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 recieve the following message:

Run-time error '-2147467259 (80004005)

Automation error
Unspecified error

Thank you for your time.

Hennie

 

[PHV]

Application.Run "ATPVBAEN.XLA!Descr", ActiveSheet.Range("d3:d1876"), ActiveSheet.Range("$H$63"), "C", False, True
Is this line of code in an access module or Excel module ?
If in access VBA, you have to change all references (explicit or implicit) to the excel application to references to your instantiated excel object, like this:
XL.Run "ATPVBAEN.XLA!Descr", XL.ActiveSheet.Range("d3:d1876"), XL.ActiveSheet.Range("$H$63"), "C", False, True
If the AddIn is not installed, you may consider something like this:
objXL.AddIns("analysis toolpak").Installed = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[henniec]

Thank you for your reply. I will look at your suggestion. and see whether it may help in fine tuning the application.

I received the following answer from the Microsoft Access news group that deals with my problem and attach it as I received it.

Once again thank you for your time and the reply.

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
>
>
> --
> Doug Steele, Microsoft Access MVP
>

http://I.Am/DougSteele

> (no e-mails, please!)