Access VBA calling procedures in Excel Module 1

[mkasson]

Developing an Access app using VBA. There is an Excel spreadsheet which I am opening for a more detailed analysis of the selected record. (No problem opening Excel.) After opening Excel, I would like to be able to call a procedure in the Excel file's code module. (I am passing the index key to the Excel procedure so it can look things up.)

I imagine its a simple tweak from what I've got:

In a procedure in my Access Form's Module:

Dim oApp As Object

Set oApp = CreateObject("Excel.Application")
oApp.Workbooks.Open (CurrentProject.Path + "\LienDb.xls")
oApp.Load1Lien (Form!LienKey) 'THE PROBLEM

--------------------------------------------------------------------------------



In my Excel ThisWorkbook Module:

Sub Load1Lien(LienNum As Integer)
' ... load the data relating to LienNum
End Sub

--------------------------------------------------------------------------------



I don't do any declaring or 'Public'ing. (Tried Public to no avail.)

Any help would be greatly appreciated.
- MSK

 

[TonyJollans]

Hi mkasson,

You just aren't finding your procedure because you are not being specific enough for Excel. Try telling it where it is ..

oApp.

Workbooks("LienDb.xls").

Load1Lien (Form!LienKey)

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[mkasson]

Bingo! Thanks Tony.