VBA调用外部Excel文件宏时变量未解析问题求助

Hey there, let's get that macro execution issue sorted out! The problem you're hitting is straightforward: when you wrap Nomefile inside a static string like "Nomefile!listaIdprodotto", VBA treats it as literal text instead of evaluating the variable's value. That's why it can't find the macro—it's looking for a workbook literally named "Nomefile" instead of the actual filename stored in your variable.

The Fix: String Concatenation #

Instead of hardcoding the variable name into the string, use VBA's string concatenation operator (&) to combine the variable's value with the macro name part. Here's how to adjust that problematic line:

Application.Run Nomefile & "!listaIdprodotto"

This tells VBA to first grab the value stored in Nomefile, then append !listaIdprodotto to it, creating the correct workbook-macro reference.

Improved, More Robust Code #

While we're at it, let's make your code more reliable by avoiding ActiveWorkbook/Select (which can break if focus shifts) and adding error handling for edge cases like missing files:

Sub MacroRunner()
    Dim Nomefile As String, Nomefolder As String
    Dim targetWB As Workbook
    
    Nomefolder = ActiveWorkbook.Path
    Nomefile = Dir(Nomefolder & "\*.xlsb")
    
    ' Check if any .xlsb file was found
    If Nomefile = "" Then
        MsgBox "No .xlsb files found in the current workbook's folder!", vbExclamation
        Exit Sub
    End If
    
    ' Open the workbook and assign it to a variable (no need for Select!)
    Set targetWB = Workbooks.Open(Nomefolder & "\" & Nomefile)
    
    ' Run the macro using the workbook variable and concatenated string
    Application.Run targetWB.Name & "!listaIdprodotto"
    
    ' Clean up with safe, explicit references
    Application.DisplayAlerts = False
    targetWB.Close SaveChanges:=False ' Adjust SaveChanges as needed
    Application.DisplayAlerts = True ' Always reset this!
    Set targetWB = Nothing ' Release object reference
End Sub

Key Improvements: #

• No more Select/ActiveWorkbook: Using an object variable (targetWB) makes the code immune to accidental window switches.
• File existence check: Prevents errors if there are no .xlsb files in the folder.
• Restored DisplayAlerts: Ensures Excel's normal alerts work after your macro runs.
• Explicit Close with SaveChanges: Avoids ambiguous save prompts.

内容的提问来源于stack exchange，提问作者Mat257