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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
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 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
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.
|
1 2 3 4 5 6 7 8 9 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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