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.


A further trivial example. Gist.

ExcelVba01

 

Advertisements

SqlServer: get rowcounts for wildcards

Gist.

SQL Server: create a quick copy of a table

… for testing purposes, whatever.


 

I’m using SQL Server 2016:

Microsoft SQL Server 2016 (CTP3.2) - 13.0.900.80 (X64) 
 Dec 12 2015 13:18:12 
 Copyright (c) Microsoft Corporation
 Express Edition (64-bit) on Windows 10 Home 6.3 <X64> (Build 10240: )

select @@VERSION

Whenever I do an example, I’ll be looking to illustrate it using the AdventureWorks database, as that is pretty rich, and it is useful to know we are dealing with roughly the same data, constraints, etc.


The 1-liner for creating a copy of a table:

select * into target_table from source_table;

For example:

select * into t_person from [AdventureWorks2014].[Person].[Person];

Don’t expect any of the primary keys, constraints etc. to be pulled across – they won’t. And that is quite reasonable – surely e.g. a foreign key would have to be thought out before just copying it.

 

SQLServer recipe: Create file paths based on rows in a table

Use case: given  the products in the Product table (all examples use the AdventureWorks database), for those products whose reorder point is <= 5, for each product in that set create a root folder, and under each products root folder, create an Invoices folder, and an Orders folder. CLR could be used to actually create the folders, but as CLR is banned in many companies for security reasons, we can just use this output to create a static file of e.g. PowerShell commands. This example just gives the folders, rather than the full command line:

;with fileSystem as (
 select 'c:\ProductFiles\' as rootFolder
)
, product as (
select p.ProductID, f.rootFolder + p.Name as productFileLocation
 from [adventureworks2014].[production].[product] p
 cross apply fileSystem f
 where p.ReorderPoint <= 5
)
select ProductID, productFileLocation from product
union all
select ProductID, productFileLocation + '\Orders' from product
union all
select ProductID, productFileLocation + '\Invoices' from product
order by ProductID

ProductsSQL01

Sample of the result:

ProductsSQL02 ProductsSQL03