How to export a hyperlink field from Access to Excel - VBA

[YISUZENG]

Does anyone know how to export a table with a hyperlink field from MS Access to Excel (using VBA), while the hyperlinks still clickable?

Thanks

MM

[JustJim]

Does anyone know how to export a table with a hyperlink field from MS Access to Excel (using VBA), while the hyperlinks still clickable?

Thanks

MM

I think that you would have to have code in the export subroutine to open the workbook you just created with the export (transferspreadsheet method) to then format the column or range as a hyperlink. Sadly I don't know enough of Excel's flavour of VBA to tell you how to do this. Someone here will though. Hang in there, I may be able to alert someone.

Jim

[Nathan H]

Does anyone know how to export a table with a hyperlink field from MS Access to Excel (using VBA), while the hyperlinks still clickable?

Thanks

MM

If the field's data type in the table is set to hyperlink, then it should be rather easy:

DoCmd.OutputTo acOutputTable, "Table1"

This works on Access 2003 to Excel 2003.

[NeoPa]

I don't know what special export features may be available for this, but there is a short procedure that can convert a text link to a hyperlink. Displayable text is an optional extra parameter.

Option Explicit
 
Public Sub HyperConv(ranCell As Range, Optional ByVal strText As String = "")
  If Left(ranCell, 7) <> "http://" Then
    Call MsgBox("Invalid cell reference", vbCritical Or vbOKOnly, "HyperConv")
    Exit Sub
  End If
  If strText = "" Then strText = ranCell
  Call ActiveSheet.Hyperlinks.Add(Anchor:=ranCell, _
                                  Address:=ranCell, _
                                  TextToDisplay:=strText)
End Sub

[NeoPa]

Of course, if you wanted to run something against an imported (exported) sheet, then something like the following may work better :

Option Explicit
 
Public Sub HyperConv()
  Dim lngRow As Long
  Dim intCol As Integer
  Dim ranCell As Range
 
  For lngRow = 1 To ActiveCell.SpecialCells(xlLastCell).Row
    For intCol = 1 To ActiveCell.SpecialCells(xlLastCell).Column
      Set ranCell = Cells(lngRow, intCol)
      If Left(ranCell, 7) = "http://" Then
        Call ActiveSheet.Hyperlinks.Add(Anchor:=ranCell, _
                                        Address:=ranCell, _
                                        TextToDisplay:=ranCell)
      End If
    Next intCol
  Next lngRow
End Sub

[YISUZENG]

If the field's data type in the table is set to hyperlink, then it should be rather easy:

DoCmd.OutputTo acOutputTable, "Table1"

This works on Access 2003 to Excel 2003.

Thanks, Nathan, I'll try it and let you know the outcome.

MM

[YISUZENG]

Of course, if you wanted to run something against an imported (exported) sheet, then something like the following may work better :

Option Explicit
 
Public Sub HyperConv()
  Dim lngRow As Long
  Dim intCol As Integer
  Dim ranCell As Range
 
  For lngRow = 1 To ActiveCell.SpecialCells(xlLastCell).Row
    For intCol = 1 To ActiveCell.SpecialCells(xlLastCell).Column
      Set ranCell = Cells(lngRow, intCol)
      If Left(ranCell, 7) = "http://" Then
        Call ActiveSheet.Hyperlinks.Add(Anchor:=ranCell, _
                                        Address:=ranCell, _
                                        TextToDisplay:=ranCell)
      End If
    Next intCol
  Next lngRow
End Sub

Thanks NeoPa, I'll try it and let you know the outcome. MM

[NeoPa]

Thanks NeoPa, I'll try it and let you know the outcome. MM

Please do.
Welcome to theScripts!

[YISUZENG]

Please do.
Welcome to theScripts!

Thanks everyone, it works.

MM