Author Posts

August 11, 2015 at 11:03 am

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!

August 11, 2015 at 11:06 am

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

August 11, 2015 at 11:11 am

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.

August 11, 2015 at 11:18 am

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?

August 11, 2015 at 11:31 am

@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.

August 11, 2015 at 11:40 am

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.

August 11, 2015 at 11:42 am

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