Run Excel VBA code inside a PowerShell script?

[mkrautler]

Is it possible to embed VBA Code inside of a Powershell script so when the PS Script is run it automatically runs the VBA code?

I have the below Powershell code to open the most recently created file and then I want to apply VBA code to the file that was open automatically without any user input, is that possible?

Thanks in advance!!

# start Excel
$excel = New-Object -comobject Excel.Application 

# Enable the 'visible' property so the document will open in excel
$excel.Visible = $true

$FilePath = Get-ChildItem '\\SHARED\software\Papyru\ECMS\ActiveTasks\' -recurse -File |
    Sort-Object -Property CreationTime -Descending | Select-Object -First 1 | Invoke-Item

 

[CephasOz]

Hi mkrautler. This is not exactly what you were after, more of an alternative, but perhaps useful anyway.

This method uses marker files in the Temp folder that are checked when a workbook is opened. The code in the workbook's Workbook_Open event checks for the existence of particular marker files and takes actions based on which ones exist. Like so:

Private Sub Workbook_Open()
    Dim strPath As String
    Dim bolCloseAfter As Boolean
    bolCloseAfter = True
    strPath = Environ("Temp")
    If (Right(strPath, Len(Application.PathSeparator)) <> Application.PathSeparator) Then
        strPath = strPath & Application.PathSeparator
    End If
    Select Case True
        Case (Dir(strPath & "ChangeColour.txt") <> vbNullString)
            MsgBox "Will now proceed to change colour"
            ' ... colour changing code
            Kill strPath & "ChangeColour.txt"
        Case (Dir(strPath & "RotateHorizontal.txt") <> vbNullString)
            MsgBox "Will now proceed to rotate horizontally"
            ' ... rotation code
            Kill strPath & "RotateHorizontal.txt"
        Case (Dir(strPath & "DoNotClose.txt") <> vbNullString)
            ' Allows you to edit the code
            bolCloseAfter = False
        Case Else
            MsgBox "Didn't have to do a thing"
    End Select
    If bolCloseAfter Then
        ThisWorkbook.Close SaveChanges:=False
    End If
End Sub

The disadvantage with this method is the dialog box which comes up to ask if macros should be enabled. So, it's not the VBA in PowerShell or even the no user input scenario you were after, but still a way to have PowerShell run a set of VBA code.

 

[mkrautler]

Thank you that is definitely interesting but I don't think it will accomplish what I need as I am trying to automate our reporting process with no user interaction. But it's always good to have alternatives

 

[mkrautler]

Does anyone else know if it is possible to embed VBA code inside Powershell?

 

[zweifuss]

By default VBA macros should be disabled. If you know what you are doing, you can play with everythig:

$XL = New-Object -ComObject Excel.Application
$XL.Visible = $true                                # Show application window
$xlBook = $XL.Workbooks.Add()                      # New workbook

# Needs option "Trust Access to the VBA Project object model" (registry value AccessVBOM) set and macros enabled!
$xlModule = $xlBook.VBProject.VBComponents.Add(1)  # New 1=Module, 2=Class, 3=MSForm
$xlModule.CodeModule.AddFromString(@"
Private Sub MySub()
    MsgBox "Hi!", vbInformation
End Sub
"@
)
$XL.Run($xlModule.Name + '.MySub')                 # Call MySub

$XL.DisplayAlerts = $false                         # No confirmations
$xlBook.Close()
$XL.Quit()

 

[eirikdaude]

To add to zweifuss' answer, if you want to run your macro with arguments, the syntax is

$XL.Run($xlModule.Name + '.MySub', 'arg1', 'arg2', ...)