Saturday, September 1, 2012

Extract worksheets from Excel into separate files with PowerShell

I recently had need to dust off an old VB script I'd written years ago to get worksheets out of Excel files. I've also been curious about doing more with PowerShell, and besides feeling guilty about putting a VB script into use in 2012, it seemed like a really good learning opportunity.

So why not just rewrite the script in .NET? Well, you can definitely do that; in fact, the code would look very similar. However, not everyone is a .NET developer. I wrote the original VB script on a team where we were building C++ DLLs for ETL processing; .NET wasn't part of our code base. I also think there are plenty of IT roles - DevOps, DBAs, Network Admininstrators to name a few - that might find a simple PowerShell tool like this a little easier to use and/or modify for their needs.

So that being said, just copy & paste the code below into an empty .ps1 file, and you should be good to go. To use it, simply execute the following command (should work from command-line, batch file, or managed code):

PowerShell.exe -command "C:\ScriptFile.ps1" -filepath "C:\Spreadsheet.xls" -output_type "csv"

I did run into one problem / issue while writing this script - getting it to run the first time! Thanks to this great article by Scott Hanselman, I found out that there are some very tight Windows security restrictions on PowerShell scripts - particularly the ones you didn't write yourself. After reading his article, it seemed easier for me (and for anyone who wants to use my code) to just post the source code rather than a downloadable script with certificates, at least in this instance. Maybe if I write another PowerShell article I'll give the certificate thing a go.

If you get the error message I got - "The file C:\ScriptFile.ps1 cannot be loaded. The execution of scripts is disabled on this system. Please see "Get-Help about_signing" for more details." - you can enable execution of PowerShell scripts you've created by running the following command 'As Administrator':

PowerShell.exe Set-ExecutionPolicy RemoteSigned

Anyway, here's my script:


  1. Great Article, I find this from twitter and then, from codeproject, finally find the source, I will visit your blog very soon, and I think you are a great developer, I will let many people know your blog and tell my facebook friends and twit it in twitter. my facebook is

    1. @lovelylinlin:

      So glad you enjoyed the article, and thanks for the kind words. Thanks also for posting the first comment on my blog!

    2. Of course, I am honored to be the first people to comment your article, keep posting and I think one day people may find your article, I myself also keep a blog but I think it is so good as yours.welcome to visit my blog and if you like, I can put your blog link on my blog to promote it.of course, I have my requirement which is very easy for you, you can e-mail me if you take my blog PR is 1 and you can visit it here:

    3. Cool, thanks for sharing. Looks like you've been working on it for a while; awesome! Looks like you have a couple of articles related to my post here as well; I'd encourage anyone looking to accomplish the above task in .NET to take look at your posts.

    4. I think if you have time, I want to invite your self to be reviewer of e-iceblue(, since I know you are a very good developer, also, as for your review, e-iceblue will give you a free license which is 999$ packs all main products in e-iceblue, I sincerely invite you and hope to be friends with you.

  2. not working for me. got same bunch of sheets in each separate file

  3. Replies
    1. Sure, no problem! Can you post the PowerShell command you're using to run the script?