Open Excel file with parameter using PowerShell
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 = "\testfolder1"
$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 "\testfolderfile.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
excel vba excel-vba powershell xlsm
asked Dec 7 '18 at 16:18
user1800674user1800674
1061315
add a comment |
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 = "\testfolder1"
$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 "\testfolderfile.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
excel vba excel-vba powershell xlsm
asked Dec 7 '18 at 16:18
user1800674user1800674
1061315
The ExcelApplication.Runmethod supports calling methods in a workbook and passing arguments - you could use that in place of passing command line arguments. rondebruin.nl/win/s9/win001.htm
– Tim Williams
Dec 7 '18 at 18:18
@TimWilliams I need to call the spreadsheet from a powershell and pass a parameter. Application.Run vba code isn't applicable in this case.
– user1800674
Dec 10 '18 at 8:58
add a comment |
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 = "\testfolder1"
$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 "\testfolderfile.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
excel vba excel-vba powershell xlsm
asked Dec 7 '18 at 16:18
user1800674user1800674
1061315
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 = "\testfolder1"
$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 "\testfolderfile.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
excel vba excel-vba powershell xlsm
excel vba excel-vba powershell xlsm
asked Dec 7 '18 at 16:18
user1800674user1800674
1061315
asked Dec 7 '18 at 16:18
user1800674user1800674
1061315
asked Dec 7 '18 at 16:18
user1800674user1800674
1061315
asked Dec 7 '18 at 16:18
user1800674user1800674
1061315
asked Dec 7 '18 at 16:18
user1800674user1800674
1061315
1061315
The ExcelApplication.Runmethod supports calling methods in a workbook and passing arguments - you could use that in place of passing command line arguments. rondebruin.nl/win/s9/win001.htm
– Tim Williams
Dec 7 '18 at 18:18
@TimWilliams I need to call the spreadsheet from a powershell and pass a parameter. Application.Run vba code isn't applicable in this case.
– user1800674
Dec 10 '18 at 8:58
add a comment |
The ExcelApplication.Runmethod supports calling methods in a workbook and passing arguments - you could use that in place of passing command line arguments. rondebruin.nl/win/s9/win001.htm
– Tim Williams
Dec 7 '18 at 18:18
@TimWilliams I need to call the spreadsheet from a powershell and pass a parameter. Application.Run vba code isn't applicable in this case.
– user1800674
Dec 10 '18 at 8:58
The Excel
Application.Run method supports calling methods in a workbook and passing arguments - you could use that in place of passing command line arguments. rondebruin.nl/win/s9/win001.htm– Tim Williams
Dec 7 '18 at 18:18
The Excel
Application.Run method supports calling methods in a workbook and passing arguments - you could use that in place of passing command line arguments. rondebruin.nl/win/s9/win001.htm– Tim Williams
Dec 7 '18 at 18:18
@TimWilliams I need to call the spreadsheet from a powershell and pass a parameter. Application.Run vba code isn't applicable in this case.
– user1800674
Dec 10 '18 at 8:58
@TimWilliams I need to call the spreadsheet from a powershell and pass a parameter. Application.Run vba code isn't applicable in this case.
– user1800674
Dec 10 '18 at 8:58
add a comment |
1 Answer
1
active
oldest
votes
$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
answered Jan 3 at 15:35
user1800674user1800674
1061315
add a comment |
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53673269%2fopen-excel-file-with-parameter-using-powershell%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
$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
answered Jan 3 at 15:35
user1800674user1800674
1061315
add a comment |
$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
answered Jan 3 at 15:35
user1800674user1800674
1061315
add a comment |
$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
answered Jan 3 at 15:35
user1800674user1800674
1061315
$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
answered Jan 3 at 15:35
user1800674user1800674
1061315
answered Jan 3 at 15:35
user1800674user1800674
1061315
answered Jan 3 at 15:35
user1800674user1800674
1061315
answered Jan 3 at 15:35
user1800674user1800674
1061315
1061315
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53673269%2fopen-excel-file-with-parameter-using-powershell%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
The Excel
Application.Runmethod supports calling methods in a workbook and passing arguments - you could use that in place of passing command line arguments. rondebruin.nl/win/s9/win001.htm– Tim Williams
Dec 7 '18 at 18:18
@TimWilliams I need to call the spreadsheet from a powershell and pass a parameter. Application.Run vba code isn't applicable in this case.
– user1800674
Dec 10 '18 at 8:58