Excel: VBA

I needed to do stuff with strings in Excel, specifically, to get the initials from a full name (first name, last name, optional middle name), to make that the basis of an (non-unique) identifier. I had not previously written VBA (sure, there are other ways), so this was new, and dare I say, quite amusing.

This is an example of the input (B2 down) and the output (C2 down):

First point is how did I get the Column B data? I grabbed it from the Adventureworks example database (look elsewhere in the blog for that), and then ran this SQL over it:

 ;with names as 
 (select p.BusinessEntityID, p.FirstName + ' ' + ISNULL(p.MiddleName,'') + ' ' + p.LastName as FullName 
 from [AdventureWorks2016].[Person].[Person] p)
 select * from names
 order by BusinessEntityID

, which returns you this, for a very useful 3,000 rows or so of test/dummy data (btw):

Here is the code for the function that gets the initials from the full name:

But when I first opened  Excel for Office 365, I could see no way of editing vba. I happened to know I needed the Developer tab to be visible, and it is not visible, by default. The steps to enable the Developer tab are:

ExcelVba04

ExcelVba05

ExcelVba06

Once you have ticked the Developer check box, the Developer tab will appear, and you can then start editing (you will be prompted to enable macros, which you must accept):

ExcelVba07

Gist for the SQL, Gist for the VBA.

So going back to the first screenshot, the way you turn [Ken J Sánchez] into [KJS] is type [GetInitials(B2)] into the cell C2, then copy down. Now obviously I could have done this in a 1-liner, but that would have been ugly and hard to test. A bonus of this way is that you get a step-in debugger.

 

 

Advertisements

Powershell: enumerating Excel things

Use approved verbs.

cd C:\sandbox\PowerShell
function Unlock-Reference ($reference) {
 ([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
 [System.__ComObject] $reference) -gt 0)
 [System.GC]::WaitForPendingFinalizers()
}
function Show-WorkSheet 
{
 [CmdletBinding()]
 Param
 (
 [Parameter(Mandatory=$true)]
 $spreadSheet
 ) 
 if (! (Test-Path -Path $spreadSheet)) {
 Write-Host "Could not find $spreadSheet. Exiting"
 return
 }
 $excel = New-Object -ComObject excel.application
 $excel.visible = $false
 $workbook = $excel.WorkBooks.Open($spreadSheet)
ForEach ($workSheet in $workbook.Worksheets) {
 Write-Host($workSheet.Name)
 }
$dummy = Unlock-Reference($workSheet) 
 $dummy = Unlock-Reference($workbook)
 $dummy = Unlock-Reference($excel)
}