Translate powershell code to VBA or run powershell script from VBA

Question

This is my first question at Stack Overflow, and I really hope I am doing this right. If not, please let me know and I will correct.

I run Office365 Build 16.0.12527.20612 32 bit on Windows 10 64 bit.

I have searched for 2 days and tried different approaches, but not succeeded. I am familiar with VBA and have coded some macros. I have a list of computer names and serial numbers in Excel. I have a powershell script that can get computer information from Dell. It is the warranty information that is of interest. Preferably I would like to write a macro that does the same thing as the script. Here is the powershell script:

    $ServiceTag = "A1B2C3D4"
    Try {
        # Get Access Token - expires in 3600 seconds
        $accessTokenEndpointUrl = "https://apigtwb2c.us.dell.com/auth/oauth/v2/token"
        $requestBody = @{
            grant_type    = "client_credentials"
            client_id     = "123456789012345" # api key
            client_secret = "098765432109876" # api secret
            
        }
        write-output "requestbode: $requestBody"
        $token = (Invoke-RestMethod -uri $accessTokenEndpointUrl -Headers @{ } -Method Post -Body $requestBody).Access_token
        write-output "Token: $token"
        # url for api warranty query - tag is comma seperated list of service tags
        $url = "https://apigtwb2c.us.dell.com/PROD/sbil/eapi/v5/asset-entitlements"
        $headers = @{"Authorization" = "Bearer $token" }
        $body = @{
            servicetags = $ServiceTag
        }
        
        $resp = Invoke-RestMethod -Uri $url -Headers $headers -Method Get  -Body $body
        write-output $resp
        
    }
    Catch {
        Write-Host $($_.Exception.Message)
    }

I have not been able to make call to the webservice correctly. I didn’t save any of the macros as they didn't work. I was able to get as far as to call the service but got response that "data missing or duplicate". It is the requestBody I think I am not able to create correctly. Then I thought that I could try to run the script from VBA and output to a file, then read the file in VBA. I tried different ways I found on stack overflow, but I either get a sidebyside error (eventlog referring to powershell.exe) or permission denied depending on which sample I try. I could from same macro start other programs, like notepad.

I would appreciate if anybody here could help.

I tried this:

    Sub RunPS()
    strCommand = "Powershell.exe - ExecutionPolicy ByPass -File ""D:\temp\DellCheck.ps1"""
    Set WshShell = CreateObject("WScript.Shell")
    WshShell.Run (strCommand)
End Sub

I get this at WshShell.Run(strCommand): error

Does this answer your question? Call Powershell Script from VBA(with Parameter) — Ocaso Protal
If the duplicate is no solution for you please edit your question and some detail: How did you call the script, which error do you get and so on. — Ocaso Protal
Thank you Ocaso. The link didnt help. I have edited my question. — ITD
If anyone know how to do same thing in VBA, it would be the best solution for me. Then I will have the string-data without need for external script and reading file. — ITD
Looks like this wont be fixed any time soon. I tried on another computer and with different executionpolicy settings, but same error. — ITD

ITD ITD · Accepted Answer · 2020-09-10T19:17:06

I found what was wrong in my case. I am writing this so it may help others in same situation.

Thing to note is that i run 32 bit Office on 64 bit Windows. I suspected that this may be part of the problem so I mentioned it in my question. When calling something from 32 bit app in 64 bit windows, it is run from the c:\windows\syswow64. I had set my execution policy and checked several times, but when you start powershell from start menu, it starts the 64 bit. Go to C:\Windows\SysWOW64\WindowsPowerShell\v1.0 and run powershell.exe as administrator. Set the execution policy. I did: "set-executionpolicy unrestricted".
Then i noticed a warning from windows antimalware/bitdefender. If a program calls powershell, it seems bitdefender sees that as malicous. Go in to settings, virus and threat protection and exclude the syswow64\windowspowersehll\v1.0 folder. Keep in mind that this may be a security risk as other programs can now run powershell commands.

Then it worked as intended. I used a sample from stack overflow to call the script and get the results. I used this in VBA (Excel):

pscommand = ".\myscript2.ps1"
cmd = "powershell.exe -noprofile -command " & pscommand
Set oshell = CreateObject("WScript.Shell")
Set executor = oshell.Exec(cmd)
executor.StdIn.Close
strS =  executor.StdOut.ReadAll

OBS! In the sample I found it is

Set shell = CreateObject("WScript.Shell")

That fails with error "Argument not optional". I guess Shell is a reserved word. I used oShell.

Translate powershell code to VBA or run powershell script from VBA

1 Answers