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:
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.
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
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.
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.
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.
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:
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 download AmazonLinkerator.zip
A few years ago I created a javascript “bookmarklet” to generate Amazon links with my associate ID. A bookmarklet appears on the toolbar. Navigate to the amazon product page and click the bookmarklet button. An alert box pops up that displays HTMl code. Something like this:
Access 2013 Bible (Access Bible) (9781118490358): Michael Alexander, Dick Kusleika: Books
Press Ctrl+C to copy the code, and then paste it where ever you want.
The bookmarklet code:
();asin2=document.getElementsByName(‘ASIN.0′);if(asin2.length>0){num=asin2[0].value;}else{num=document.getElementById(‘ASIN’).value};var%20title=document.getElementsByTagName(‘title’)[0].innerHTML;var%20loc=%22%22+title+%22%22;loc=loc.replace(‘Amazon.com:%20′,”);alert(loc);
Here’s a non-vba linkerator
http://www.contextures.com/excelfiles.html#FN0025
[...] week, Dick Kusleika posted his Amazon Linkerator – an Excel file lets you create links to Amazon [...]