VBA Call Function in Access

Question

Is it possible to use VBA in access to perform macros in excel, using a "call function"? I have am trying to format data, by using the VBA functions in Access.

ZygD · Accepted Answer · 2015-06-28 13:52:02Z

1

Yes, it is possible.

Here is an example:

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 quit the Excel application
    xl.ActiveWorkbook.Close (True)
    xl.Quit

'Step 5:  Memory Clean up.
    Set xl = Nothing

End Sub

edited Jun 28, 2015 at 13:52

ZygD

24.8k41 gold badges106 silver badges144 bronze badges

answered Oct 17, 2013 at 18:15

Jair Batista

884 bronze badges

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

1 Comment

autumntiger Over a year ago

Thank you! That works great! The only issue now, is that, I start with it closed because my boss wants as much automation as possible. When I run the code you gave me, it closed the wb immediately following the execution of the macro. Please advise.

Collectives™ on Stack Overflow

VBA Call Function in Access

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related