Create an integrated (WPF based) ISE Add-On with PowerShell

38887680_ffd3c02233_m

The goal of this post is to show you how to create an ISE Add-On that integrates itself graphically in a similar way as the built-in Show-Command Add-On (using the VerticalAddOnTools pane) without having to use Visual Studio and writing the code in C#. As an example I will walk you through the steps to create an Add-On that will generate comment based help for functions. The end product will look like this:
Add-ScriptHelp

While there are quite some tutorials around on how to do this using Visual Studio and C# (e.g.: here) I wanted to be able to do the same using PowerShell only.
I can’t take full credit for the approach since I’ve just modified what James Brundage came up with for his ISEPack Add-On. The function that takes care of the creation of the Add-On is ConvertTo-ISEAddOn. The version I’m using is heavily based on the original version that comes with the ISEPackv2 but also with ShowUI/ConvertTo-ISEAddOn. The main difference between the original and the modified version are additional…:

  • Option to compile the Add-On into a .dll for faster loading (The original version creates the Add-On on the fly)
  • Option to generate multiple Add-Ons and compile them into one .dll
  • In relation to the above I’ve also added options to specify the namespace and classname(s) used for the ISE Add-On(s)
  • Option to add a menu item for the Add-On(s) (this is for testing purpose more on that further down below)

ConverTo-IseAddOn can be used in two different “modes”:

  1. To create one or more ISE Add-On(s) in memory (that’s the way ISEPack is making use of it). :
    ConvertTo-ISEAddOn -ScriptBlock $addScriptHelp -AddVertically -Visible -DisplayName "Add-ScriptHelp" -addMenu
    

    In this case the generated code block is compiled and loaded into memory via Add-Type this would require the code to be re-generated (via ConvertTo-ISEAddOn) on every start of the ISE. I’m using the functionality only for testing purpose while developing a new Add-On.

  2. To create on or more ISE-AddOn(s) and compile them into a .dll.
    ConvertTo-ISEAddOn -ScriptBlock $addScriptHelp -NameSpace $namespace -DLLPath $dllPath -class $classes
    

    This option has the advantage that the code generation needs to happen only once and the usage of the Add-On(s) only requires the .dll to be loaded.

The source code for Convert-ISEAddOn is a bit too long to embed but you can get it from Gist if you want to follow along.
The actual functionality to create the WPF UI, grab the values and generate the comment based help is done in a bit more than 100 lines. In this case I’m using ShowUI to help me generating the WPF UI but this can of course also be done without (you can see some examples of this within some of the other functions that I’ve added to my own ISE Add-On ISEUtils). I’ve updated the function with some AST parsing in order to get the parameter names automatically in case the cursor is placed inside a function while launching the AddOn::

$addScriptHelp ={
    #get the parameters of the enclosing function at the current cursor position if any
    $lineNumber = $psISE.CurrentPowerShellTab.Files.SelectedFile.Editor.CaretLine
    $code = $psISE.CurrentPowerShellTab.Files.SelectedFile.Editor.Text
    $Errors = $Tokens = $null
    $AST = [System.Management.Automation.Language.Parser]::ParseInput($Code, [ref]$Tokens, [ref]$Errors)
    $functions = $AST.FindAll({ $args[0].GetType().Name -like "*FunctionDefinition*Ast" }, $true ) 
    $enclosingFunctionParamNames = -1
    foreach ($function in $functions){
        if ($function.Extent.StartLineNumber -le $lineNumber -and $function.Extent.EndLineNumber -ge $lineNumber){
            if ($function.Body.ParamBlock){
                $enclosingFunctionParamNames = $function.Body.ParamBlock.Parameters.Name.VariablePath.UserPath
            }
            else{
                $enclosingFunctionParamNames = $function.Parameters.Name.VariablePath.UserPath
            }
            break
        }
    }
    $dynamicParams = $false
    if ($enclosingFunctionParamNames -ne -1){
        $dynamicParams = $true
    }
    New-StackPanel {
        New-TextBlock -FontSize 17 -Margin "24 2 0 3" -FontWeight Bold -Text "Synopsis"
        New-TextBox -Margin "7, 5, 7, 5" -Name "txtSynopsis"
        New-TextBlock -FontSize 17  -Margin "24 2 0 3" -FontWeight Bold -Text "Description"
        New-TextBox -Margin "7, 5, 7, 5" -Name "txtDescription"
        if ($dynamicParams){
            foreach ($paramName in $enclosingFunctionParamNames){
                New-TextBlock -FontSize 17 -Margin "24 2 0 3" -FontWeight Bold -Text "Parameter description: $paramName" 
                New-TextBox -Margin "7, 5, 7, 5" -Name ("txt$paramName" + 'Desc')
            }
        }
        else{
            New-TextBlock -FontSize 17 -Margin "24 2 0 3" -FontWeight Bold -Text "1. Param"
            New-TextBox -Margin "7, 5, 7, 5" -Name "txtFirstParamName"
            New-TextBlock -FontSize 17 -Margin "24 2 0 3" -FontWeight Bold -Text "1. Param Description" 
            New-TextBox -Margin "7, 5, 7, 5" -Name "txtFirstParamDesc"
            New-TextBlock -FontSize 17 -Margin "24 2 0 3" -FontWeight Bold -Text "2. Param"
            New-TextBox -Margin "7, 5, 7, 5" -Name "txtSecondParamName"
            New-TextBlock -FontSize 17 -Margin "24 2 0 3" -FontWeight Bold -Text "2. Param Description"
            New-TextBox -Margin "7, 5, 7, 5" -Name "txtSecondParamDesc"
        }
        New-TextBlock -FontSize 17 -Margin "24 2 0 3" -FontWeight Bold -Text "Link"
        New-TextBox -Margin "7, 5, 7, 5" -Name "txtLink"
        New-TextBlock -FontSize 17 -Margin "24 2 0 3" -FontWeight Bold -Text "1. Example"
        New-TextBox -Margin "7, 5, 7, 5" -Name "txtFirstExample"
        New-TextBlock -FontSize 17 -Margin "24 2 0 3" -FontWeight Bold -Text "2. Example"
        New-TextBox -Margin "7, 5, 7, 5" -Name "txtSecondExample"
        New-CheckBox -Margin "5, 5, 2, 0"  -Name "chkOutput" {
            New-StackPanel -Margin "3,-5,0,0" {
                New-TextBlock -Name "OutputText" -FontSize 16 -FontWeight Bold -Text "Copy to clipboard"
                New-TextBlock -FontSize 14 TextWrapping Wrap
            }
        }
        New-Button -HorizontalAlignment Stretch -Margin 7 {
            New-TextBlock -FontSize 17 -FontWeight Bold -Text "Add to ISE"
        } -On_Click{
            $txtSynopsis = ($this.Parent.Children | where {$_.Name -eq "txtSynopsis"}).Text 
            $txtDescription = ($this.Parent.Children | where {$_.Name -eq "txtDescription"}).Text
            if ($dynamicParams){
                foreach ($paramName in $enclosingFunctionParamNames){
                    Set-Variable ("txt$paramName" + 'Desc') -Value ($this.Parent.Children | 
                        where {$_.Name -eq ("txt$paramName" + 'Desc')}).Text
                }
            }
            else{
                $txtFirstParamName = ($this.Parent.Children | where {$_.Name -eq "txtFirstParamName"}).Text
                $txtFirstParamDesc = ($this.Parent.Children | where {$_.Name -eq "txtFirstParamDesc"}).Text
                $txtSecondParamName = ($this.Parent.Children | where {$_.Name -eq "txtSecondParamName"}).Text
                $txtSecondParamDesc = ($this.Parent.Children | where {$_.Name -eq "txtSecondParamDesc"}).Text
            }
            $txtLink = ($this.Parent.Children | where {$_.Name -eq "txtLink"}).Text 
            $txtFirstExample = ($this.Parent.Children | where {$_.Name -eq "txtFirstExample"}).Text 
            $txtSecondExample = ($this.Parent.Children | where {$_.Name -eq "txtSecondExample"}).Text 
            $chkOutput = ($this.Parent.Children | where {$_.Name -eq "chkOutput"}).isChecked
            $helptext=@"
    <#    
    .SYNOPSIS
        $txtSynopsis
    .DESCRIPTION
        $txtDescription
"@
            if ($dynamicParams){
                foreach ($paramName in $enclosingFunctionParamNames){
                    $txtParamDesc = Get-Variable -Name ("txt$paramName" + 'Desc') -ValueOnly
                    $helpText+="`n`t.PARAMETER $paramName`n`t`t$txtParamDesc"
                }
            }
            else{
                if ($txtFirstParamName) {
                    $helpText+="`n`t.PARAMETER $txtFirstParamName`n`t`t$txtFirstParamDesc"
                }
                if ($txtSecondParamName) {
                    $helpText+="`n`t.PARAMETER $txtSecondParamName`n`t`t$txtSecondParamDesc"
                }
            }

            if ($txtFirstExample) {
                $helpText+="`n`t.EXAMPLE`n`t`t$txtFirstExample"
            }
            if ($txtSecondExample) {
                $helpText+="`n`t.EXAMPLE`n`t`t$txtSecondExample"
            }
            if ($txtLink) {
                $helpText+="`n`t.LINK`n`t`t$txtLink"
            }
        $helpText+="`n" + @"
    .NOTES 
        CREATED:  $((Get-Date).ToShortDateString())
        AUTHOR      :  $env:USERNAME
	    Changelog:    
	        ----------------------------------------------------------------------------------                                           
	        Name          Date         Description        
	        ----------------------------------------------------------------------------------
	        ----------------------------------------------------------------------------------
  
"@.TrimEnd() + "`n`t#>"
            if ($chkOutput) {
                $helptext | clip
		    } 
            $psise.CurrentPowerShellTab.Files.SelectedFile.Editor.InsertText($helpText)  
        }
    }  
}

For a test run we can use the first mode of ConvertTo-ISEAddOn:

ConvertTo-ISEAddOn -ScriptBlock $addScriptHelp -AddVertically -Visible -DisplayName "Add-ScriptHelp" -addMenu

If you followed along this should successfully generate and load the Add-On (remember that this also requires the ShowUI module to be present). Just in case I’ve also uploaded the complete code so far (+ what follows) separately to GitHub.
Since everything is working fine we can go ahead now and compile the Add-On into a .dll:

$dllPath = "$env:USERPROFILE\Desktop\AddScriptHelp.dll"
ConvertTo-ISEAddOn -ScriptBlock $addScriptHelp -NameSpace ISEUtils -DLLPath $dllPath -class AddScriptHelp

To have the function constantly available in the ISE Add-On we need to add the following to your profile (you can read here and here to see how to work with profiles). This will add an entry to the Add-ons menu and load the Add-On when the entry is clicked:

Add-Type -Path $dllPath
$addScriptHelp = {
    #check if the AddOn if loaded if yes unload and re-load it
    $currentNameIndex = -1
    $name = 'Add-ScriptHelp'
    $currentNames = $psISE.CurrentPowerShellTab.VerticalAddOnTools.Name
    if ($currentNames){
        $currentNameIndex = $currentNames.IndexOf($name)
        if ($currentNameIndex -ne -1){
            $psISE.CurrentPowerShellTab.VerticalAddOnTools.RemoveAt($currentNameIndex)
        }
    }
    $psISE.CurrentPowerShellTab.VerticalAddOnTools.Add($name,[ISEUtils.AddScriptHelp],$true)
    ($psISE.CurrentPowerShellTab.VerticalAddOnTools | where {$_.Name -eq $name}).IsVisible=$true
}
$psISE.CurrentPowerShellTab.AddOnsMenu.Submenus.Add('Add-ScriptHelp', $addScriptHelp, $null)

As mentioned above this function and more is also part of my ISE Add-On ISEUtils

shareThoughts


Photo Credit: Nomad Photography via Compfight cc

Advertisements

Using Microsoft SyncToy through PowerShell

4745206997_ac577b3248_m
This post is about running Microsoft SyncToy via PowerShell. For those that don’t know SyncToy:

SyncToy 2.1 is a free application that synchronizes files and folders between locations. Typical uses include sharing files, such as photos, with other computers and creating backup copies of files and folders.

SyncToy has been around already since good old Windows XP times and even though there are alternative freeware applications it’s still one of my favorite tools for the job.
While SyncToy already comes with a commandline version out of the box, it’s lacking quite some features as compared to the graphical user interface:

  • No option to preview the sync operation
  • No progress indication
  • No option to exclude subfolders
  • No option to exclude files by attributes (e.g. hidden, system)
  • No option to specify recursion
  • No option to specify action for overwritten files

Googling around for solutions I came across two related posts on codeproject.com:

Following translating the suggest approach to PowerShell it seemed to be quite easy to accomplish what I wanted utilizing the SyncToyEngine.dll .NET assembly that comes with the SyncToy installation. Considering that I would have setup already a folder pairing called ‘Test’ using the GUI, the following code should initiate the sync operation (it’s important to use the correct version of PowerShell to test this (i.e. SyncToy(x64) needs to be run via PowerShell x64):

$syncToyEnginePath = Resolve-Path 'c:\Program Files*\SyncToy 2.1\SyncToyEngine.dll'
#load the dll
Add-Type -Path $syncToyEnginePath

#retrieve the sync engine configuration
$syncToyEngineConfigPath = "$env:LOCALAPPDATA\Microsoft\SyncToy\2.0\SyncToyDirPairs.bin"
$bf = New-Object Runtime.Serialization.Formatters.Binary.BinaryFormatter 
$sr = New-Object IO.StreamReader($syncToyEngineConfigPath)
do{
    $seConfig = [SyncToy.SyncEngineConfig]$bf.Deserialize($sr.BaseStream)
    if ($seConfig.Name -eq 'Test'){
        $engineConfig = $seConfig
        break
    }
}
while($sr.BaseStream.Position -lt $sr.BaseStream.Length)
$sr.Close()
$sr.Dispose()

#invoke the sync
$syncEngine = New-Object SyncToy.SyncEngine($engineConfig)
$syncEngine.Sync()

But unfortunately the last line causes PowerShell to hang. After multiple unsuccessful attempts to work around this (also implementing the same as a C# PowerShell cmdlet). I ended up writing a C# executable that only takes care of the synchronization and preview part, since I wanted to keep as much as possible of the code in PowerShell. The end result is a PowerShell module ‘SyncToy.psm1’ providing three functions:

Name Description
Get-SyncConfig To retrieve an existing sync configuration (FolderPair) either setup via Set-SyncConfig or GUI
Set-SyncConfig To configure a new Sync Configuration (FolderPair). Those can be stored into the default configuration that the GUI uses (default behaviour) or into a custom path
Invoke-Sync To preview a sync operation or to run the actual sync operation showing results and a progress bar

Let’s have a look at an example usage. Setting up two folders and a sync between the two. The below code is part of the module (Test-SyncToy), and can be downloaded via GitHub:

#Import the module
Import-Module $PSScriptRoot\SyncToy.psm1
#create folders for testing
#leftDir containing some content
$leftDir = "$env:TEMP\Left"
mkdir $leftDir | Out-Null
foreach ($num in 1..30){
    mkdir "$leftDir\test$num" | Out-Null
    foreach ($num2 in 1..10){
        $extension = '.txt'
        if ($num2 % 2){
            $extension = '.ps1'
        }
        "Test $num2" | Set-Content -Path ("$leftDir\test$num\test$num2" + $extension)
    }
}
#rightDir as the initial destination
$rightDir = "$env:TEMP\Right"
mkdir $rightDir | Out-Null

#exclude test10-test29 sub-folders from sync
$excludeFolders = (dir "$leftDir\test[1-2][0-9]" -Directory).FullName 

#setup the sync configuration
Set-SyncConfig -folderPairName 'Test' -leftDir $leftDir -rightDir $rightDir -syncMode Synchronize `
    -includedFilesPattern '*.ps1' -excludedSubFolders $excludeFolders 

#preview the sync
$previewResults = Invoke-Sync -folderPairName 'Test' -previewOnly
$previewResults
$previewResults.Action
#run the snyc
$results = Invoke-Sync -folderPairName 'Test' 
$results

Please let me know if you have any further suggestions, questions or comments about the module.
shareThoughts


Photo Credit: Tatters ❀ via Compfight cc

Using the String.Split method with multiple separator characters in PowerShell

15703896368_bfc55bdd19_m
This post is about what I thought of an odd behaviour when calling the .NET String.Split method with multiple separator characters from PowerShell. I first came across this myself but didn’t really pay much attention to it. Only after reading about it again over on Tommy Maynard’s blog, I decided to find out more.
Let’s have a look at an example first:

#using String.Split with one separator character works as expected
'This is a test'.Split('e')
#using multiple characters not so much
'c:\\test'.Split('\\')
'c:\\test'.Split('\\').Count

When running the second example trying to split a string based on double backslashes the result is an array of 3 strings instead of two. Let’s try to see why this is happening by retrieving the specific overload definition we are using:

#get the overload definition of the method we are using
''.Split.OverloadDefinitions[0]
#string[] Split(Params char[] separator)

Ok, it looks like this overload of the Split method expects a character array for the separator parameter. That is why we saw an additional split, every character of the string argument ‘\\’ is considered as a unique separator. Let’s see if String.Split has other overload definitions that accept a String as the separator argument:

''.Split.OverloadDefinitions | Select-String 'string[] separator' -SimpleMatch
<#
string[] Split(string[] separator, System.StringSplitOptions options)
string[] Split(string[] separator, int count, System.StringSplitOptions options)
#>

Indeed, there are two overloads that accept a String array argument instead. Let’s use the first one. We don’t need the StringSplitOptions parameter in this case and can therefore use a value of ‘None’ for the argument.

#this doesn't work since we need a String array
 'c:\\test'.Split('\\', 'None')
#finally we get only two parts back
 'c:\\test'.Split(@('\\'), 'None')
'c:\\test'.Split(@('\\'), 'None').Count

We could have used the -split operator in the first place, but that would have been to easy, right ;-). Furthermore with the String.Split method we can also split a string by multiple strings in just one go:

#using -split operator we need to escape the \ by doubling them since we are dealing with regular expressions
'c:\\test' -split '\\\\'
#splitting by two strings
'split by xx and yy in one go'.Split(('xx','yy'),'None')
#can be done also with -split using a scriptBlock

In conclusion, PowerShell provides a lot of options when it comes to splitting strings. Only looking at the separator parameter we have five options:

  1. Using String.Split’s first overload with a character array
  2. Using one of String.Split’s overloads that accept a string array
  3. Using the -split operator which accepts a string for the separator parameter (the string is actually interpreted as a regular expression)
  4. Using the -split operator which also accepts a ScriptBlock to determine the split operation. With that one can do a lot of things within the ScriptBlock $_ represents the current character, $args[0] the entire string, and $args[1] the current position within the entire string
  5. Finally there is also the .NET Regex.Split method with even more options but very similar to the -split operator

shareThoughts


Photo Credit: Matiluba via Compfight cc

Adding ‘Edit with PowerShell ISE’ and ‘Open PowerShell here (as Admin)’ to the context menu

1002140874_11967e2e51_m
In order to edit PowerShell files within PowerShell ISE I used to just drag and drop them from Windows Explorer into the ISE scripting pane. Unfortunately this doesn’t work anymore (I believe since Windows 8). The best explanation for the behaviour I found is here. In short drag and drop doesn’t work from Windows Explorer to an elevated application because of the higher Mandatory Integrity Control (MIC) level of the drag & drop source (Windows Explorer has a default level of medium while the elevated application runs with a high MIC level). There are several workarounds available but they all have negative side effects (elevating explorer to a higher privileges, disabling UAT…).
The workaround I’m using is adding a context menu for all PowerShell related files to Edit them with PowerShell ISE (while there is already a default ‘Edit’ context menu entry for PowerShell ISE I like to have it open a specific platform version elevated without loading profile). In addition to that I also like to add a context menu entry to open up a PowerShell command prompt from any folder or drive in Windows Explorer:
editWithPowerShellISE
openPowerShellHere
I wrapped the creation of the context menu into a function. The function offers to following options:

  • Add ‘Edit with PowerShell ISE’ context menu entry
  • Add ‘Open PowerShell here’ context menu entry (to Directories, Directory Backgrounds and Drive(s))
  • The type of context menu entry can be specified by using the contextType parameter
  • Specify the platform (x64 is default) of Windows PowerShell to open
  • Add the -noProfile switch when opening ISE or PowerShell console (-noProfile switch)
  • Run the PowerShell instance as admin (-asAdmin switch)

Example usage:

Add-PowerShellContextMenu -contextType editWithPowerShellISE -platform x86 -asAdmin
Add-PowerShellContextMenu -contextType openPowerShellHere -platform x86 -asAdmin

Below is the source code for ‘Add-PowerShellContextMenu’, but the function can also be downloaded via GitHub.

shareThoughts


Photo Credit: slack12 via Compfight cc

Automatically convert an Excel table to a checklist for JIRA

3161323938_3bb7be6248_m
JIRA supports a subset of Wiki Markup to add tables and other formatting to fields like Description or Comments. Writing the Wiki Markup manually is a bit time consuming though. To make the process a bit easier I ended up writing a Macro that converts an Excel based task tracker list into JIRA.
From this:
jiraTable
Into that:
jiraWikiMarkup
Running the Macro will copy the Wiki Markup to the clipboard from where it can be pasted into JIRA.
The Macro relies on a mapping between Status values and supported Markup symbols and assumes that the selected cell is within the range that needs to be converted before running the Macro. The VBA project requires the following additional references:

  1. Microsoft Scripting Runtime (for the Dictionary object)
  2. Microsoft Forms 2.0 Object Library (for the DataObject to provide the copy to clipboard functionality)


A Workbook containing the example can also be downloaded from GitHub.

shareThoughts


Photo Credit: chantrybee via Compfight cc

A nicer PromptForChoice for the PowerShell Console Host

378322049_c01db2cbf5_m
Sometimes it’s not possible to fully automate a certain process and we need some input from the user(s) of the script in order to determine the further path of action. If this is based on a fixed set of choices the built-in PromptForChoice method can come to the rescue. Here is an example:

Running the code below in PowerShell ISE will produce the following result:

PromptForChoiceISE
Running the same from the PowerShell console though will not look as fancy:
PromptForChoiceConsole
The reason for the difference is that the underlying PromptForChoice method on the System.Management.Automation.Host.PSHostUserInterface is declared as an abstract method. This basically means that the implementation details are up to the respective PowerShell host (as long as the method complies with the declaration).
As a result your script will not provide a consistent user experience across PowerShell hosts (e.g. ISE, Console). Because of this I wrote a little Windows.Form based helper function that provides the same features as PromptForChoice but will look the same across all PowerShell hosts:

Using Get-Choice like this:

Get-Choice "Pick Something!" (echo Option1 Option2 Option3) 2

Will look in both ISE and Console like that:

GetChoice
The most notable parts of the function are probably in the loop on lines 46-59. Where the buttons are created dynamically based on the options provided.:

foreach ($option in $Options){
        Set-Variable "button$index" -Value (New-Object System.Windows.Forms.Button)
        $temp = Get-Variable "button$index" -ValueOnly
        $temp.Size = New-Object System.Drawing.Size($buttonWidth,$buttonHeight)
        $temp.UseVisualStyleBackColor = $True
        $temp.Text = $option
        $buttonX = ($index + 1) * $spacing + $index * $buttonWidth
        $temp.Add_Click({ 
            $script:result = $this.Text; $form.Close() 
        })
        $temp.Location = New-Object System.Drawing.Point($buttonX,$buttonY)
        $form.Controls.Add($temp)
        $index++
}

Similar to the way it works in PromptForChoice preceding a character from within the option values with an ampersand (e.g. Option &1) will make the button accessible via ALT-key + the letter (e.g. ALT + 1).
The function can also be found in my GitHub repo.

shareThoughts


Photo Credit: zachstern via Compfight cc

Automatically clean-up excel data with multiple entries per cell separated by comma or line-breaks

tree
This is a follow-up from a previous post where I did the same using PowerShell.
A short recap first. The goal is to turn something like this:
CleanUpData
Into this:
CleanUpData2
In the original state some of the cells have multiple entries that are either separated by comma or line-breaks (via Alt+Enter). Furthermore several of those entries contain extraneous spaces. In order to tabulate the data the columns for those rows that contain multiple entries per cell also need to be cross-joined (or Cartesian product) to ensure all possible combinations for the entries are accounted for.
Rather than merely translating the recursive CartesianProduct function from the previous post into VBA I decided to follow a different approach.
Utilizing ADO to build a cross-join (without duplicates) across columns for rows that contain multiple entries. In order do that (I’m not really good at VBA and there might be better ways, that I’d love to hear about) the columns need to be copied to separate sheets so that the ADODB adapter recognizes them as separate tablesI actually found that there is no need to copy the columns to separate sheets since ADO also accepts range references.The SQL for the cross-join with only unique entries is very simple. Assuming the following setup (for the separated entries of the second row from our example):
cross-join3
The Macro to build the cross-join looks like this:

If you like to follow along here are the steps:

  1. Setup up the workbook as in the screenshot and save it (as .xlsm)
  2. Press Alt+F11 to open the Visual Basic Editor (VBE)
  3. Locate the project for your workbook (VBAProject(NAME.xlsm)) within the project tree view on the left hand side
  4. Right-Click the project entry and pick ‘Insert -> Module’
  5. Copy and paste the code into the new window
  6. From the menu at the top select Tools -> References…
  7. Tick the box for the entry ‘Microsoft ActiveX Data Objects 2.x Library’ and click ‘OK’
  8. Close the VBE window
  9. Run the Macro by hitting Alt+F8 and picking the entry for ‘CrossJoinRanges’ and clicking on ‘Run’

If everything worked out (in case it didn’t you can download the workbook via GitHub) the workbook should now contain a new sheet with the cross-joined content of the other sheets:
cross-join2
To turn this into a re-usable generic Macro the columns for the rows containing cells with multiple-entries need to be copied to a temporary sheet and the SQL statement needs to be build dynamically based on the number of columns and rows. Furthermore the Macro should also take care of rows that do not contain multiple entries, add the header to the output, and remove extraneous spaces from all entries. The final result uses two Subs one for the cross-join (CrossJoinRangesWithoutDupes) and another one that acts as the main entry point and to do the rest of the job (CleanData) and a little helper function (isSaved) to determine whether the workbook has ever been saved (otherwise I didn’t get the ADODB connection to work):

A workbook containing the Macro that produces the output shown at the top of the post can be downloaded from my GitHub repo. If you’d like to use the function more frequently I would recommend adding it to the Personal Macro Book as outlined here in order to have it available across all Excel files.

shareThoughts


Photo Credit: byb64 via Compfight cc