如何利用ExecCMD执行纯VBA代码宏并等待其完成后再继续执行后续逻辑？

Since VBA executes macros synchronously by default, you don’t need to use ExecCMD to wait for BID_1_TextOut to finish. Just call it directly in your RUN-MACRO—the code will pause execution until the macro completes writing the text file and cleaning up resources.

Here’s how to update your main macro:

Sub RUN-MACRO()
    ' First stage: Run login macro (waits for PowerShell process to finish)
    RUN_LOGIN_SAVE_TO_FILE
    
    ' Second stage: Run text output macro (automatically waits until done)
    BID_1_TextOut
    
    ' Continue with your remaining stages here...
End Sub

This approach keeps everything within the same Excel instance, avoids unnecessary process overhead, and guarantees sequential execution.

If you specifically need to run BID_1_TextOut as an independent process (matching how you execute the PowerShell script), you can launch a new Excel instance and trigger the macro via the command line. This works with your existing ExecCMD function, which waits for external processes to finish.

Step 1: Update Your Main Macro #

Sub RUN-MACRO()
    ' First stage: Run login macro
    RUN_LOGIN_SAVE_TO_FILE
    
    ' Second stage: Run text output macro as a separate process
    Dim workbookPath As String
    ' Quote the path to handle spaces in folder/file names
    workbookPath = Chr(34) & Application.ActiveWorkbook.FullName & Chr(34)
    Dim cmd As String
    ' Command line to launch Excel, run the macro, then exit
    cmd = "excel.exe /e /x " & workbookPath & " /mBID_1_TextOut"
    
    ' Execute via ExecCMD (will wait for the process to finish)
    ExecCmd cmd
    
    ' Continue with subsequent stages once the separate process completes...
End Sub

Command Line Argument Breakdown: #

• /e: Starts Excel without opening a blank workbook (reduces overhead)
• /x: Exits Excel immediately after running the specified macro
• /mBID_1_TextOut: Triggers the BID_1_TextOut macro when Excel opens your workbook

Step 2: Improve Macro Reliability (Optional) #

To ensure the macro works correctly in a separate Excel instance, replace ActiveWorkbook with ThisWorkbook (it always refers to the workbook containing the macro):

Sub BID_1_TextOut()
    Dim fso As New FileSystemObject
    Dim Stream As TextStream
    Dim RowNum As Long
    Dim ws As Worksheet
    Dim outputPath As String
    
    ' Target the sheet in the workbook containing this macro
    Set ws = ThisWorkbook.Sheets("Merge1")
    outputPath = Split(ThisWorkbook.FullName, ".")(0) & "_Merge1.txt"
    Set Stream = fso.CreateTextFile(outputPath, True)
    
    RowNum = 1
    ' Write columns A-BR until column A is empty
    Do Until IsEmpty(ws.Range("A" & RowNum)) = True
        Stream.WriteLine ws.Range("A" & RowNum).Value & ws.Range("B" & RowNum).Value & _
        ws.Range("C" & RowNum).Value & ws.Range("D" & RowNum).Value & ws.Range("E" & RowNum).Value & _
        ' Keep the rest of your column concatenation here, prefixing each Range with ws.
        ws.Range("BR" & RowNum).Value
        RowNum = RowNum + 1
    Loop
    
    Stream.Close
End Sub

Key Notes for the Separate Process Approach: #

• A new Excel window will open briefly while the macro runs (add /hidden to the command line to run it silently, though some Excel versions may still show a window momentarily).
• Ensure your workbook is saved before running this, as the path is captured at runtime.
• Adjust macro security settings if needed to allow macros to run in the new Excel instance.

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