VBA, Running Excel function from Access function/macro

Running Excel function from Access function/macro

Author	Message
Carlo #1 / 7	Running Excel function from Access function/macro I've created a function in Excel. I'd like to call that function from another function or macro in Access. For example the Excel function is xlfunction(myarg). While Excel & Access are both open, I'd like to put in a call to that function like xlfunction("abcd") so that xlfunction will run with abcd as the value of myarg. Is this possible and if so how? Thank you muchly!
Wed, 07 Dec 2005 01:50:31 GMT

Rob #2 / 7	Running Excel function from Access function/macro Carlos, You may want to look at this article on MS's site. http://support.microsoft.com/default.aspx?scid=kb;en-us;198571 Hope it helps. -Rob Quote: > I've created a function in Excel. I'd like to call that > function from another function or macro in Access. For > example the Excel function is xlfunction(myarg). While > Excel & Access are both open, I'd like to put in a call to > that function like xlfunction("abcd") so that xlfunction > will run with abcd as the value of myarg. > Is this possible and if so how? > Thank you muchly!
Wed, 07 Dec 2005 02:34:51 GMT

jaf #3 / 7	Running Excel function from Access function/macro Run Method Example This example shows how to call the function macro My_Func_Sum, which is defined on the macro sheet Mycustom.xlm (the macro sheet must be open). The function takes two numeric arguments (1 and 5, in this example). mySum = Application.Run("MYCUSTOM.XLM!My_Func_Sum", 1, 5) MsgBox "Macro result: " & mySum So xlApp.Application.Run("YOURWORKBOOKNAME.XLS!xlfunction",myarg) will work from an Access module. (as long as the function is public). -- John johnf202 at hotmail dot com Quote: > I've created a function in Excel. I'd like to call that > function from another function or macro in Access. For > example the Excel function is xlfunction(myarg). While > Excel & Access are both open, I'd like to put in a call to > that function like xlfunction("abcd") so that xlfunction > will run with abcd as the value of myarg. > Is this possible and if so how? > Thank you muchly!
Wed, 07 Dec 2005 03:33:56 GMT

Ronald Dodg #4 / 7	Running Excel function from Access function/macro jaf, your code is a bit on the long side as you don't have to call the application object 2 different times. In your examples if in the Excel VBA and using the code: Application.Run It's the same as just calling on the Run method cause the application object is the application itself by default just as the Application variable is the application that the code is in by default. however, since the xlApp is already the application object (provided that it was set to Excel.Application in either the GetObject for the existing open Excel instance or using the Set statement with the New keyword included for a new instance of Excel.Application), one could just call on the Run method. xlApp.Run("MyWorkbook.xls!Module1.MyFunction") OR xlApp.Run("MyWorkbook.xls!Module1.MyFunction", 1, 5) will do the job just fine. In my test, I have tried the following code and it works for me. Sub AppTest() Dim ReportYear As Long Set XLApp = GetObject(, "Excel.Application") ReprtYear = XLApp.Run("ProdReportExec.xls!ProdReporting.ReportYear") End Sub The only catch to this that I have found, it must be in a Standard Module, not a Class Module, and the function that this code is calling on must be Public. -- Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 Quote: > Run Method Example > This example shows how to call the function macro My_Func_Sum, which is > defined on the macro sheet Mycustom.xlm (the macro sheet must be open). The > function takes two numeric arguments (1 and 5, in this example). > mySum = Application.Run("MYCUSTOM.XLM!My_Func_Sum", 1, 5) > MsgBox "Macro result: " & mySum > So xlApp.Application.Run("YOURWORKBOOKNAME.XLS!xlfunction",myarg) will work > from an Access module. > (as long as the function is public). > -- > John > johnf202 at hotmail dot com > > I've created a function in Excel. I'd like to call that > > function from another function or macro in Access. For > > example the Excel function is xlfunction(myarg). While > > Excel & Access are both open, I'd like to put in a call to > > that function like xlfunction("abcd") so that xlfunction > > will run with abcd as the value of myarg. > > Is this possible and if so how? > > Thank you muchly!
Wed, 07 Dec 2005 04:14:49 GMT

jaf #5 / 7	Running Excel function from Access function/macro 8<0 It never hurts to be explicit. -- John johnf202 at hotmail dot com Quote: > jaf, your code is a bit on the long side as you don't have to call the > application object 2 different times. In your examples > if in the Excel VBA and using the code: > Application.Run > It's the same as just calling on the > Run > method cause the application object is the application itself by default > just as the Application variable is the application that the code is in by > default. > however, since the xlApp is already the application object (provided that it > was set to Excel.Application in either the GetObject for the existing open > Excel instance or using the Set statement with the New keyword included for > a new instance of Excel.Application), one could just call on the Run method. > xlApp.Run("MyWorkbook.xls!Module1.MyFunction") > OR > xlApp.Run("MyWorkbook.xls!Module1.MyFunction", 1, 5) > will do the job just fine. > In my test, I have tried the following code and it works for me. > Sub AppTest() > Dim ReportYear As Long > Set XLApp = GetObject(, "Excel.Application") > ReprtYear = XLApp.Run("ProdReportExec.xls!ProdReporting.ReportYear") > End Sub > The only catch to this that I have found, it must be in a Standard Module, > not a Class Module, and the function that this code is calling on must be > Public. > -- > Ronald R. Dodge, Jr. > Production Statistician > Master MOUS 2000 > > Run Method Example > > This example shows how to call the function macro My_Func_Sum, which is > > defined on the macro sheet Mycustom.xlm (the macro sheet must be open). > The > > function takes two numeric arguments (1 and 5, in this example). > > mySum = Application.Run("MYCUSTOM.XLM!My_Func_Sum", 1, 5) > > MsgBox "Macro result: " & mySum > > So xlApp.Application.Run("YOURWORKBOOKNAME.XLS!xlfunction",myarg) will > work > > from an Access module. > > (as long as the function is public). > > -- > > John > > johnf202 at hotmail dot com > > > I've created a function in Excel. I'd like to call that > > > function from another function or macro in Access. For > > > example the Excel function is xlfunction(myarg). While > > > Excel & Access are both open, I'd like to put in a call to > > > that function like xlfunction("abcd") so that xlfunction > > > will run with abcd as the value of myarg. > > > Is this possible and if so how? > > > Thank you muchly!
Wed, 07 Dec 2005 21:46:16 GMT

Carlo #6 / 7	Running Excel function from Access function/macro Thats pretty near what I need. The problem is the article describes access built-in and add-in functions. I can't figure out how to apply that to an Excel module/function that I've built myself. Any ideas/suggestions? Thank you. Quote: >-----Original Message----- >Carlos, >You may want to look at this article on MS's site. >http://support.microsoft.com/default.aspx?scid=kb;en- us;198571 >Hope it helps. >-Rob >> I've created a function in Excel. I'd like to call that >> function from another function or macro in Access. For >> example the Excel function is xlfunction(myarg). While >> Excel & Access are both open, I'd like to put in a call to >> that function like xlfunction("abcd") so that xlfunction >> will run with abcd as the value of myarg. >> Is this possible and if so how? >> Thank you muchly! >.
Fri, 09 Dec 2005 23:00:33 GMT

Ronald Dodg #7 / 7	Running Excel function from Access function/macro After you have created your Excel Application variable, you can then use the Run function on that variable either as a function to have your method (Function only) to return a result, or as a sub procedure to execute your method (Function or Sub). See examples of both below: Used as a function example: MyVar = xlApp.Run("MyWorkbook.xls!Module1.MyFunction", 1, 5) Used as an execution example: xlApp.Run "MyWorkbook.xls!Module1.MyFunction", 1, 5 Note, when you call up your method, you must have it in the String format of the syntax for your macro name argument: <WorkbookFullName>!<ModuleName>.<MethodName> FullName is the name of the file including the file 3 letter extension, but excluding the path name to the file. This does assume that this workbook is already openned in Excel. Can not use Named arguement with the Run function (I.e. With the Open function of the Excel application, Filename := "MyWorkbook.xls"), but rather must put in the arguments in the same order and position as it is done in the method. -- Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 Quote: > Thats pretty near what I need. The problem is the article > describes access built-in and add-in functions. I can't > figure out how to apply that to an Excel module/function > that I've built myself. > Any ideas/suggestions? > Thank you. > >-----Original Message----- > >Carlos, > >You may want to look at this article on MS's site. > >http://support.microsoft.com/default.aspx?scid=kb;en- > us;198571 > >Hope it helps. > >-Rob > >> I've created a function in Excel. I'd like to call that > >> function from another function or macro in Access. For > >> example the Excel function is xlfunction(myarg). While > >> Excel & Access are both open, I'd like to put in a call > to > >> that function like xlfunction("abcd") so that xlfunction > >> will run with abcd as the value of myarg. > >> Is this possible and if so how? > >> Thank you muchly! > >.
Fri, 09 Dec 2005 23:29:55 GMT

Page 1 of 1

[ 7 post ]