Exporting from Powershell to an Active.workbook

PowerShell Help
1

I am essentially wanting Excel to return a variable from PowerShell and input it into an activeworkbook on a Sharepoint server. Is this possible? I am wanting to excecute everything from Excel, so when data is imputed, it will initiate the PowerShell query, and when it obtains the information, I want it to dump the results into the Active Workbook that I am in. I have attempted to search via the internet, but really could only find information on creating new workbooks. Would love some insight, or best resource on how to accomplish my goal.

Thank you,

2

So… if you want all the code to run in Excel, you’ll be running Visual Basic for Applications (VBA), which is all Excel supports in terms of programming languages. VBA could probably launch a PowerShell instance to do something for you, and probably capture its output, but what you do with that will still be a VBA question since you’re executing in Excel.

VBA’s pretty old, and I’m not sure where to point you in terms of resources for connecting to SharePoint, or if it’s even feasible.

3

Doug Finke has some code for use in Powershell Specifically for working with Excel.

You can find the code here:

For Working with SharePoint online this module has been very useful for me:

I wrote up a short article on how to use PnP PowerShell:

Hopefully that helps a little, not sure if you are on or off premises.

4

Thank you guys for your wisdom. Ideally I would like to remove Excel from the equation in it’s entirety, but it is the easiest way to grant access for the users that would be updating data for the query.