Open Excel file with parameter using PowerShell

Question

I've got a requirement to fetch a last created .xlsm file from a particular folder. I managed to achieve it using the below powershell code:

$dir = "\\test\folder1\"
$latest = Get-ChildItem -Path $dir *.xlsm | Sort-Object CreationTime -Descending | Select-Object -First 1
$filePath = $dir + $latest

Once I've got the file I need to launch the excel file and pass a parameter to it. In the past, the last created file wasn't required (it was using one master file) so I used to run a batch file that looked like this:

start excel "\\test\folder\file.xlsm" /e/TestEnv

Then using below code in VBA I was able to pick up the parameter when Workbook_Open() gets executed:

Function CmdToSTr(Cmd As Long) As String
    Dim Buffer() As Byte
    Dim StrLen As Long

    If Cmd Then
        StrLen = lstrlenW(Cmd) * 2

        If StrLen Then
            ReDim Buffer(0 To (StrLen - 1)) As Byte
            CopyMemory Buffer(0), ByVal Cmd, StrLen
            CmdToSTr = Buffer
        End If
    End If
End Function

I've tried calling many different powershell launch methods and none of them seem to include the parameter that's passed at the end of it including

Start-Process -FilePath $filePath -ArgumentList "/TestEnv"


$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open($filePath)[TestEnv]

Any suggestions highly appreciated

Answer 1

$dir = "\\path"
$latest = Get-ChildItem -Path $dir *.xlsm | Sort-Object CreationTime -Descending | Select-Object -First 1
$filePath = $dir + $latest
start excel "`"$filepath`"/TestEnv"

This resolves my issue