1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How do I use Powershell to run vba program on an opened excel workbook

Discussion in 'Programming/Internet' started by AdamKong, Sep 12, 2018.

  1. AdamKong

    AdamKong Guest

    I am building a powershell script to search for a specify opened excel workbook and run the vba on it.

    It works well when there is one excel application with multiple workbooks.

    However, it is not working when there are more than one excel application, since the code only search workbooks on one excel application. How do I search workbooks on all opened excel application?

    Here is the code I am using

    Try{
    # Check if any excel application is running
    $ExistingExcel = ((get-process excel | select MainWindowTitle, ID, StartTime |
    Sort StartTime)).mainwindowtitle

    # Get existing excel application
    $excel = [Runtime.Interopservices.Marshal]::GetActiveObject('Excel.Application')

    # Get a list of workbook name in the excel application
    $ExcelList = $excel.workbooks | Foreach-object {$_.name}

    # Check if the list of workbooks contain the target workbook
    $targetWb = if ($ExcelList -contains "MyTargetWorkbook.xlsm")
    {
    # Activate the target workbook and run the vba
    $excel.Workbooks.Item("MyTargetWorkbook.xlsm")
    $targetwb.Activate()
    $excel.run('MyVbaProgram')

    } else {
    # Do error handling
    }

    }Catch{
    # Do error handling
    }

    Login To add answer/comment
     

Share This Page