Originally Posted by
Gicu
I would try to add Anchor:
Code:
.Hyperlinks.Add Anchor: .Range("A" & y), Address:="", _ SubAddress:=Chr(39) & strRep & Chr(39), _
TextToDisplay:=rstSummary(0)
Also you don't show us what strRep is but should probably be stSheet from above (check it in the immediate window to ensure that it looks right).
Cheers,
Yep, the strRep was a typo. Unfortunately, adding Anchor didn't fix the issue. Here's where I am with full code (except formatting) so far:
Code:
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
Still getting error on the Hyperlinks.Add . I've also tried referring to cell rather than range wit the same result.
Code:
.Hyperlinks.Add Anchor:=.Cells(y, x), Address:="", _
SubAddress:=Chr(39) & strRep & Chr(39) & "!A1", _
TextToDisplay:=rstSummary(0)
Thanks for looking.