6

Quick question. I am trying to write the following PowerShell script, but I would like it to fit on a single line:

$app = New-Object -comobject Excel.Application
$wb1 = $app.Workbooks.Open("C:\xampp\upload_files\Launchpad.xlsm")
$app.Run("Refresh")
$wb1.Close($false)
$app.Quit()

The pseudo-code would look something like this:

$app = New-Object -comobject Excel.Application AND $wb1 = $app.Workbooks.Open AND "C:\xampp\upload_files\Launchpad.xlsm") AND $app.Run("Refresh") AND $wb1.Close($false) AND $app.Quit()

The reason I want to fit on a line is because I would like to insert the arguments directly in the 'arguments' box of Windows Task Scheduler. The reason for this is that for some reason scripts have been disabled (e.g. I cannot call a .ps1 file...)

I know this will still work, as I already have a "one liner" PS script running. What would the syntax look like??

Kind regards, G.

alroc
  • 27,574
  • 6
  • 51
  • 97
Noobster
  • 1,024
  • 1
  • 13
  • 28
  • 2
    "The reason for this is that for some reason scripts have been disabled" - Rather than try to work around this, find out if there is a valid reason for this decision (there likely isn't, or the reason isn't known, based on how you've worded this). If it wasn't a conscious decision made with valid reasons, get it reversed. – alroc Mar 07 '14 at 19:12
  • If by "scripts have been disabled" you mean the Powershell Execution policy is set to "Restricted", you can override that in the command line when you call Powershell.exe. – mjolinor Mar 07 '14 at 19:20

2 Answers2

9

Powershell statements can be separated with semicolons:

$app = New-Object -COM 'Excel.Application'; $wb1 = $app.Workbooks.Open("..."); ...

The PowerShell executable takes a -Command parameter that allows you to specify a command string for execution in PowerShell:

powershell.exe -Command "stmnt1; stmnt2; ..."

To run this via Task Scheduler you'd put powershell.exe into the "program" field and -Command "stmnt1; stmnt2; ..." into the "arguments" field of the task.

However, as @alroc said: you should verify why script execution has been restricted. If it's just the default setting you can simply change it by running Set-ExecutionPolicy RemoteSigned or override it by adding -ExecutionPolicy ByPass to a PowerShell command line. However, if the setting is enforced by policy changing/bypassing the setting will fail, and you could get into quite some trouble for violating company policies.

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
  • Thank you so much for your quick reply! Your syntax works when I input the script directly into powershell ... however it doesn't work when I put it in the 'arguments' box of the Windows Task Scheduler? – Noobster Mar 07 '14 at 19:15
  • Update: I have inserted the following code `-Command "$app = New-Object -comobject Excel.Application; $wb1 = $app.Workbooks.Open("C:\xampp\upload_files\Launchpad.xlsm"); $app.Run("Refresh"); $wb1.Close($false); $app.Quit()"` into the arguments section and nothing seems to work. The code however works perfectly when I input it direcly into powershell.. Is this a policy issue? Or is there still an error with the syntax? Thank you all again. – Noobster Mar 08 '14 at 19:39
  • 1
    @Noobster: Try running the command line `powershell.exe -Command "..."` from a regular command prompt (`CMD.exe`). – Ansgar Wiechers Mar 08 '14 at 20:48
  • Thanks man -doing this enabled me to see what was going wrong. Forgot to escape the " with a '. This is the annoying aspect of programming that sometimes makes me want to give up and set up a farm. – Noobster Mar 09 '14 at 22:23
0

Here is a solution that you might use if the script is not that easy to convert, but you are on Windows running at least PowerShell V5. It converts the code into Base64 and uses PowerShell.exe with the parameter -encodedCommand to pass the encodedCommand as string.

$command = Get-Content .\YourPowerShellFileContainingTheCode.ps1 -raw
# Get-Content may require "-encoding utf8" or other encodings depending on your file
$encodedCommand = [Convert]::ToBase64String([Text.Encoding]::Unicode.GetBytes($command))
Write-Output "Text for application:"
Write-Output "PowerShell.exe" ""
Write-Output "Text for argurments:"
Write-Output "-encodedCommand $encodedCommand"

It would look like this, but with a much larger command:

Text for application:
PowerShell.exe

Text for argurments:
-encodedCommand SABvACAASABvACAASABvACwAIABzAHQAYQBjAGsAbwB2AGUAcgBmAGwAbwB3AA==
An-dir
  • 465
  • 2
  • 13