[RESOLVED] Run Excel Module code from Access...

[strobinson1]

I am opening an excel spreadsheet from Access called test.xls like below. I have a Sub saved in the spreadsheet in Module1 called Test.

How do I run that from Access???

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlWSheet As Excel.Worksheet
 
Set xlApp = New Excel.Application
xlApp.Workbooks.Open ("C:\Test.xls")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks(1)
Set xlWSheet = xlApp.Worksheets(1)
 
'call xlapp.module1.test   ????????
 
 
xlBook.Module1.test

[strobinson1]

ok,

sorted that one with

Call xlApp.Run("mdltest.test")

Any ideas on how to update Excel variables from Access??

[si_the_geek]

The variables are running in separate processes, and therefore cannot be updated directly.

I would recommend making subs in Excel which can update them for you, eg:

Public Sub UpdateVar_Fred (NewValue as Integer)
  Fred = NewValue 
End Sub

..hopefully you can call it like this:


Call xlApp.Run("mdltest.UpdateVar_Fred 4")

[strobinson1]

I've actually done it by copying my Access variable data to a cell on the spreadsheet and then in the excel code updating the excel variable by looking at that cell, and finally emptying the cell.

Will try your method now.

Is there a way to send data the other way aswell?? ie the Err value should the process fail in excel, i want to store the Err value in Access.....???

[strobinson1]

Used syntax like this in Access to send Access Variable to Excel Function:

xlApp.Run "test", strName

Still need to work out how to return a value back to Access from Excel....?????

[si_the_geek]

I would assume that Run itself is a function, and returns the value from the function that you call. If that is true, the following should work:

myVar = xlApp.Run ("test", strName)


Note that if the parameters (like strName above) are defined as ByRef you may be able to alter them in the function/sub and then read them on return to the caller program.

[strobinson1]

Yup...that's exactly how i did it!!

Cheers!