SpurmanisA
Technical User
I'm new to VBA but have already had some initial success at using VBA to integrate Acces and Excel to produce a chemical inventory database that can be updated from spreadsheets. As an added feature to this dB, I wanted to be able to automate the retrieval of appropriate msds (material safety data sheets) links from each vendor's website.
As an initial test I set up a simple 2-column table in Excel with the following data:
Supplier Catalog #
Sigma 34871
Sigma D1878
Sigma 459771
Fisher BP176-100
Sigma I9516
Fisher A415-4
Sigma M1390
Sigma M4251
I then used the following VBA code to insert hyperlinks to the products in the third column:
Sub MSDSsearchBot()
Dim myRange As Range
A = 0: B = 0: j = 1:
Do
If A = "" Then
Exit Do
End If
A = Sheets(1).Cells(j, 1).Value 'set the supplier value
B = Sheets(1).Cells(j, 2).Value 'set the product catalog number
Set myRange = Sheets(1).Cells(j, 3) 'cell to insert product hyperlink
Select Case A 'select appropriate supplier
Case "Sigma"
myRange.Hyperlinks.Add Anchor:=myRange, _
Address:=" & B, _
ScreenTip:="Sigma Online", _
TextToDisplay:=B & " product info"
Case "Fisher"
myRange.Hyperlinks.Add Anchor:=myRange, _
Address:=" & B & "&Start=1&Count=13&SearchOrder=4&SearchWV=TRUE&SearchFuzzy=FALSE&Culture=", _
ScreenTip:="Fisher Online", _
TextToDisplay:=B & " product info"
End Select
j = j + 1 'move to the next row
Loop Until A = "" 'exits loop once you reach the end of the table
End Sub
I've run into 2 intitial problems:
1. The code only works some of the time for the Sigma vendor because some prodcut details are stored in a different directory:
My script runs fine but returns a dead link for the "SIAL" products (and there isn't a way to tell in advance which catalog numbers will fail in this manner). Is there some programmatic way to identify the dead links (like what I get for the first 3 Sigma products) so I can use it to set up a contingency loop to handle the data stored in the SIAL sub-dir (i.e., queuing on some kind of on-error re-direct)?
2: What I'd really like to do is follow the "MSDS" links on the sheets that are currently returned by my script and insert the appropriate weblinks into my Excel table. (e.g., manually following my "BP176-100 product info" link produces this link to the correct MSDS data: I'm not quite sure about how to go about getting VBA to return this second hyperlink however.
Sigma, seems to be even more complicated because manually clicking the MSDS link activates a javascript routine which always returns a generically titled pdf. Here I have no clue if there is even a handle for me to grab.
Any help or clues (even a good book ref) would be appreciated.
Thanks
Aleks
As an initial test I set up a simple 2-column table in Excel with the following data:
Supplier Catalog #
Sigma 34871
Sigma D1878
Sigma 459771
Fisher BP176-100
Sigma I9516
Fisher A415-4
Sigma M1390
Sigma M4251
I then used the following VBA code to insert hyperlinks to the products in the third column:
Sub MSDSsearchBot()
Dim myRange As Range
A = 0: B = 0: j = 1:
Do
If A = "" Then
Exit Do
End If
A = Sheets(1).Cells(j, 1).Value 'set the supplier value
B = Sheets(1).Cells(j, 2).Value 'set the product catalog number
Set myRange = Sheets(1).Cells(j, 3) 'cell to insert product hyperlink
Select Case A 'select appropriate supplier
Case "Sigma"
myRange.Hyperlinks.Add Anchor:=myRange, _
Address:=" & B, _
ScreenTip:="Sigma Online", _
TextToDisplay:=B & " product info"
Case "Fisher"
myRange.Hyperlinks.Add Anchor:=myRange, _
Address:=" & B & "&Start=1&Count=13&SearchOrder=4&SearchWV=TRUE&SearchFuzzy=FALSE&Culture=", _
ScreenTip:="Fisher Online", _
TextToDisplay:=B & " product info"
End Select
j = j + 1 'move to the next row
Loop Until A = "" 'exits loop once you reach the end of the table
End Sub
I've run into 2 intitial problems:
1. The code only works some of the time for the Sigma vendor because some prodcut details are stored in a different directory:
My script runs fine but returns a dead link for the "SIAL" products (and there isn't a way to tell in advance which catalog numbers will fail in this manner). Is there some programmatic way to identify the dead links (like what I get for the first 3 Sigma products) so I can use it to set up a contingency loop to handle the data stored in the SIAL sub-dir (i.e., queuing on some kind of on-error re-direct)?
2: What I'd really like to do is follow the "MSDS" links on the sheets that are currently returned by my script and insert the appropriate weblinks into my Excel table. (e.g., manually following my "BP176-100 product info" link produces this link to the correct MSDS data: I'm not quite sure about how to go about getting VBA to return this second hyperlink however.
Sigma, seems to be even more complicated because manually clicking the MSDS link activates a javascript routine which always returns a generically titled pdf. Here I have no clue if there is even a handle for me to grab.
Any help or clues (even a good book ref) would be appreciated.
Thanks
Aleks
