0

I need to run a powershell script with a parameter from a word macros. For now even the attempts to simply run a script from VBA are not sucessful. I do the following:

Sub Powershell_Run()

sCmd = "powershell -file ""C:\Users\i351063\Desktop\Scripts\NewAttempt.ps1"""
Set xShell = CreateObject("Wscript.Shell")
Set xShellExec = xShell.Run(sCmd)
rReturn = xShellExec.Run("C:\Users\i351063\Desktop\Scripts\NewAttempt.ps1")

End Sub

The execution of this code returns an error: "Run-time error '70': Permission denied" on the line Set xShellExec = xShell.Run(sCmd). What do I do wrong? How to fix the error? Thanks a lot in advance!

UPD: Powershell code (I want to pass the filename as a parameter from VBA to PS, for now it's initialized right in the code)

Param([string]$filename)
$filename = "HT.docm"

Add-Type -AssemblyName "Microsoft.Office.Interop.Word" 
$word = [Runtime.Interopservices.Marshal]::GetActiveObject('Word.Application')
$wshell = New-Object -ComObject Wscript.Shell

$doc = $word.Documents | Where-Object {$_.Name -eq "$filename"}
    If (!$doc) 
         {
            $form.Close()
            [void] $wshell.Popup("Failed to find an opened report",0,"Report not found",0x1)
            $word.ScreenUpdating = $true
            Break
        }
    Else {...}
anthony sottile
  • 61,815
  • 15
  • 148
  • 207
TRP
  • 33
  • 6
  • 2
    You may want to start up a PowerShell console session, and see what `Get-ExecutionPolicy` returns. – Jeff Zeitlin Sep 03 '18 at 15:44
  • It is in unrestricted mode. Actually I tried to add -executionPolicy parameter in the code above but the outcome was the same. – TRP Sep 04 '18 at 06:35
  • Are you running as the user i351063, or running elevated, or as a different user entirely? What is NewAttempt.ps1 doing (you should probably post the text of that script)? – Jeff Zeitlin Sep 04 '18 at 11:47
  • I run the code as this user, yes. The Powershell script performs some operations over a word document, it finds the needed opened document by the name and starts to work with it. As this functionality (that is performed with Powershell) is additional to the main logic realized with VBA means, I want just to insert into VBA code a function that allows to execute PS script when it's needed. – TRP Sep 04 '18 at 12:17
  • Have you tried running the PowerShell script outside the VBA? Is the PowerShell script trying to access the Word document that contains the executing copy of the VBA macro? – Jeff Zeitlin Sep 04 '18 at 12:21
  • I've just added a code snippet above. Well, yes, at least PowerShell allows to "fetch" an opened .docm file. If it works simultaneously, I mean running VBA macros and PS script, I can't assert because I can't launch PS from VBA - that's why here I am, asking for help :) – TRP Sep 04 '18 at 12:34
  • Are you ***absolutely certain*** that you're not getting the 'permission denied' error because HT.docm is open in Word when you try to run the macro? In other words, is `WScript.shell.run()` returning its error because _it_ is failing, or is it because it's running the script, but the _script_ is encountering (and reporting) the error? – Jeff Zeitlin Sep 04 '18 at 12:43
  • Also, you may want to look at https://stackoverflow.com/questions/26762566/vba-wscript-shell-run-exe-file-with-parameter – Jeff Zeitlin Sep 04 '18 at 12:47
  • I'm pretty sure that the macro doesn't even try to run my script. Smth is wrong with xShellExec = xShell.Run(sCmd). I just couldn't find any other examples of how to code the PS call in macros. – TRP Sep 04 '18 at 12:56
  • I've just tried to change the line Set xShellExec = xShell.Run(sCmd) on Set xShellExec = xShell.Exec(sCmd) (as in the thread you gave a link to), and received an error: "run-time error -2147024891 (80070005) Access is denied" – TRP Sep 04 '18 at 13:02
  • The 80070005 is a Net Framework error, which may well mean that the PowerShell script is being loaded, but doing something that it's not allowed to do. What happens if the PowerShell script is a "do nothing" script, or a script that creates an unrelated text file with arbitrary text in it - in other words, not trying to interact with Word or the open Word document? – Jeff Zeitlin Sep 04 '18 at 13:05
  • I tried - the same outcome, the same line is highlighted. – TRP Sep 04 '18 at 13:30

0 Answers0