hyperlink field from access to excel (1 Viewer)

[masoud_sedighy]

I have a query inside that there is a hyperlink that comes from a hyperlink field in the table. now when i open this query in Excel, it shows hyperlink field like below text,

38687901-000-BE-DR-1011-00#\\bb2-1\DCC\Detail-ENG Documents\EQP\TBE\ZP11\38687901-000-BE-DR-1011-00.pdf#


please help how i con use and convert or formulate this address again to hyperlink in excel and it works.

 

[arnelgp]

after exporting you can modify the the cells
and convert them back to Hyperlink:

Public Sub fnTextToHyperLink(row as Integer, column as Integer)
'  row is the starting row that has hyperlink text
'  column is the starting column that has hyperlink text (Column A=1, B=2, etc)
    Dim var As Variant
    row = 2
    column = 2
    Do While ActiveSheet.Cells(row, column).Value <> ""
        ActiveSheet.Cells(row, column).Select
        var = Split(ActiveCell, "#", , vbTextCompare)
        Debug.Print var(0)
        Debug.Print var(1)
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=var(1) & "", _
        TextToDisplay:=var(0) & ""
        row = column + 1
    Loop
End Sub

 

[arnelgp]

after exporting you can modify the the cells
and convert them back to Hyperlink:

Call fnCall fnTextToHyperLink("d:\hyperlink.xlsx", 2, 2)

the first 2 is the row number where you want to apply hyperlink
the second 2 is the column number where you want to apply hyperlink

Public Sub fnTextToHyperLink(strWorkbook As String, row As Integer, column As Integer)
'  strWorkbook is the path and name of excel file (including extension .xlsx)
'  row is the starting row that has hyperlink text
'  column is the starting column that has hyperlink text (Column A=1, B=2, etc)
    
    Dim obj As Object
    Dim oSht As Object
    Dim var As Variant
    
    Set obj = CreateObject(strWorkbook)
    Set oSht = obj.Sheets(1)
    Do While oSht.Cells(row, column).Value <> ""
        var = Split(oSht.Cells(row, column).Value, "#", , vbTextCompare)
        oSht.Hyperlinks.Add Anchor:=oSht.Cells(row, column), Address:=var(1) & "", _
                TextToDisplay:=var(0) & ""
        row = row + 1
    Loop
    Set oSht = Nothing
    obj.Close True
    Set obj = Nothing
End Sub