Exporting from Powershell to an Active.workbook

This topic contains 3 replies, has 3 voices, and was last updated by Profile photo of Kevin Dunn Kevin Dunn 9 months, 2 weeks ago.

  • Author
    Posts
  • #35371
    Profile photo of Kevin Dunn
    Kevin Dunn
    Participant

    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,

  • #35440
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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.

  • #35488
    Profile photo of Thom Schumacher
    Thom Schumacher
    Participant

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

    You can find the code here:
    https://github.com/dfinke/ImportExcel

    For Working with SharePoint online this module has been very useful for me:
    https://github.com/OfficeDev/PnP-PowerShell

    I wrote up a short article on how to use PnP PowerShell:
    https://crshnbrn66.wordpress.com/2015/12/22/using-officedevpnp-powershell-module/

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

  • #35643
    Profile photo of Kevin Dunn
    Kevin Dunn
    Participant

    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.

You must be logged in to reply to this topic.