Hyperlinks.Add from Access to Excel

[dashiellx]

Long story short, due to some very specific formatting requirements, I can't just export the data to excel and create a pivot table.

So, I need to create a tab for each category and on the first "summary" sheet have the value in Column A (the category) be a hyperlink that opens goes to Range "A1" on the detail sheet.

For each category, there is a detail sheet with the name of the Workflow State (space removed) followed by "_Detail". All of the categories are at least two rows, but they can be up to 5.

Here is the code I have so far:

Dim strSQL As String
Dim dteStartDate as Date
Dim dteEndDate as Date
Dim rstSummary As DAO.Recordset
Dim objApp As Object
Dim objBook As Object
Dim objSheet As Object
Dim x As Integer
Dim strSheet as String
 
dteStartDate = Me.txtReportStartDate
dteEndDate = Me.txtReportEndDate
 
Set objApp = CreateObject("Excel.Application")
Set objBook = objApp.Workbooks.Add(1)
Set objSheet = objBook.Worksheets("Sheet1")
objApp.Visible = True
objApp.ActiveWindow.WindowState = xlMaximized
i = 1
With objSheet
    .Name = DLookup("EmployeeLastName", "tblEmployeeMaster", "[EmployeeMasterID]=" & varItem) & "_RepQueue_Summary"
    .Range("A1") = "xxx"
    .Range("A2") = "xxx"
    .Range("A3") = "Manager: xxx"
    .Range("A4") = "Representative Workqueues Tracking Summary " & Format(dteStartDate, "mmmm d, yyyy") & " and " & Format(dteEndDate, "mmmm d, yyyy")
    .Range("A1:A4").Font.Bold = True
    strSQL = ""
    strSQL = strSQL & " TRANSFORM Sum(StatusBalance) AS SumOfEncounters"
    strSQL = strSQL & " SELECT Representative, EmployeeResponsibility  AS [Responsibility], Sum(Encounters) AS [Avg]"
    strSQL = strSQL & " FROM tblWeeklyQueueSummary_Temp"
    strSQL = strSQL & " GROUP BY Representative, EmployeeResponsibility,"
    strSQL = strSQL & " EmployeeLastName, EmployeeFirstName"
    strSQL = strSQL & " ORDER BY EmployeeResponsibility, EmployeeLastName, EmployeeFirstName"
    strSQL = strSQL & " PIVOT Format(DateValue([SummaryStatusDate]), 'd-mmm')"
    Set rstSummary = CurrentDb.OpenRecordset(strSQL, dbReadOnly)
        For x = 1 To rstSummary.Fields.Count
            .Cells(6, x) = rstSummary(x - 1).Name
        Next x
        y = 7
        Do Until rstSummary.EOF
            For x = 1 To rstSummary.Fields.Count
                If x = 1 Then
                    strSheet = ""
                    strSheet = Trim(Left(rstSummary(0), InStr(rstSummary(0), ",") - 1)) & "_"
                    strSheet = strSheet & Mid(rstSummary(0), InStr(rstSummary(0), ",") + 2, 1) & "_"
                    strSheet = strSheet & "WorkQueue_Summary"
                    Debug.Print strSheet
                    .Hyperlinks.Add Anchor:=.Range("A" & y), Address:="", _
                    SubAddress:=Chr(39) & strSheet & Chr(39) & "!A1", _
                    TextToDisplay:=rstSummary(0)
                Else
                    .Cells(y, x) = rstSummary(x - 1)
                End If
            Next x
            .Cells(y, 3).FormulaR1C1 = "=AVERAGE(RC[1]:RC[" & x - 2 & "])"
            y = y + 2
            rstSummary.MoveNext
        Loop
End with

I get run time error 5 on the Hyperlinks.Add method

Thanks for looking.

[NeoPa]

Hi. Welcome to Bytes.com.

There's a lot of good & tidy information in your question that makes most of it easy to follow. There's also some information missing that is quite hard to work without. An error message is always required. Error numbers are unhelpful on their own.

In this case, from what I can see, I would guess that hyperlinks don't like an empty or invalid address. How it determines what is valid I don't know but I would guess empty (ZLS) would be recognised as invalid quite simply.

I suspect you want to fill that data in later but you may need to create it with some sort of valid - or valid looking - value in order to get further.

All in all a pretty tidy question for a first time but there are a couple of variables that are referred to without any declarations and, as I say, the error message is always required when there is one.

[dashiellx]

Whoops, Run-Time Error '5' is Invalid Procedure Call or Argument. And yep, I did forgot to copy the Dim i/y = integer declarations when getting on the site. Sorry. :)

I don't believe it is the zero length string. As this works in excel:

ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Range("C1"), Address:="", SubAddress:="'Test'!A1", TextToDisplay:= "Test"

And the sheet Test doesn't exist in the workbook either.

Thanks for looking.

[dashiellx]

Figured it out:

.Range("A" & y).Select
ActiveCell.Hyperlinks.Add .Range("a" & y), "", Chr(39) & strSheet & Chr(39) & "!A1", , CStr(rstSummary(0))
 

[NeoPa]

Ah. I didn't recognise the first time that you were using line continuations. With indented code I find it helps to indent such to make it clear :

.Hyperlinks.Add Anchor:=.Range("A" & y), _ 
                Address:="", _
                SubAddress:=Chr(39) & strSheet & Chr(39) & "!A1", _
                TextToDisplay:=rstSummary(0)

I must admit I see nothing obviously wrong with your code. Perhaps you could use Debug.Print to show the values of all your parameters at the time of invoking and post them into the thread.

Another thing that might be helpful is to confirm that the code has been compiled before you run it.

Good work so far though. You're an easy person to work with :-)

[dashiellx]

I'm sure some of my coworkers would dispute the ease of working with me, but I take it. :-)

I was able to figure this out and I posted some working code above.

Thanks again for looking.