Hyperlink issue

[Ashfaque]

Hi,

I tried to manage some part of the below hyperlink and but not fully operational.

Through VBA code I am exporting vehicle record data on multiple sheets of one excel file. And in the same file and summary of the total sheets ( each sheet have named with different vehicle number) is being generated on one single sheet.

Let us suppose there are 10 sheets by name 1030-ABC, 1024-PQR..... and so on and one last sheet named as Summary on which all the vehicle numbers are being placed in one column.

What I want is to make hyperlinks on the vehicle numbers of summery sheet thru the vba code. I reached somehow to some level where I am enable to make hyperlink but to the same file - NOT TO DIFFERENT SHEETS in the same file.

The concerned lines only I placed here of the VBA code I used:
Code Tags Added by UG
Please use Code Tags when posting VBA Code
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/

.....
..... various lines of codes.

          objXl.Cells(SRow, 2).Value = rstNames![VNo]
....
....

'  ADD MAIN-ADDRESS
               objXl.Cells.Hyperlinks.Add _
                Anchor:=objXl.Cells(SRow, 2), _
                    Address:="", _
                        SubAddress:="", _
                            ScreenTip:="-", _
                                TextToDisplay:=""

Above works ok and but this managed only hyperlink to the vehicleNo that generating on Summery sheet but after clicking on vehicleno, it dont move to required particular sheet in the same file.

Below sub address might be require:
Code Tags Added by UG
Please use Code Tags when posting VBA Code
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/

'            ' ADD SUB-ADDRESS
           objSht("Sheet2").Hyperlinks.Add _
                Anchor:=objSht(rstNames![VNo]).Cells(cell.Row, 1), _
                    Address:="", _
                        SubAddress:=objSht.Name & "rstNames![VNo]" & cell.Address, _
                            ScreenTip:="", _
                                TextToDisplay:=""

But I am not aware how to do it.

Anybody there to help me ? Any suggestion / advise shall be appreciated...

Thanks in advance.
Ashfaque

 

[arnelgp]

here you may try this.
let me know if it works.

paste the code in a standard Module.

to use in your code:

Call HyperLinkToSummary ("name of excel file", "name of summary sheet")

Public Function HyperLinkToSummarySheet(ByVal FileName As String, ByVal SummarySheetName As String)
'*
'* as requested by Mr.Ashfaque
'*
'* Please read carefully
'*
'* parameters:
'*
'*      FileName    = the name of your excel file (eg. "d:\Reports\Excel\Vehicles.xlsx")
'*      SummarySheetName    =   the name of summary worksheet (eg. "Summary")
'*
'* to use:
'*
'*      Call HyperLinkToSummary ("d:\Reports\Excel\Vehicles.xlsx", "Summary")
'*
    
    Dim xlApp As Object
    Dim xlWBk As Object
    Dim xlSht As Object
    Dim xlSummSht As Object
    Dim i As Integer
    
    Set xlApp = CreateObject("Excel.Application")
    Set xlWBk = xlApp.Workbooks.Open(FileName, False, False)
    
    Set xlSummSht = xlWBk.Sheets(SummarySheetName)
    xlSummSht.Activate
    xlSummSht.Cells.Delete
    i = 1
    xlSummSht.Cells(i, 1) = "Vehicles"
    For Each xlSht In xlWBk.Worksheets
        If xlSht.Name <> xlSummSht.Name Then
            i = i + 1
            xlSummSht.Cells(i, 1).Select
            xlSummSht.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
            xlSht.Name & "!A1", TextToDisplay:=xlSht.Name
        End If
    Next
    Set xlSht = Nothing
    Set xlSummSht = Nothing
    xlWBk.Close True
    xlApp.Quit
    Set xlApp = Nothing

End Function

 

[Ashfaque]

Thanks for the code arnelgp,

Yet to work.... but one thing I didnt understand. My excel file is already opened on screen so your code is again opening another excel application.

The destination report file I kept as read only (so that each time end user will make it safe as)

I tried to attach my db at least 10 times but no success. Please see the link below where I attached db 2 days back in my last post.

https://www.access-programmers.co.uk/forums/showthread.php?t=295975

Again thanks in advance....

 

[Ashfaque]

Hi arnelgp,

I tried your code keeping in a separate module, set the correct path and executed it after even saving the excel file at the destiantion. But it produces error.

Then as said in my previous post, I just simply add 4-5 lines that creating hyperlink to each vehicle numbers appearing on the SUMMARY sheet corresponding to the other sheets available in in the same file.

But it just show the Hyperlinks on the vehicle numbers on SUMMARY sheet. In actual it is not directing the control over to the related sheet.

As another try, I saved the Vehicle Report at the destination, closed excel file and then executed your given module with correct path of file thru a command btn on main form but it produces error.

Can you please check this.

Thanks,
Ashfaque

 

[arnelgp]

You post vehExpReport. Xlsx

 

[Ashfaque]

Hi arnelgp,

I attached the excel file.

Thanks,
Ashfaque

 

[Ashfaque]

[SOLVED] Re: Hyperlink issue

Yes, I managed what I was looking for.

Thanks to all for their valuable time...

Ashfaque