run stored excel VBA procedure from ACCESS

[Angelo]

anyone know of anyway to call a subprocedure 'workbook_open()' embedded
in an excel workbook from VB in access? i'm working on some code that
was already written for an access db and has data exported to a text
file and when the excel file opens up, it loads this subprocedure
'workbook_open()' that links to this text file and processes that data.
Well, now i have to tweak this code and so i need to call this
subprocedure to run from the ADO connection i have open in ACCESS
without me having to open excel. Is there any way to do this?

Any help is much appreciated.
Angelo

[Rich P]

If you are referring to a subroutine written in an Excel VBA code module
here is how you invoke a sub in Excel from Access - you have to use COM
automation. First, while inside an Access code module - goto
tools/references, make a reference to the Microsoft Excel Object
Library. Now you can use this code:

Sub runExcelProcFromAccess()
Dim xlObj as Excel.Application, wkbk As Excel.WorkBook
Set xlObj = CreateObject("Excel.Application.10")
Set wkbk = xlObj.Workbooks.Open("c:\somedir\yourwkbk.xls")
wkbk.Application.Run "subSuchnSuch"
wkbk.Close
xlObj.Quit
Set xlObj = Nothing
End Sub

So you are still openeing a workbook, but only programmatically, not
physically. Also in this line

wkbk.Application.Run "subSuchnSuch"

if your sub takes arguments you can pass the arguments like this:

wkbk.Application.Run "subSuchnSuch", arg1, arg2

where arg1, arg2 could be an integer value, string value, and array.

Rich

*** Sent via Developersdex http://www.developersdex.com ***