Running Powershell Script in Excel VBA

Question

So I'm trying to create a workbook that will pull down all info from AzureAD using powershell.(C:\Script\VBA Testing\ExportOffice365UsersAndGroups.ps1)

and then send commands to powershell and run them.

I've thought of different ways such as creating a ps1 file:

strpath = "C:\Script\VBA Testing\"
Set oFile = fso.CreateTextFile(strpath & "Testing.ps1")
oFile.WRiteline "Connect-AzureAD"
oFile.Close

then just running it to creating a batch file that will run the script after creating it as running powershell scripts seem to be hard.

I am able to start powershell and run the command but the issue is that for some reason its not noticing the modules i have run.

"Powershell -noexit -File ""C:\Script\VBA Testing\Testing.ps1"""

just returns that the module AzureAD does not exist but if i just start powershell and try it, it works fine.

Same if i set it to run from C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

Anyone have any bright ideas that might help me?

Pretty much I just need it to do Connect-AzureAD then run a few lines.. What im thinking is that every change i do will update the ps1 file and in the end i press a button and that will run the entire script.

A lot of users are running 32bit excel so need something that will check if its running 32bit and if so, run powershell 64bit instead of 32bit.

Are you just trying to load data from Azure AD into Excel? What else are you trying to do? What you propose (writing to a batch file then running it) is quite convoluted and unreliable. Just solve the immediate errors, i.e. the " module AzureAD does not exist" — Nick.McDermaid
I want to first extract all the data then make changes to them and have those changes sent back into Azure AD.. My goal is to just run the powershell script i have to get all the user info.. then for every change i make a new line will be created in my script(this i can do) then when pressing a submitting this script will be run... so one script needs to be ran in the beginning then one in the end. — Christian Pedersen

Tom Sun - MSFT Tom Sun - MSFT · Accepted Answer · 2018-10-29T08:23:29

Based on my test it works in the 64 bit Excel VBA project.

So please have a try to use the 64 bit Excel to test.

If I run the command in the 32 bit excel VBA I also can reproduce the issue that you mentioned.

We could know that Connect-AzureAD in the powershell AzureAD or AzureAD preview module. I also found that I can't Import-Module AzureAD in the 32 bit VBA project.

In the AzureADPreview/AzureAD.psd1 file, I found that

ProcessorArchitecture = 'Amd64'

And I also try to update the ProcessorArchitecture = "None" or ProcessorArchitecture = "X86", but both of don't work.

powershell script:

Import-Module AzureAD -Force  //force to load the AzureAD module 
Connect-AzureAD

Running Powershell Script in Excel VBA

1 Answers