PowerShell Script to Bulk-Change Excel File Formats

Today I had to find a way to change file formats for a lot of files. Here at Jelastic we use JIRA for bug-report tracking. Every week I am getting tons of automated reports from JIRA to send around to our partners and so on. The problem is that JIRA exports reports in some weird html format with xls extention, and although Windows Excel can open it (after displaying ‘the file format and extension do not match’ warnings) – Macs cannot.

Opening and resaving lots of files manually was not an option – PowerShell was. 🙂

[UPDATE] I’ve made a few minor changes per feedback from Stanley – now properly managing file extansions and formats.

Here’s the script that you can reuse if you have a similar problem to solve:

############################
# PowerShell script to open all Excel files in a folder
# and re-save them in proper format
# (c) Dmitry Sotnikov
############################

# create COM object to use Excel
$objExcel = new-object -comobject excel.application

$objExcel.Visible = $True

# open the files to re-save one by one
dir D:\myfolder\*.xls | ForEach-Object {
$doc = $objExcel.WorkBooks.Open($_.FullName)

# Save in new format and with new extension
# Format codes can be found here:
# http://msdn.microsoft.com/en-us/library/office/ff198017.aspx
$doc.SaveAs(“$($_.Directory)\$($_.BaseName).xlsx”,
[Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook)
$doc.close()
}

$objExcel.Quit()
$objExcel = $null

Advertisement

Call for Sessions for PowerShell Deep Dive 2012 – San Diego, CA – April 29 – May 2

In case you missed the announcement from the PowerShell team at Microsoft, it is time to register for PowerShell Deep Dive 2012!

PowerShell Deep Dive is the most advanced PowerShell conference of the year with level 400 sessions and all main PowerShell MVPs and movers and shakers, as well as the PowerShell team members attending and presenting.

This year, it will be a full 3 day long conference with tons of great content and a lot of PowerShell v2 and v3 stuff – so don’t miss your chance and register today to get the early bird discount.

Even more importantly, the call for session proposals is still open till February 15. So if you have a few cool topics to preset, want to share your knowledge, become a star, and get your conference pass, travel and registration paid for (!) send your session abstracts to TEC2012@quest.com ASAP.

See videos from the Deep Dive 2011 here.

See you in San Diego!

 

 

Compile PowerShell scripts to exe

Script compilation to executable files is one of the features we have added in PowerGUI Pro 3.0.

You would likely want to use compile a script when you want to share it in the enterprise environment and you are not sure whether the other person is comfortable running scripts or you simply don’t want risking someone modifying the code.

To compile a PowerShell script:

1. Open the script in PowerGUI Pro Script Editor,
2. On the Tools menu, click Compile Script,
3. Then specify the name and path for the exe file you want to create:

The additional options you get are:

• Show or hide the PowerShell Console window when executing the script,
• Automatically close or keep the console window (if you do want to show it),
• Protect the script source code by obfuscating it using a password you specify,
• Add other PowerShell scripts  to the exe (if you have script which your main script is using).

[UPDATE] If your script access parameters – so will the exe file it generates. So for an executable generated from a script like:

param ($MyParam1, $MyParam2) "MyParam1: $MyParam1""MyParam2: $MyParam2"

You may use a command line like:
c:\Generated.exe -Arguments -MyParam2 "Value2" -MyParam1 "Value1"

 

To try this feature, you can install a trial version of PowerGUI Pro from here.

(Screenshot taken from the original PowerGUI 3.0 announcement which lists a lot of other great features we shipped in that release)

Quest Software, Kirk, PowerGUI, PowerShell

The news of Kirk Munro leaving Quest has generated so much emotional response, often including bashing of Quest Software, that I thought I have to write this quick post to summarize my opinion on the situation. (And by the way, for the record, I was not the manager Kirk mentioned in his blog post. :))

Kirk has been a tremendous member of the PowerGUI team, and when your position gets cut it is not fun at all – so I understand the tendency of us seeing this as the “big evil ungrateful corporation doing bad things” kind of thing.

At the same time, I don’t think this is what actually happened.

For many years Quest has been the major contributor to the PowerShell community and this contribution continues. Yes, being a public company Quest has to protect the interests of its shareholders and when the economy is not doing well some of the expenses have to get cut, and PowerGUI became one of the products which budget got adjusted. This is the sad reality here and such things happen. From what I know, Quest handled this unfortunate situation quite well giving Kirk a good package and offering options to stay involved.

Back in 2006, in the Monad days, Quest supported me: giving me the investment I requested, and allowing me to spend my work time to start PowerGUI and AD cmdlets development. These tools were created free and remain free. You are not even required to provide your email address and get marketing emails for using the tools. For a lot of IT professionals I know – their PowerShell life started with either (or both) of these tools. I have met many Active Directory administrators who told me that QAD cmdlets literally had changed the way they work.

PowerGUI VSX is our free and open source effort to bring PowerShell into Visual Studio.

PowerGUI.org website – again, created and maintained with Quest investment, is the most active PowerShell community in the internet (more active than Microsoft’s PowerShell newsgroups) – where many people get their PowerShell questions answered every day.

Quest is the major sponsor of the PowerShell Deep Dive – which remains significantly subsidized (just check out the discount you get there compared to regular TEC price). Anyone who was at the event can attest that this was one of the most advanced, intense and amazing PowerShell experience they’ve ever had.

From what I know, in none of these efforts Quest is abusing the relationship. Instead the company keeps doing what it can to give back to the community and make lives of IT professionals easier.

From my personal perspective, my career and visibility in the IT community got catapulted to stratosphere thanks to these investments. Seriously, how many IT pros knew about me or Kirk before PowerGUI and AD cmdlets? Both of us became celebrities thanks to Quest’s contributions to PowerShell.

So, am I sad that this happened and Kirk had to leave? Absolutely. I enjoyed working with Kirk and he is a good friend of mine. And yes without Kirk, the rest of the PowerGUI team including myself will have to work harder to keep the project advancing.

Does this make Quest evil, greedy and bad to the PowerShell community? I don’t think so. I really don’t see any companies besides maybe Microsoft investing so much in keeping PowerShell growing, and I think we should all keep that in mind. If you guys like what we are doing here at Quest for the PowerShell community – help us: buy the commercial versions of the products, be active in our forums, spread the news and love towards these efforts – this will help us keep going and protect us against any further cuts.

Thank you for listening.

Projects in PowerGUI Script Editor

Adam’s latest add-on gave me something I wanted to have in the script editor for a long time – the project tree where I can easily access files for each of my PowerShell projects, conveniently grouped together in a hierarchy.

1. Just unzip the add-on to your Documents\WindowsPowerShell\Modules folder.

2. Turn it on in File / PowerShell Libraries / Add-on.ScriptExplorer.

3. And start creating folders and adding files to them (multiselect works! 🙂

Download the new add-on here and leave your comments on the add-on page or Adam’s blog to let him know what you think!

Managing Certificate Revocation Lists and Certificate Stores

Vadims has published a couple of articles with great examples of how to use PowerShell to manage CRLs (Certificate Revocation List) and local certificate stores.

CRL tasks include:

• Importing CRL:

$crl = Import-QADCertificateRevocationList -File C:\pica-1.crl

• Reviewing CRL details:

$crl | format-list *

• Add CRL to local certificate store:

Add-QADCertificateRevocationList -CRL $crl -Store $store

• CRL removal:

Get-QADCertificateRevocationList -Store $store |
  where-object {$_.IssuedBy -like "sysadmins*"} |
  Remove-QADCertificateRevocationList -Store $store

• CRL export:

Export-QADCertificateRevocationList -CRL $crl -File c:\customcrl.crl

• Publishing CRL:

Publish-QADCertificateRevocationList -CRL $crl -CAName CustomCAName

• Unpublishing CRL:

Get-QADPKIObject CDP |
  Get-QADCertificateRevocationList |
  where-object {$_.IssuedBy -like "sysadmins*"} |
  Unpublish-QADCertificateRevocationList -CAName "CustomCAName"

For certificate store management, Vadims goes through:

• Exploring certificate stores:

Get-QADLocalCertificateStore -StoreLocation LocalMachine -StoreName My |
  Get-QADCertificate

• Adding certificate stores:

New-QADLocalCertificateStore -StoreLocation CurrentUser -StoreName CustomStoreNameToAdd

• Removing certificate stores:

Remove-QADLocalCertificateStore -StoreLocation LocalMachine -StoreName CustomStoreToDelete

For all these, Vadims is providing tons of details so I highly recommend checking those out:

Managing Certificate Revocation Lists (CRL) with PowerShell

Managing Certificate Stores with PowerShell