Results 1 to 9 of 9
  1. #1
    dashiellx's Avatar
    dashiellx is offline Falconer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Location
    Baltimore
    Posts
    49

    Insert Hyperlink in Excel from Access

    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 example, here is the first few line of the "summary" sheet:



    Now, for each category, there is a detail sheet with the name of the Workflow State (space removed) followed by "_Detail". In the above example, all of the categories are two rows, but they can be up to 5.

    Here is the code I have so far:

    Code:
    Set objApp = CreateObject("Excel.Application")
    Set objBook = objApp.Workbooks.Add(1)
    Set objSheet = objBook.Worksheets("Sheet1")
    objApp.Visible = True
    objApp.ActiveWindow.WindowState = xlMaximized
    With objSheet
        strSQL = ""
            strSQL = strSQL & " TRANSFORM 
        Set rstSummary = CurrentDb.OpenRecordset(strSQL, dbReadOnly)
        y = 7
            Do Until rstSummary.EOF
        For x = 1 To rstSummary.Fields.Count
                    .Cells(y, x) = rstSummary(x - 1)
                    If x = 1 Then
                strSheet = ""
                      strSheet = Chr(39) & Replace(rstSummary(0)," ","") & "_Detail" & Chr(39) & "!A1"
                      .Hyperlinks.Add .Range("A" & y), Address:="", _
                SubAddress:=Chr(39) & strRep & Chr(39), _
                 TextToDisplay:=rstSummary(0)
            End if
        next x
    etc.....
    I've tried every syntax I can think of and all I get is Run Time Error 5 Invalid procedure call or argument.

    Thanks.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Which line triggers error?

    Why is there not a complete SQL statement?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    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,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    dashiellx's Avatar
    dashiellx is offline Falconer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Location
    Baltimore
    Posts
    49
    Quote Originally Posted by Gicu View Post
    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.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I thought you wanted to have the hyperlink on the summary sheet navigate to A1 of the detail sheet but the detail sheet is not created yet.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    dashiellx's Avatar
    dashiellx is offline Falconer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Location
    Baltimore
    Posts
    49
    Correct, the detail sheet has not yet been created.

    I have tested creating hyperlinks to sheets that don't exist within excel VBA :

    Code:
    ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Range("C1"), Address:="", SubAddress:="'Test'!A1", TextToDisplay:= "Test"
    It creates just fine. I just need to create it from Access.

  7. #7
    dashiellx's Avatar
    dashiellx is offline Falconer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Location
    Baltimore
    Posts
    49
    Figured it out:

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

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Code:
    On Error Resume Next
    Dim oApp As Excel.Application
    Dim oPC As Excel.PivotCache
    Dim oPT As Excel.PivotTable
    Dim oWT As Excel.Workbook
    Dim oWS As Excel.Worksheet
            
    Dim lastRow As Long
    Dim lStartOfDataList As Long
    Dim lEndOfDatList As Long
    DoCmd.RunCommand acCmdSaveRecord
    
    
    'vcPrepareBAS_Export
    'Me.Refresh
    
    
    
    
    Application.Echo False
    Set oApp = GetObject("Excel.Application")
              If Err.Number <> 0 Then Set oApp = CreateObject("Excel.Application")
        With oApp
            .Visible = True
            .Workbooks.Close
            On Error GoTo 0
            .Workbooks.Add
            .Workbooks(1).Activate
            
            Set oWT = .ActiveWorkbook
            Set oWS = oWT.ActiveSheet
            
            .ScreenUpdating = False
            .DisplayAlerts = False
            'lets do the header
            oWS.Range("A1") = "Reporting Period:"
            oWS.Range("A1").Font.Name = "Calibri"
            oWS.Range("A1").Font.Size = 12
            oWS.Range("A1").Font.Color = 16744448
            'vc4/30/2020
            Dim strSheet As String
            strSheet = "GL Analysis"
            oWS.Hyperlinks.Add Anchor:=.Range("B1"), Address:="", _
                        SubAddress:=Chr(39) & strSheet & Chr(39) & "!A1", _
                        TextToDisplay:=strSheet
    Glad to hear, the above code also works for me, but I'm using early binding....
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    dashiellx's Avatar
    dashiellx is offline Falconer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Location
    Baltimore
    Posts
    49
    Always more than one way to skin the access cat.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Insert Hyperlink into Form from Table
    By Gsteel in forum Access
    Replies: 4
    Last Post: 09-12-2017, 05:26 AM
  2. Default path for Insert hyperlink code
    By jaworski_m in forum Programming
    Replies: 3
    Last Post: 02-13-2015, 10:42 AM
  3. Insert hyperlink - ensure absolute path
    By jaworski_m in forum Programming
    Replies: 1
    Last Post: 02-04-2015, 11:39 AM
  4. Replies: 22
    Last Post: 04-24-2014, 01:56 PM
  5. how to insert a hyperlink?
    By markjkubicki in forum Programming
    Replies: 1
    Last Post: 09-16-2010, 07:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums