Amazon Linkerator

Posted on March 13, 2013March 19, 2018 by Dick Kusleika

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 Amazon.com, 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
    doClip.GetFromClipboard
    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
    UpdateWeb
    
End Sub

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

doClip.GetFromClipboard

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

'update the web browser <- comments that don't help are great

UpdateWeb

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=""http://www.amazon.com/gp/product/"
    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=""http://www.assoc-amazon.com/e/ir?t="
    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=""http://ws.assoc-amazon.com/widgets/q?_encoding=UTF8&ASIN="
    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

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=""http://www.amazon.com/gp/product/"

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=""http://www.assoc-amazon.com/e/ir?t="

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=""http://ws.assoc-amazon.com/widgets/q?_encoding=UTF8&ASIN="

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, "\", "/")
    Else
        UserForm_Initialize
    End If
    
End Sub

Private Sub UpdateWeb()

If Len(msFile) > 0 Then

Me.webPreview.Navigate "file:///" & Replace(msFile, "\", "/")

Else

UserForm_Initialize

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
        doClip.PutInClipboard
        Me.tbxMessage.Text = "Image copied to clipboard"
    End If
    
End Sub

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

doClip.PutInClipboard

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?

Posted on November 27, 2012November 26, 2012 by Tushar Mehta

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.

MSHTML Version and getElementsByClassName

Posted on November 2, 2012March 20, 2018 by Dick Kusleika

Doug alerted me that Rob van Gelder’s In-Cell Charting post was yet another casualty of the great data loss of 2011. I got the post back up, but there were 85 comments on that post that really deserve to be put back. And I’m working on that.

To that end, I wrote some code that includes the getElementsByClassName method of the HTMLLIElement object.

With clsComment
    .AddNameFromCite hLIComment.getElementsByTagName("cite")(0)
    .AddDate hLIComment.getElementsByClassName("comment-meta commentmetadata")(0)
    .AddContent hLIComment.getElementsByTagName("p")
End With

With clsComment

.AddNameFromCite hLIComment.getElementsByTagName("cite")(0)

.AddDate hLIComment.getElementsByClassName("comment-meta commentmetadata")(0)

.AddContent hLIComment.getElementsByTagName("p")

End With

I’m parsing through the HTML from the wayback machine to reconstruct the database entries. When I ran this code on a different machine, I got an error. It did not recognize getElementsByClassName as a valid method. I checked the Object Browser and, sure enough, it wasn’t listed. Googling was fruitless. The only thing I saw was that IE doesn’t support getElementsByClassName prior to version 9. Well, that doesn’t apply to me. I’m not automating IE, I’m using the MSHTML Object Library.

So I check the version of mshtml.tlb and find that it’s 4.0.

I check on the other machine and…

The exact same version with different members. Nice. Finally, it dawned on me that I probably have never updated IE on the second system. I checked and noted that I still had version 8 installed. I updated to IE9 and the code ran perfectly. I checked the ObjectBrowser and the method was listed. I checked the TLB version and it’s still 4.0.

Clearly I don’t understand how object libraries work. I had assumed that MSHTML was independent of IE. It makes sense that they’re related, but I would have thought that IE9 used the object definitions in MSHTML, not the other way around. In reality, they’re probably both using object definitions from a third file. Anyway, I’m happy to have the problem solved, if not the mystery.

Introducing TM Match Target

Posted on March 2, 2012March 3, 2012 by Tushar Mehta

The TM Match Target add-in analyzes a list of numbers and finds combinations that sum to a given total. This has applications in a range of disciplines including processing receipts, reconciling payments such as health care insurance reimbursements or payments by a customer for many outstanding invoices, operations management and operations research, and supply change management.

There are a number of posts, easily found through Google or Bing, that address the same issue, including my own Find a set of amounts that match a target value. This add-in packages the analysis in an easy-to-use interface, includes a means to stop the analysis if it is taking too long and preserve the combinations found so far, together with various options on what results should be shown.

For more please visit http://www.tushar-mehta.com/excel/software/match_target/.

— Tushar Mehta

Get a Table from a Web Page with an XML Request

Posted on December 15, 2011December 14, 2011 by Dick Kusleika

I’m tired of trying to remember the library names and the syntax for doing this. I’m putting here so when I search for XMLRequest, Web Request, HTTPRequest, I can find it.

Sub GetData()


    Dim oHttp As MSXML2.XMLHTTP

    Dim sHtml As String

    Dim hDoc As HTMLDocument

    Dim hTable As HTMLTable

    Dim hRow As HTMLTableRow

    Dim hCell As HTMLTableCell

    Dim rStart As Range
    Const sURL As String = "http://www.contextures.com/xlsampledata01.html"
    Set oHttp = New MSXML2.XMLHTTP

    Set hDoc = New HTMLDocument

    Set rStart = Sheet1.Range("A1")
    'Send the web request

    oHttp.Open "GET", sURL

    oHttp.send
    'Give it enough time to process

    Do

        DoEvents

    Loop Until oHttp.readyState = 4
    'put the web page into an HTML Document

    hDoc.body.innerHTML = oHttp.responseText
    'Find the right table and write it to a sheet

    For Each hTable In hDoc.all.tags("TABLE")

        If hTable.Rows(0).Cells(0).innerText = "OrderDate" Then

            For Each hRow In hTable.Rows

                For Each hCell In hRow.Cells

                    rStart.Offset(hRow.RowIndex, hCell.cellIndex).Value = hCell.innerText

                Next hCell

            Next hRow

        End If

    Next hTable

End Sub

It sends a request to the sample data page over at Contextures, jams that into an HTML Doc, then writes the table out to a sheet.

Daily Dose of Excel

Haphazardly Posted Excel Information and Other Stuff

Category: Automation

Amazon Linkerator

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

MSHTML Version and getElementsByClassName

Introducing TM Match Target

Get a Table from a Web Page with an XML Request