PowerShell tricks – Convert copied range from excel to an array of PSObjects

In this post, I would like to share a simple function that converts tables copied from Excel to the clipboard into PowerShell objects.
While there are several more efficient ways to retrieve data out of Excel files (e.g. COM, EPPlus), I sometimes just want something quick and dirty to get the job at hand done.



Photo Credit: Sina Farhat – Webcoast via Compfight cc


PowerShell tricks – Open a dialog as topmost window

Windows.Forms provides easy access to several built-in dialogs (see MSDN: Dialog-Box Controls and Components). Here is an usage example to show a “FolderBrowse” dialog:

Add-Type -AssemblyName Windows.Forms
$FolderBrowser = New-Object System.Windows.Forms.FolderBrowserDialog
$FolderBrowser.Description = 'Select the folder containing the data'
$result = $FolderBrowser.ShowDialog()
if ($result -eq [Windows.Forms.DialogResult]::OK){
else {

While this works as expected, the dialog won’t show up as the topmost window. This could lead to situations where users of your script might miss the dialog or simply complain because they have to switch windows. Even though there is no built-in property to set the dialog as the topmost window, the same can be achieved using the second overload of the ShowDialog method (MSDN: ShowDialog method). This overload expects a parameter which indicates the parent windows of the dialog. Since the owning window will not be used after the dialog has been closed we can just create a new form on the fly within the method call:

Add-Type -AssemblyName System.Windows.Forms
$FolderBrowser = New-Object System.Windows.Forms.FolderBrowserDialog
$FolderBrowser.Description = 'Select the folder containing the data'
$result = $FolderBrowser.ShowDialog((New-Object System.Windows.Forms.Form -Property @{TopMost = $true }))
if ($result -eq [Windows.Forms.DialogResult]::OK){
else {


Photo Credit: Infomastern via Compfight cc

PowerShell tricks – Use Show-Command to add a simple GUI to your functions

The Show-Command cmdlet has been introduced in PowerShell Version 3 and is very useful to help discovering and learning more about PowerShell cmdlets and their respective parameters (also built into the ISE as the Show-Command Add-on).:

#Discover commands by running Show-Command without parameters
#Run Show-Command for a specific cmdlet
Show-Command Get-ChildItem

Show-Command can be also utilized for your own functions in order to provide your users with a simple GUI as it builds a graphical user interface for the provided function on the fly. Show-Command displays:

  • A drop-down for parameters that use the ValidateSet option
  • A check-box for switch parameters
  • A text box for any other type of parameter
  • An asterisk behind the parameter name in case the parameter is mandatory (the mandatory parameters are also enforced by disabling the run/copy buttons until the mandatory parameter is provided)
  • Each parameter set is displayed on a separate tab

Below is an example showing the features mentioned above using the NoCommonParameter switch to hide those parameters and PassThru in combination with Invoke-Expression in order to run the function with the chosen parameters (I couldn’t get this working otherwise):

Limitations of this approach are:

  • No enforcement for any other advanced function parameter option (e.g. ValidatePattern). The error message is displayed on the command prompt after clicking Run.
  • No option to disable/change the built-in functionality (e.g. disable buttons at the bottom, change minimum height)
  • No sophisticated options for specific parameter types (e.g. browse for files)

What are the GUI options you like to you use for your functions?
Update: I’ve added a follow-up post with a new function that removes the disadvantages mentioned above

Photo Credit: Sun Spiral via Compfight cc

PowerShell tricks – Useful default parameters to add to your profile

Since version 3 PowerShell introduced $PSDefaultParameterValues which is a built-in preference variable which lets you specify default values for any cmdlet or advanced function. You can read much more about it inside the respective help file. In a nutshell $PSDefaultParameterValues is a hash-table where (in its most common version) the key consists of the cmdlet name and parameter name separated by a colon (:) and the value is the custom default value:


I’ve added the following default parameter values to my profile (You can read here and here on how to work with profiles):

$PSDefaultParameterValues.Add('Export-Csv:NoTypeInformation', $true)
$PSDefaultParameterValues.Add('Get-Member:Force', $true)
$PSDefaultParameterValues.Add('Format-List:Property', '*')
$PSDefaultParameterValues.Add('Set-Location:Path', '..')
$PSDefaultParameterValues.Add('Get-Help:Detailed', $true )

What are other default parameter values that you use?


Photo Credit: coloneljohnbritt via Compfight cc

PowerShell tricks – Using dot(s) to refer to the current location

Most people are aware that PowerShell supports commandline navigation in the same way as the good old command prompt (see my previous post Improve PowerShell commandline navigation for ways to enhance this):

cd $env:USERPROFILE\Desktop
Resolve-Path '.'
#change to the current direction (doing nothing)
cd .
#move up one level
Resolve-Path '..'
cd ..

The above is using cd as the alias for the Set-Location Cmdlet providing:

  • One dot as an argument for the Path parameter representing the current location (changing the location to the current location does effectively nothing)
  • Two dots for the Path as an argument for the Path parameter representing one level higher than the current location

Since this is implemented by the Provider (at least that’s what I believe) the same concept can be used in many different places, basically every built-in command that has a Path parameter.:

#get all the built-in commands that have a Path parameter
Get-Command | where { $_.Parameters -and $_.Parameters.ContainsKey('Path') -and $_.HelpURI -and $_.HelpURI.StartsWith('http://go.microsoft.com') }

#navigation works also with the registry provider
cd HKLM:\Software\Microsoft
cd ..
cd c:
#open windows explorer using the current location (ii is the alias for Invoke-Item)
ii .
#same one level higher
ii ..
#Within ISE open all files in the current folder or one level higher
psedit .
psedit ..
#copy the current folder and its content to a folder one level higher
copy * ..\test

Please share if you know of more tricks using dot(s) as an argument to the Path parameter.


Photo Credit: Rein -e- Art via Compfight cc

PowerShell tricks – replace and transform a value within a string


Most PowerShell users know already of the String.Replace method and the PowerShell -replace operator. While the latter is quite powerful with its support for regular expressions … :

#using string's replace method
'this is good'.Replace('good','quite good')
#using PowerShell's replace operator to remove duplicate words
'this this is a a test' -replace '\b(\w+)(\s+\1){1,}\b', '$1'

… there is an even more powerful option available for cases where the characters that we want to replace need to be “transformed” in some way. Let’s look two examples:

  1. Increase a number within a string by one e.g. ‘XX33.txt’ should turn into ‘XX34.txt’
  2. Capitalize the second word within a sentence string e.g. ‘this is a test’ should turn into ‘this IS a test’

For the two examples there are of course multiple ways to accomplish this using PowerShell without involving regex replace, but there might be situations where it is your only option. For those case the .net Regex.Replace method could become your new best friend. The method provides an overload that takes the following three arguments:

Parameter Description
Input string Text that contains the character(s) to be replaced
Pattern string The regex pattern that identifies the character(s) to be replaced
MatchEvaluator A scriptBlock that is being evaluated against the captured matched characters

The 3rd argument (MatchEvaluator) is the key to the solution as the scriptBlock automatically receives the captured characters as an argument it can be used to transform the match(es) using any method available. Let’s use the same to solve the two example problems:

The automatic $args[0] variable can be used to access the match(es) within the scriptBlock. Note that for the second example the ToUpper method is called on the Value property rather than the $args[0] variable directly. Furthermore the $counter variable needs to be declared in script scope (see also PowerShell scope write-up) in order to be visible within the scriptBlock for PowerShell version 3 or later.


Photo Credit: Indy Kethdy via Compfight cc

PowerShell tricks – Build an array of strings without quotation marks

This is one of the tricks I keep forgetting about and therefore document it here for myself but also in case someone else might find it useful.
In order to create an array of strings one usually does something like this:

$stringArray = "first", "second", "third", "fourth"

It involves quite some redundant characters in order to do a simple thing. This can be made easier using a simple function that is part of the excellent PowerShell Communicty Extensions. The QL (QL is short for Quote-List an idea borrowed from Perl) function has the following definition:

function ql {$args}
ql first second third fourth

Note that extraneous commas and quotation marks can be avoided using this approach. There is actually even a built-in cmdlet that can be used for the same purpose. Write-Output alias echo or write:

echo first second third fourth

If an element of the string array we’d like to create contains a space the element needs to be surrounded in quotes:

echo first second third fourth "with space"

As a bonus tip we can use a similar idea as for the ql function in order to create strings without having to limit them by quotation marks:

function qs {"$args"}
qs this is a long string without any quotes
#only gotcha is when using quotes (single or double) within the argument
#qs this does not' work
qs quotes require escaping using a `'backtick`' otherwise it will not work


Photo Credit: bjimmy934 via Compfight cc