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!
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?
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.