Execute an existing macro in another workbook

Question

I want to execute an existing macro in a workbook named A on an another workbook B. I tried to write a vba code in B to call the macro but it keeps execute in A.

Here is the macro:

Sub LoadQueries()

' TestQueries Macro

    Sheets.Add After:=ActiveSheet
    Sheets("Feuil1").Select
    Sheets("Feuil1").Name = "questions"
    Sheets.Add After:=ActiveSheet
    Sheets("Feuil2").Select
    Sheets("Feuil2").Name = "clean"
    Sheets("questions").Select
    Application.CutCopyMode = False
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=questions;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [questions]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Tableau_questions"
        .Refresh BackgroundQuery:=False
    End With
    Sheets("clean").Select
    Application.CutCopyMode = False
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=clean;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [clean]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Tableau_clean"
        .Refresh BackgroundQuery:=False
    End With
End Sub

And how i tried to call it in B

Call LoadQueries

`Activesheet` will be in the `ActiveWorkbook` which looks like workbook B, any mention of `Sheets` or `Worksheets` without being fully qualified to a workbook will also be in the `ActiveWorkbook`. Try `ThisWorkbook.Sheets....` and specifying a worksheet - it won't be the `Activesheet` in a non-active workbook. Also have a read of [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) as selecting sheets in a non-active workbook is probably causing problems to. — Darren Bartrup-Cook, Jun 01 '23 at 09:48
Does this help: https://stackoverflow.com/questions/34211496/run-a-macro-from-another-workbook? — RichardCook, Jun 01 '23 at 12:37

score -1 · Answer 1 · answered Jun 02 '23 at 12:57

This workaround simply copy the remote macro in the active workbook, execute it, and after execution remove it from the project. You can execute with the same data structure as in the source where the macro was created and tested.

Copy it in the workbook where the new datas are in a new Standard Module.

Call it with the required parameters:

macro_copy(remotewb,remotemod,remotemacro,remotepath)

where:

remotewb - the name of the workbook

remotemod - the name of the standard module of the macro

remotemacro - the name of the macro

remotepath - the path of the workbook (optional, default= the application path)

Sub macro_copy(remotewb As String, remotemod As String, remotemacro as string, Optional remotepath As String = Application.Path)

Dim wb As Workbook, localwb As Workbook
Dim remotewbstate As Boolean
Dim macroloc As VBComponent, newmod As VBComponent
Dim codetext As String

On Error Resume Next
Set wb = Workbooks(remotewb)
On Error GoTo 0
Set localwb = ThisWorkbook
If Not (TypeName(wb) = "Nothing") Then
remotewbstate = True
Else
remotewbstate = False
On Error Resume Next
Set wb = Workbooks.Open(remotepath & remotewb)
If Err <> 0 Then MsgBox "File is not found in: " & remotepath: Exit Sub
On Error GoTo 0
End If
Set macroloc = wb.VBProject.VBComponents(remotemod)
codetext = macroloc.CodeModule.Lines(1, macroloc.CodeModule.CountOfLines)
Set newmod = localwb.VBProject.VBComponents.Add(vbext_ct_StdModule)
newmod.CodeModule.InsertLines 1, codetext
Run remotemacro
localwb.VBProject.VBComponents.Remove newmod
If Not remotewbstate Then wb.Close
End Sub

Execute an existing macro in another workbook

1 Answers1