Archive for the ‘Automation’ Category.

Building an Excel Add-in

Hi there!

Only recently I read this quote somewhere: “If you want something done, ask a busy person”. I found two entirely different people as the originator of this quote: Benjamin Franklin and Lucille Ball. I wonder which it is…

Well, turns out I’ve been quite busy as of late. So I decided it was time to dust off some old stuff I prepared to add to my site but never came round to finishing (I must have become less busy when I was almost done :-) ).

If you’re about to embark on the journey to create an add-in out of a set of macro’s you have been using for some time now, this article is a nice read as it takes you through most of the steps needed when building an add-in for Excel.



Jan Karel Pieterse


Avoiding Date Conversion When Pasting an HTML Table

From Get Data From a Website…, MD comments:

However, the info in the table I copy is formatted ## / ## whereby # represents a number.
So when it tries to paste 10 / 10 in the worksheet for example, it auto-changes to 10/okt in my sheet.

This is a common problem that’s hard to solve. There’s no setting I can find in Excel that tells it to stop converting things that look like dates into dates. Since I’m reading in the data and putting it in the clipboard, I can just message the data before I do it. That’s not so easy when you just want to copy and paste, but if you’re using code, you may find the technique useful.

In the above linked post, I automated Internet Explorer to login to a website. I don’t automate Internet Explorer any more, preferring XML instead. But it doesn’t matter which you use. It all ends up in an HTMLDocument, so it’s the same from there. Also, this example doesn’t log into a webpage. It uses Contextures’ Sample Data.

Sub GetTableNoDateConversion()
    Dim xHttp As MSXML2.XMLHTTP
    Dim hDoc As MSHTML.HTMLDocument
    Dim hTable As MSHTML.HTMLTable
    Dim hCell As MSHTML.HTMLTableCell
    Dim doClip As MSForms.DataObject
    'Get the webpage
    Set xHttp = New MSXML2.XMLHTTP
    xHttp.Open "GET", ""
    'Wait for it to load
    Do: DoEvents: Loop Until xHttp.readyState = 4
    'Put it in a document
    Set hDoc = New MSHTML.HTMLDocument
    hDoc.body.innerHTML = xHttp.responseText
    'Find the third table
    Set hTable = hDoc.getElementsByTagName("table").Item(2)
    'Fix anything that looks like a date
    For Each hCell In hTable.Cells
        If IsDate(hCell.innerText) Then
            hCell.innerText = "'" & hCell.innerText
        End If
    Next hCell
    'put it in the clipboard
    Set doClip = New MSForms.DataObject
    doClip.SetText "<html>" & hTable.outerHTML & "</html>"
    'paste it to the sheet
    Sheet1.PasteSpecial "Unicode Text"
    'Make the leading apostrophes go away
    Sheet1.Range("A1").CurrentRegion.Value = Sheet1.Range("A1").CurrentRegion.Value
End Sub

Once I get the table into an HTMLTable object, I loop through all the HTMLTableCells to see if any of them looks like a date. If so, I put an apostrophe in front of it. The apostrophe is the Excel way to say “No matter what I type next, assume it’s text”. Except when you’re pasting special as Unicode Text. In that case, it doesn’t hide the apostrophe like it should. So the last line of the code is the equivalent of pressing F2 and Enter all the say down the column and forcing Excel to reevaluate its decision to ignore that apostrophe.

A VBA performance class

Hi everyone!

If you write a lot of VBA code you probably sometimes run into performance issues. A customer rings you and complains that the Excel file you built for them does not perform well. I’ve written a small article on my site, inspired by a post here: Error Handling via an Error Class. I needed a quick way to log and report performance of subroutines and I also wanted to see how many times a routine was called.

See: A VBA performance class


Jan Karel Pieterse

Sending Images via WinSCP

Since my recent move to Digital Ocean for hosting, I’ve had to make a change to how I upload images for this blog. I used to create an FTP file and a batch file, but as far as I know that doesn’t support SFTP. I’m using WinSCP to transfer files manually and learned that it has a command line interface. I made a procedure called SendViaSCP to replace my SendViaFTP.

Public Sub SendViaSCP(vFname As Variant)
    Dim aScript() As String
    Dim i As Long
    ReDim aScript(1 To 4 + UBound(vFname))
    aScript(1) = "option batch abort"
    aScript(2) = "option confirm off"
    aScript(3) = "open sftp://username:password@"
    aScript(UBound(aScript)) = "exit"
    For i = LBound(vFname) To UBound(vFname)
        aScript(3 + i) = "put " & Dir(vFname(i)) & " /home/wordpress/public_html/blogpix/"
    Next i
    Open "winscpup.txt" For Output As #1
    Print #1, Join(aScript, vbNewLine)
    Close #1
    Shell "winscpup.bat"
End Sub

The vFname arguments is a variant array that holds all of the files I selected from Application.GetOpenFileName. The aScript array holds three lines of setup, a line for each file, and an exit line.

The commands are joined together and written to a batch file and the batch file is run. It doesn’t solve the problem that Billkamm and Haines solved of having your username and password in a batch file, but I can live with it.

You might be wondering why I don’t just use the file upload functions in WordPress. What fun would that be?

A new tool: Trusted Document Manager

Hi everyone!

I have just published a new tool today, Trusted Document Manager. This little tool enables you to manage your list of trusted documents. Currently, Excel only allows you to either leave the list intact, or delete the entire list. This means all of your currently trusted documents become untrusted again so you have to enable macro’s on all of them once again. The tool allows you to remove just one file, remove an entire folder or even an entire drive. Also it offers to possibility to remove files which no longer exist from the list.

This is what the tool looks like:




Jan Karel Pieterse

Office 2013 VBA help files

Hi All,

Microsoft announced today that the Help files for Office 2013 VBA have been made available for download.
The help files are for:

Office Shared

Note that these files do not offer context sensitive help (F1), they are presented as stand-alone help files which you have to open manually.

I invite you all to give your opinions about VBA help. Make sure you include the Office version with your comments.

Amazon Linkerator

Since I can’t install JPSoftwareTech’s Amazon link generator, I decided to write my own. Fewer features, less choice because that’s how I roll. I recommend JP’s version – it’s very well done.

My tastes and habits are very quirky, so this “utility” is very specialized to the way I like to work. There may be some good stuff in there even if it’s not something you’d use yourself. It looks like this:

amazon link generator

The normal workflow is to find the product on, launch the utility, tab to whichever link I want, and press Esc. I guess I’ll have to edit the Description field most of the time too.

Here’s how it works:

  • If the clipboard has a URL in it that matches this regular expression, the form is prefilled with the ASIN and description
  • The ASIN goes in the Product field, the hyperlink text goes in the Description field
  • Changes to either Product or Description updates the two link textboxes
  • Exiting either Product or Description refreshes the web browser control
  • Entering either link textbox puts that link in the clipboard
  • Esc closes the form

One shortcoming you may have noticed is that my Amazon Associates ID isn’t on the form. It’s in a constant in the code. It’s not intended to be a general purpose utility, but if you have the chops you can modify it for yourself. The other omission is that there are only two links. I only ever need a text link or a large image with a text link underneath it. I’m just boring that way.

Let’s look at some code for goodness sake.

Private Sub UserForm_Initialize()
    Dim lFile As Long
    Dim sInstructions As String
    Dim doClip As MSForms.DataObject
    Dim sClip As String
    Dim regEx As VBScript_RegExp_55.RegExp
    Dim regMatch As VBScript_RegExp_55.MatchCollection
    'Create a template web page for when the form is blank
    sInstructions = "Exit Product or Description to update browser <hr /> Enter Text Link or Image Link to copy to clipboard <hr /> Esc to close form"
    msFile = Environ("TEMP") & Application.PathSeparator & "AMZNLNK.html"
    lFile = FreeFile
    Open msFile For Output As lFile
    Print #lFile, sInstructions
    Close lFile
    'Check the clipboard for an amazon URL and fill in the form
    Set doClip = New MSForms.DataObject
    If doClip.GetFormat(1) Then
        sClip = doClip.GetText
        Set regEx = New VBScript_RegExp_55.RegExp
        regEx.Pattern = "http://www\.amazon\.com/([\w\-]+/)?(?:dp|dp/product|gp/product|exec/obidos/asin)/(?:\w+/)?(\w{10})"
        Set regMatch = regEx.Execute(sClip)

        If regMatch.Count > 0 Then
            Me.tbxDescription.Text = Replace(Replace(regMatch.Item(0).SubMatches.Item(0), "-", Space(1)), "/", vbNullString)
            Me.tbxProduct.Text = regMatch.Item(0).SubMatches.Item(1)
        End If
    End If
    'update the web browser <- comments that don't help are great
End Sub

Since you devoured that RegEx post from earlier this week, I’m sure that part needs no explanation. When there’s a match, here’s what the regMatch variable looks like.

vba regular expression object

I only have two groups in my regex pattern that store substrings. The other groups start with ?: so nothing is saved, only matched. That guarantees that I’ll have a SubMatch.Count of exactly two, even if both are Empty.

The Change events for Product and Description call the UpdateLinks procedure.

Private Sub UpdateLinks(ByVal sProduct As String, ByVal sDesc As String)

    Dim aImage(1 To 4) As String
    Dim aLink(1 To 12) As String
    Dim lFile As Long
    Const sPLACE As String = "||||"
    If Len(sDesc) = 0 Then sDesc = "No description set"
    aLink(1) = "<a href="""
    aLink(2) = sProduct
    aLink(3) = "/ref=as_li_tf_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN="
    aLink(4) = sProduct
    aLink(5) = "&linkCode=as2&tag="
    aLink(6) = msAssocID
    aLink(7) = """>" & sPLACE & "</a>"
    aLink(8) = "<img src="""
    aLink(9) = msAssocID
    aLink(10) = "&l=as2&o=1&a="
    aLink(11) = sProduct
    aLink(12) = """ width=""1"" height=""1"" border=""0"" alt="""" style=""border:none !important; margin:0px !important;"" />"
    aImage(1) = "<img border=""0"" src="""
    aImage(2) = sProduct
    aImage(3) = "&Format=_SL160_&ID=AsinImage&MarketPlace=US&ServiceVersion=20070822&WS=1&tag="
    aImage(4) = msAssocID & """ >"

    Me.tbxText.Text = Replace(Join(aLink, vbNullString), sPLACE, sDesc)
    Me.tbxImage.Text = Replace(Join(aLink, vbNullString), sPLACE, Join(aImage, vbNullString)) & "<br />" & Replace(Join(aLink, vbNullString), sPLACE, sDesc)
    lFile = FreeFile
    Open msFile For Output As lFile
    Print #lFile, Me.tbxText.Text & "<hr />" & Me.tbxImage.Text
    Close lFile
End Sub

This procedure builds up a couple of strings, sticks them in the textboxes, and sticks them in an html file. There are three things that I’m forcing myself to do: use Join with arrays instead of strings of ampersands and line continuation characters (easier to maintain, I tell myself); use Space(1) instead of ” “; and use vbNullString instead of “”.

The Exit events for Product and Description update the web browser control for a preview of what the links look like. The Change events actually build the HTML file in the user’s temp directory and the Exit events navigate to that.

Private Sub UpdateWeb()
    If Len(msFile) > 0 Then
        Me.webPreview.Navigate "file:///" & Replace(msFile, "\", "/")
    End If
End Sub

Not much to that. If msFile is somehow not initialized, UserForm_Initialize will put up the instruction template.

Lastly, both of the link textboxes contents are copied to the clipboard using the Enter event. Here’s the Image one:

Private Sub tbxImage_Enter()
    Dim doClip As MSForms.DataObject
    If Len(Me.tbxImage.Text) > 0 Then
        Set doClip = New MSForms.DataObject
        doClip.SetText Me.tbxImage.Text
        Me.tbxMessage.Text = "Image copied to clipboard"
    End If
End Sub

I have a textbox at the bottom to alert the user that something has happened. I should probably add a Copy To Clipboard button, a Refresh Preview button and a settings dialog so you could personalize it without changing code. But that’s for next version. If you want the rough version, you can have it.

You can buy Amazon Linkerator

Do consumers prefer the ease of an EXE installer or the transparency of a ZIP file?

For all the various add-ins available from my website, I have supported two downloadable formats, an EXE and a ZIP file. The EXE is easier to install and includes an uninstall capability. The ZIP hopefully provides the consumer greater transparency and control over what is on their computer.

In addition to the software downloadable from my website, I also develop custom solutions through my consulting work. Most of my projects are sponsored by senior executives in companies or otherwise people with substantial decision making authority. It turns out that even a Managing Director or an Executive Vice President is subject to the automated IT protocols in effect in their organization (e.g., Group Policy).

Small and midsize companies are more tolerant of the kinds of files their employees can download. So, it is my larger clients who prefer — actually, require — a solution acceptable to their respective corporate IT filters. That means no EXE and no MSI, not even inside a ZIP file.

That has been a major stumbling block in my migration to .Net. For all the advantage of the platform, providing an add-in inside a ZIP file is not one of them.

I wondered if that inability (or reluctance) to download an EXE extended to those who download software from my website. Using Google Analytics I decided to check their download preference/requirement. Would the data indicate a strong preference for EXE over ZIP or the other way around? Turns out the result is decidedly mixed! As the table below shows, with the exception of TM Plot and TM TOCCreator, the download choice is about 50-50!

The table below is a PivotTable based on Google Analytics data for my website. For each add-in the table lists the EXE and the ZIP download percentages together with a sparkline for the two formats.