By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,397 Members | 1,467 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,397 IT Pros & Developers. It's quick & easy.

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

P: 4
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
Dec 6 '07 #1
Share this Question
Share on Google+
8 Replies


JustJim
Expert 100+
P: 407
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
Dec 7 '07 #2

Nathan H
100+
P: 104
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.
Dec 7 '07 #3

NeoPa
Expert Mod 15k+
P: 31,661
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.
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Public Sub HyperConv(ranCell As Range, Optional ByVal strText As String = "")
  4.   If Left(ranCell, 7) <> "http://" Then
  5.     Call MsgBox("Invalid cell reference", vbCritical Or vbOKOnly, "HyperConv")
  6.     Exit Sub
  7.   End If
  8.   If strText = "" Then strText = ranCell
  9.   Call ActiveSheet.Hyperlinks.Add(Anchor:=ranCell, _
  10.                                   Address:=ranCell, _
  11.                                   TextToDisplay:=strText)
  12. End Sub
Dec 9 '07 #4

NeoPa
Expert Mod 15k+
P: 31,661
Of course, if you wanted to run something against an imported (exported) sheet, then something like the following may work better :
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Public Sub HyperConv()
  4.   Dim lngRow As Long
  5.   Dim intCol As Integer
  6.   Dim ranCell As Range
  7.  
  8.   For lngRow = 1 To ActiveCell.SpecialCells(xlLastCell).Row
  9.     For intCol = 1 To ActiveCell.SpecialCells(xlLastCell).Column
  10.       Set ranCell = Cells(lngRow, intCol)
  11.       If Left(ranCell, 7) = "http://" Then
  12.         Call ActiveSheet.Hyperlinks.Add(Anchor:=ranCell, _
  13.                                         Address:=ranCell, _
  14.                                         TextToDisplay:=ranCell)
  15.       End If
  16.     Next intCol
  17.   Next lngRow
  18. End Sub
Dec 9 '07 #5

P: 4
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
Dec 11 '07 #6

P: 4
Of course, if you wanted to run something against an imported (exported) sheet, then something like the following may work better :
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Public Sub HyperConv()
  4.   Dim lngRow As Long
  5.   Dim intCol As Integer
  6.   Dim ranCell As Range
  7.  
  8.   For lngRow = 1 To ActiveCell.SpecialCells(xlLastCell).Row
  9.     For intCol = 1 To ActiveCell.SpecialCells(xlLastCell).Column
  10.       Set ranCell = Cells(lngRow, intCol)
  11.       If Left(ranCell, 7) = "http://" Then
  12.         Call ActiveSheet.Hyperlinks.Add(Anchor:=ranCell, _
  13.                                         Address:=ranCell, _
  14.                                         TextToDisplay:=ranCell)
  15.       End If
  16.     Next intCol
  17.   Next lngRow
  18. End Sub

Thanks NeoPa, I'll try it and let you know the outcome. MM
Dec 11 '07 #7

NeoPa
Expert Mod 15k+
P: 31,661
Thanks NeoPa, I'll try it and let you know the outcome. MM
Please do.
Welcome to theScripts!
Dec 11 '07 #8

P: 4
Please do.
Welcome to theScripts!
Thanks everyone, it works.

MM
Dec 12 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.