Calling a Public Sub inside Excel from Access

  • Thread starter Thread starter Carl Rapson
  • Start date Start date
C

Carl Rapson

I have an Excel spreadsheet that I am opening from within Access:

Dim xl As Excel.Application
Dim wrk As Excel.Workbook
Set xl = New Excel.Application
Set wrk = xl.Workbooks.Open(Filename:="file.xls")

I would like to call a routine within the Excel file after opening it (I
want to set up a commandbar depending on how and where Excel is opening from
my database application). However, I can't figure out where to put such a
routine so that it is accessible. I tried putting a Public Sub into a module
file, then calling it like:

wrk.MySub "param"

However, when I did this I got a run-time error 438: "Object doesn't support
this property or method".

Is it possible to do something like this? If so, where do I need to put the
sub to make it accessible? If not, is there some other way to do what I
want?

Thanks for any information,

Carl Rapson
 
I don't use Access, but this worked from MSWord (with appropriate references
set):

Option Explicit
Sub testme()

Dim xl As Excel.Application
Dim wrk As Excel.Workbook
Set xl = New Excel.Application
Set wrk = xl.Workbooks.Open(FileName:="file.xls")

xl.Visible = True 'for testing

xl.Run "'" & wrk.Name & "'!hi", "there"

End Sub

I used a macro called "hi" in file.xls:

Option Explicit
Sub hi(parm As String)
MsgBox "parm: " & parm
End Sub
 
Perfect, thanks. Just after I posted the original message, I realized that
the problem had to do with automation -- of course the object won't know
about any methods that aren't part of the interface. I don't know why I
didn't see it at the time.

Anyway, thanks again. That will do the trick.

Carl
 
Back
Top