Access Creating Hyperlinks in Excel Worksheets

[romeerome368]

Hello Everyone,

I have some code that I'm working on. I think that my syntax is correct, but I am getting a run-time error 9 - Subscript out of range error message.

Below is a sample of the code that I am running.

What this code ultimately does is create hyperlinks for four (4) items to another worksheet.

The highlighted code is where I'm getting the error.

I am thinking that the syntax there is having trouble with the recordset member as the name of the worksheet that I want the hyperlink to exist in.

    strSQL2 = "SELECT Category FROM qrySummary WHERE SID = " & rst!SID & " "
    strSQL2 = strSQL2 & "GROUP BY Category HAVING Max([Study Info]);"
    Set rst2 = db.OpenRecordset(strSQL2)

    strSQL7 = "SELECT TOP 4 Mfg, HCO_Product_Desc, Sum([HCO_Extension]) As Spend FROM qryMarketShareDetail WHERE [Member_ID] = " & rst!SID
    strSQL7 = strSQL7 & " AND [Product_Master_PSC] = '" & rst2!Category & "' "
    strSQL7 = strSQL7 & " GROUP BY Mfg, HCO_Product_Desc ORDER BY Sum([HCO_Extension]) DESC;"
    Set rst7 = db.OpenRecordset(strSQL7)
    
    lngLastRow = 19
    
    For Each wks In wbk.Worksheets
        If rst7.EOF Then
            DoEvents
        ElseIf wks.Name = rst2!Category Then
            If rst2.EOF Then
                DoEvents
            Else
                Do Until rst7.EOF
                    [highlight #FCE94F][b][COLOR=#000000]wks.Hyperlinks.Add Anchor:=wbk.Worksheets(" & [highlight #EF2929]rst2!Category[/highlight] & ").Cells(lngLastRow, 5), Address:="", SubAddress:="'Market Share Detail'" & "!A2", _
                        TextToDisplay:=Trim(rst7![HCO_Product_Desc])[/color][/b][/highlight]
                    lngLastRow = lngLastRow + 1
                    rst7.MoveNext
                Loop
                rst7.MoveNext
                rst2.MoveNext
            End If
        End If
    Next wks

If anyone has any insight of what syntax error that I have made, you help would be greatly appreciated.

 

[romeerome368]

Hello Everyone,

I ended up fixing my own code. Thanks for looking, and any attempts to assist.

This is how I fixed my code.

[b]OLD:[/b]
wks.Hyperlinks.Add Anchor:=wbk.Worksheets(" & rst2!Category & ").Cells(lngLastRow, 5), Address:="", SubAddress:="'Market Share Detail'" & "!A2", _
                           TextToDisplay:=Trim(rst7![HCO_Product_Desc])

[b]NEW:[/b]                 
wks.Hyperlinks.Add Anchor:=wks.Cells(lngLastRow, 5), Address:="", SubAddress:="'Market Share Detail'!A1", _
                           TextToDisplay:=Trim(rst7![HCO_Product_Desc])

Instead of forcing the code to set the name of the worksheet, the For Each loop is going to move through the worksheet names, therefore I didn't have to use the recordset member.

Hopefully, this may help someone along the way when they are trying to use Access to create hyperlinks in Excel.

Thanks All.