Auto refresh data in excel file

This topic contains 6 replies, has 3 voices, and was last updated by Profile photo of Dorian Sierra Dorian Sierra 1 year, 7 months ago.

  • Author
    Posts
  • #28421
    Profile photo of Dorian Sierra
    Dorian Sierra
    Participant

    Hey Guys,

    Totally new to Powershell scripting. I have some .NET background but never used Powershell before. I would love to automate some Excel Reports that I normally have to manually open and refresh data and a couple of pivot tables. Can I automate this process with Powershell? And to take it a step further, can I automate the Task Scheduler to run a script to execute the above example on a weekly basis ? Any help would be appreciated, Thanks!

  • #28422
    Profile photo of Ondrej Zilinec
    Ondrej Zilinec
    Participant

    Answers for your questions: Yes and Yes 🙂
    Do you have any technical question?

  • #28423
    Profile photo of Dorian Sierra
    Dorian Sierra
    Participant

    Ondrej, thanks for your quick reply. Can you give me some examples or do I need to post my question in a different forum? I just opened my account today. Thanks.

  • #28424
    Profile photo of Don Jones
    Don Jones
    Keymaster

    Automating Excel isn't PowerShell per se, it's just standard .NET same as you'd do in C#. I'm not sure how much control that old API offers over Pivot Tables, but it's the only option. Are you at all familiar with the Office Automation NET API?

  • #28425
    Profile photo of Dorian Sierra
    Dorian Sierra
    Participant

    @Don
    Yes I am familiar with the API but I do not have access to an IDE where I need to automate this process...so I was thinking of using Powershell.

  • #28426
    Profile photo of Don Jones
    Don Jones
    Keymaster

    So, I know very little about the API, but there's no special trick to using it in PowerShell. And yes, if you get it to work, scheduling it is trivial.

    Code-wise, you need to know some basics about PowerShell coding. This isn't really a forum for teaching that, but I can try to answer specific questions. If you're after a starting point, look at the module Doug wrote at http://dougfinke.com/blog/category/excel/. That's the .NET API used in PowerShell.

  • #28427
    Profile photo of Dorian Sierra
    Dorian Sierra
    Participant

    @Don....Awesome.. I will take a look at that module. Thanks!

You must be logged in to reply to this topic.