How to store and run Powershell code in Excel book-CodePudding

I have a simple PS script. I need to store it somehow in the Excel workbook as it is a shared document. My final solution is to store the code in the cell value and then run it. The problem is that all quotes are removed in the running script. But watch shows them just fine. Powershell error Watch variable

I'm running the script in the following way:

strCommand = "PowerShell.exe -noexit "" " & getScript() & " "" " & host

CodePudding user response：

You can convert your script to base64 and pass it to powershell.exe's -EncodedCommand switch, this way you don't need to worry about escaping quotation marks:

# Read script into a string
$Script = Get-Content .\path\to\script.ps1 -Raw

# Get UTF16-encoded byte string
$ScriptUTF16 = [System.Text.Encoding]::Unicode.GetBytes($Script)

# Convert to base64
$EncodedCommand = [Convert]::ToBase64String($ScriptUTF16)

$EncodedCommand now holds a long base64 string that you can copy into a cell (or a macro definition) - you can copy it to your clipboard with Set-Clipboard or write it to a file if desired. The syntax for invoking the resulting script will then be:

strCommand = "PowerShell.exe -EncodedCommand " & getBase64Script()

CodePudding user response：

@Tim Williams proposed the following: Write the script to a temporary file and call that file with Powershell. Then delete the temp file. –

My solution:

...
path = createTempFile(scr)
...
strCommand = "PowerShell.exe -noexit -File " & path & " " & param
    retval = Shell(strCommand, vbNormalFocus)
...


...
Private Function createTempFile(str) As String
    Dim tempPath As String
    tempPath = "C:\blablabla\" & name & ".ps1"
    Dim fs As Object
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.CreateTextFile(tempPath, True)
    f.Write str
    f.Close
    
    createTempFile = tempPath

End Function