Call excel function in access vba

Question

I've a sub in excel that needs to be called from access. Excel vba

 Public Function testme(value As String) As String

 Dim xlpath As String
 Dim concate As String

 xlpath=ActiveWorkbook.Path
 value = ActiveWorkbook.Name
 concate = xlpath & "\" & value     
 Let testme = concate

 End Function

i need to call above method in one of the access method.How do i call it.

 Sub Connect1()
 Dim xlApp As Variant
'Set xlApp = CreateObject("Excel.Application")
'this will launch a blank copy of excel; you'll have to load workbooks
'xlApp.Visible = True

Set xlApp = GetObject(, "Excel.Application")
    Let ans = xlApp.Application.Run("MyXLVBAProject.MyXLVBAModule.testme", 400)
'here ans has the string "500"
End Sub

You'll probably want to use Application.Run from Excel's object model. You pass it a string such as "QuickRDA.JavaCallBacks.GetQuickTab" for the macro name, where QuickRDA is the name of the Excel VBA project, JavaCallBacks is the name of the VBA module in that VBA project, and GetQuickTab is the name of the function in that VBA module. — Erik Eidt
– Erik Eidt, Commented Mar 21, 2014 at 1:40
In the code sample you're showing above, you pass in a flname parameter as a string, but then assign another value to it, wiping it out. (Also, it doesn't get used either.) Further you omit assigning a return value to function passfilename. Perhaps you wanted passfilename = ActiveWorkbook.Path to return the path? — Erik Eidt
– Erik Eidt, Commented Mar 24, 2014 at 16:53
Ok, I see your update. It looks to me like you don't need to pass a parameter to testme from access. Therefore, I suggest you change value from a formal parameter to a local variable. You can omit the actual parameter in the call as well. I will update my answer to show you exactly what I mean. — Erik Eidt
– Erik Eidt, Commented Mar 25, 2014 at 15:52

Erik Eidt · Accepted Answer · 2014-03-25 15:58:11Z

2

You'll probably want to use Application.Run from Excel's object model. You pass it a string such as "QuickRDA.JavaCallBacks.GetQuickTab" for the macro name, where QuickRDA is the name of the Excel VBA project, JavaCallBacks is the name of the VBA module in that VBA project, and GetQuickTab is the name of the function in that VBA module.

In Access

Sub Connect()    
    Dim xlApp As Variant
    Set xlApp = GetObject(, "Excel.Application")
    'this will connect to an already open copy of excel, a bit easier for quick & dirty testing
    Let ans = xlApp.Application.Run("MyXLVBAProject.MyXLVBAModule.testme")
End Sub

In Excel

Public Function testme() As String
    Dim xlpath As String
    Dim concate As String
    Dim value as String
    xlpath = ActiveWorkbook.Path
    value = ActiveWorkbook.Name
    concate = xlpath & "\" & value     
    Let testme = concate
End Function

-or simply-

Public Function testme() As String
    Let testme = ActiveWorkbook.FullName
End Function

Remember that in Excel the function testme should be put in a module whose name is MyXLVBAModule, and that the project containing the module should be called MyXLVBAProject.

edited Mar 25, 2014 at 15:58

answered Mar 22, 2014 at 0:00

Erik Eidt

27.5k2 gold badges34 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

vuyy1182 Over a year ago

Thanks a lot. I'll try it and let you know.

vuyy1182 Over a year ago

but i wanted to pass file name in the place of 400 above code.**ans** is not returning filename

Erik Eidt Over a year ago

Ok, you can pass your string instead of 400, of course, and, don't forget to change value as Long to value as String in testme (as well as returning the string of interest, of course). Also, please have a look at my 2nd comment to your question above.

vuyy1182 Over a year ago

I've changed my code based on your code.please have look my question. but instead of 400 what should i pass? please help me.I need to pass concate to access.

ASH · Accepted Answer · 2018-02-02 17:41:14Z

So, you want to trigger an Excel function from Access, or run an Excel subroutine from Access?

To run a function, you can do something like this.

  Public Function FV(dblRate As Double, intNper As Integer, _ 
                  dblPmt As Double, dblPv As Double, _ 
                  intType As Integer) As Double
     Dim xl As Object
     Set xl = CreateObject("Excel.Application")
     FV = xl.WorksheetFunction.FV(dblRate, intNper, dblPmt, dblPv, intType)
     Set xl = Nothing
 End Function

To run an Excel subroutine from Access, you can do the following.

Sub RunExcelMacro()
Dim xl As Object
'Step 1:  Start Excel, then open the target workbook.
   Set xl = CreateObject("Excel.Application")
    xl.Workbooks.Open ("C:\Book1.xlsm")
'Step 2:  Make Excel visible
   xl.Visible = True
'Step 3:  Run the target macro
   xl.Run "MyMacro"
'Step 4:  Close and save the workbook, then close Excel
   xl.ActiveWorkbook.Close (True)
    xl.Quit
'Step 5:  Memory Clean up.
   Set xl = Nothing 
End Sub

Collectives™ on Stack Overflow

Call excel function in access vba

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related