By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,497 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

run stored excel VBA procedure from ACCESS

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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 ***
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.