Running Excel VBA from MS Access VBA

Question

Spent some time researching and I cannot figure out whats wrong.

Security Settings in both files are correct. The sub being called is public & in a normal module.

The error states "Cannot run macro blah blah blah...."

Code:

Private Sub this()
    Dim xl As Object
    Set xl = CreateObject("Excel.Application")
    xl.Visible = True
    xl.Workbooks.Open "pathToFile" & "\" & "Dig IT.xlsm", True, False
    xl.Run "ThisWorkbook.Module3"
    Set xl = Nothing
End Sub

Ah god im dumb. Changed it to xl.Run "name" and it worked perfectly. Post as answer and receive feedback — Doug Coats
– Doug Coats, Commented Nov 18, 2016 at 19:43

David Zemens · Accepted Answer · 2016-11-18 19:42:38Z

1

Instead of "ThisWorkbook.Module3", you need to supply the workbook name, module name, and procedure name (assuming "Module3" is the name of the code module, not the sub/function that you're trying to run).

I believe that should be in the format like this (untested):

Private Sub this()
    Dim xl As Object, wb as Object
    Const MODULE_NAME As String = "Module3"  '### MODIFY AS NEEDED! 
    Const PROC_NAME As String = "macro_name" '### MODIFY AS NEEDED! 
    Dim xlMacro As String
    Set xl = CreateObject("Excel.Application")
    xl.Visible = True
    Set wb = xl.Workbooks.Open("pathToFile" & "\" & "Dig IT.xlsm", True, False)

    ' builds the string to send to Excel.Run method:
    xlMacro = wb.Name & "!" & MODULE_NAME & "." & PROC_NAME
    ' call the macro in Excel
    xl.Run xlMacro
    Set xl = Nothing
End Sub

answered Nov 18, 2016 at 19:42

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

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

Collectives™ on Stack Overflow

Running Excel VBA from MS Access VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related