Automate Excel from Access with arguments

[TimSzrejter]

I have an Excel 97 file which contains some VBA procedures. From Access, I am able to open Excel and open the file that contains the procedures. What is the correct way to call this procedure inside the Excel file from MS Access 97 and also pass arguments to the procedure.

Thanks for any help!

Tim Szrejter

 

[JohnYingling]

Is this what you mean. I use object to aviod version incompatibilties and, "late binding" isn't really a performance handicap unless you're calling Excel "a bunch".
Dim objExcel as object
Set objExcel = ' however you opened it
objExcel.procname arg1, arg2, arg3, ....
strValue = objExcel.functionorpropertyname(arg1, arg2, arg3,...)

objExcel.Property = arg1

http://WWW.VBCompare.Com

 

[TimSzrejter]

I just got it working though it didn't work quite the way you showed.

objExcel.Run "procname", "arg1"

Not sure about the function cause I didn't need to return anything so I didn't even try that one out.

Thanks!!

 

[JohnYingling]

Ok. I forgot that the Procs and Functions are in the WorkBook. This works as a VBS script.

Dim objWk
Dim ObjXL
Set objWk = getobject("D:\tclooko\tclook.xls")
Set objXL = objWk.Application
objxl.visible = true
objWk.DisplayWKMsg "Executed objwk"
objWk.Close false
objxl.Quit

With this Procedure in the Workbook

Public Sub DisplayWKMsg(strMsg As String)
    MsgBox strMsg
End Sub

http://WWW.VBCompare.Com