By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,397 Members | 1,425 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.

Access to Excel Hyperlinks, New question checked old posts

P: 6
Sorry guys, this is killing me.

I have been fighting this issue for several weeks, to no avail. I am exporting the results of a query to an excel sheet, with one of the columns being from a field of hyperlinks that link to files on the shared drive.

I CANNOT get the hyperlinks to be assigned. I can change the field name, but not the hyperlink address. Can't figure it out.

1) I have a header row
2) Column 16 is where the hyperlinks are

Here it is:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Hiring_submit_cmd_Click()
  2. ' Excel Handling
  3. Dim oExcel As Excel.Application
  4. Dim oWB As Excel.Workbook
  5. Dim oWS As Excel.Worksheet
  6. Dim SaveString As String
  7. Dim sa
  8. Dim HyperCount As Long
  9. Dim RanCell As Excel.Range
  10. Dim StrTxt As String
  11. Dim HyperlinkTxt As String
  13. For HyperCount = 2 To oWB.Sheets(1).UsedRange.Rows.Count
  14.      Set RanCell = oWB.ActiveSheet.Cells(HyperCount, 16)
  16.      StrTxt = StrConv(RanCell, vbProperCase)
  17.      If Len(StrTxt) > 1 Then _
  18.           call oWB.ActiveSheet.Hyperlinks.Add(anchor:=RanCell, _
  19.                Address:=StrTxt, TextToDisplay:=StrTxt)
  20.      Next HyperCount
Aug 25 '08 #1
Share this Question
Share on Google+
12 Replies

Expert Mod 15k+
P: 31,661
I know you've already checked and found How to export a hyperlink field from Access to Excel - VBA (I had to remove the post you put in there before you realised you needed to post in your own thread).

This can help you I'm sure, but first you need to think about the basics of your code. It appears that there is nothing in your code which establishes any link into Excel. Check out Application Automation first.

Your code seems to be missing so much it's hard to know where you're at. Let's get the basics sorted out first then we can move on from there to fixing the hyperlink situation.
Aug 25 '08 #2

P: 6
Roger, thanks NeoPa.

Let me post all of it. I was thinking that I would just cut to the chase of it, but here we go:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Hiring_submit_cmd_Click()
  2. Dim VarItem As Variant
  3. ' Query Handling
  4. Dim dbs As Database
  5. Dim qryname As String
  6. Dim QryDef As QueryDef
  7. ' Excel Handling
  8. Dim oExcel As Excel.Application
  9. Dim oWB As Excel.Workbook
  10. Dim oWS As Excel.Worksheet
  11. Dim SaveString As String
  12. Dim sa
  13. Dim HyperCount As Long
  14. Dim RanCell As Excel.Range
  15. Dim StrTxt As String
  16. Dim HyperlinkTxt As String
  19. '
  20. ' Development of SQLStr SQL statement, works fine...
  21. '
  23. ' Perform Query--------------------
  24. Set dbs = CurrentDb
  25. qryname = "HiringQuery"
  26. 'Delete old query
  27. For Each QryDef In dbs.QueryDefs
  28.     If (QryDef.Name = qryname) Then dbs.QueryDefs.Delete qryname
  29. Next QryDef
  30. Set QryDef = dbs.CreateQueryDef(qryname, SQLStr)
  31. ' Execute query
  32. DoCmd.SetWarnings False
  33. DoCmd.OpenQuery "HiringQuery"
  34. DoCmd.Close acQuery, qryname
  35. DoCmd.SetWarnings True
  37. ' Output to Excel-----------------
  38. SaveString = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & _
  39.     "Candidate Hiring Query " & Format(Now(), "ddmmmyyyy") & " " & Format(Now(), "hhnn") & ".xls"
  40. On Error GoTo Errorhandler
  42. DoCmd.Echo False
  43. DoCmd.SelectObject acTable, , True
  44. DoCmd.OutputTo acOutputQuery, "HiringQuery", acFormatXLS, SaveString
  45. DoCmd.SelectObject acTable, , True
  46. DoCmd.RunCommand acCmdWindowHide
  47. DoCmd.Echo True
  49. Set oExcel = New Excel.Application
  50. Set oWB = oExcel.Workbooks.Open(SaveString)
  51. 'Format Headers
  52. oWB.ActiveSheet.Range("A1").Value = "Last Name"
  53. oWB.ActiveSheet.Range("B1").Value = "First Name"
  54. oWB.ActiveSheet.Range("C1").Value = "M.I."
  55. oWB.ActiveSheet.Range("G1").Value = "Possible Reqs"
  56. oWB.ActiveSheet.Range("H1").Value = "Significant Comments"
  57. oWB.ActiveSheet.Range("I1").Value = "Yrs Experience"
  58. oWB.ActiveSheet.Range("J1").Value = "Service"
  59. oWB.ActiveSheet.Range("L1").Value = "Degree Details"
  60. oWB.ActiveSheet.Range("M1").Value = "Number of Schools"
  61. oWB.ActiveSheet.Range("N1").Value = "Deployments"
  62. oWB.ActiveSheet.Range("O1").Value = "Received"
  63. oWB.ActiveSheet.Range("P1").Value = "Resume Path"
  64. **************************************************************************
  65. ' Add hyperlinks
  66. For HyperCount = 2 To oWB.Sheets(1).UsedRange.Rows.Count
  67.     Set RanCell = oWB.ActiveSheet.Cells(HyperCount, 16)
  69.     StrTxt = StrConv(RanCell, vbProperCase)
  70.     If Len(StrTxt) > 1 Then _
  71.         Call oWB.ActiveSheet.Hyperlinks.Add(anchor:=RanCell, _
  72.                                 Address:=RanCell, TextToDisplay:=RanCell)
  74. Next HyperCount
  75. *************************************************************************
  76. oWB.ActiveSheet.Range("Q1").Value = "LOI Path"
  77. oWB.ActiveSheet.Rows("1:1").EntireRow.AutoFit
  78. oWB.ActiveSheet.Columns("A:n").EntireColumn.AutoFit
  80. oWB.Save
  81. Set oWS = Nothing
  82. If Not oWB Is Nothing Then oWB.Close
  83. Set oWB = Nothing
  84. oExcel.Quit
  85. Set oExcel = Nothing
  86. sa = Shell("C:\Program Files\Microsoft Office\OFFICE11\excel.exe " & _
  87.     Chr$(34) & SaveString & Chr$(34), vbNormalFocus)
  88. Exit Sub
  90. Errorhandler:
  92.     If Err.Number = 2302 Then
  93.         MsgBox ("Close Excel file and try again.")
  94. '    Else
  95. '        MsgBox ("Undefined BPMP error.")
  96.     End If
  97. End Sub
Aug 26 '08 #3

Expert Mod 15k+
P: 31,661
Generally you can do (and it's a good idea to where possible), but you must include the relevant data or your code will not be recognisable.

Had you simply included a comment saying briefly what the code had done and what the object variables were pointing to (much as you did for the code formulating the SQL string) that could also have worked.

Anyway, no harm, no foul. I'll see if I can find some time to go through this in some detail soon. Just coming to end of Lunch-hour at the moment so will have to squeeze in as and when.
Aug 26 '08 #4

Expert Mod 15k+
P: 31,661
You mentioned earlier that the links you're using are local files rather than web references. One major difference (that I think is actually your problem) is that in my code I knew all entries that needed converting were already fully qualified HTTP references (http://...). If you want this to work correctly for local file references you need to ensure that they follow the web addressing convention for local files. IE. "FILE://...".

In short, I expect that if you're using addresses of the form "C:\Temp\MySpreadsheet.xls" you will have problems. However, if you were to use (or doctor in the code to use) addresses of the form "FILE://C:\Temp\MySpreadsheet.xls" you would have no such problems.

Try this out and let us know how you get on.
Aug 26 '08 #5

P: 6

In short, I expect that if you're using addresses of the form "C:\Temp\MySpreadsheet.xls" you will have problems. However, if you were to use (or doctor in the code to use) addresses of the form "FILE://C:\Temp\MySpreadsheet.xls" you would have no such problems.

Try this out and let us know how you get on.
You were correct that this is what I was doing. However, after inserting the following (where AddressTxt is defined as a String), the field shows up looking like a hyperlink (as it did and underlined) but with no address linked:
Expand|Select|Wrap|Line Numbers
  1. For HyperCount = 2 To oWB.Sheets(1).UsedRange.Rows.Count
  2.     Set RanCell = oWB.ActiveSheet.Cells(HyperCount, 16)
  3.     AddressTxt = "FILE://" & StrConv(RanCell, vbProperCase)
  4.     StrTxt = StrConv(RanCell, vbProperCase)
  5.     If Len(StrTxt) > 1 Then _
  6.         oWB.ActiveSheet.Hyperlinks.Add anchor:=RanCell, _
  7.                                 Address:=AddressTxt, TextToDisplay:=StrTxt
  9. Next HyperCount
Setting Address and TextToDisplay directly to "FILE://" & Rancell and Rancell, respectively, yield a runtime error '5' invalid procedure call or argument. Same error occurs when I CALL the hyperlinks.add and use parentheses.

:( Was really hoping your suggestion would work.
Aug 26 '08 #6

Expert Mod 15k+
P: 31,661
Try something like this :
Expand|Select|Wrap|Line Numbers
  1. With oWB.ActiveSheet
  2.   For HyperCount = 2 To .UsedRange.Rows.Count
  3.     Set RanCell = .Cells(HyperCount, 16)
  4.     StrTxt = StrConv(RanCell, vbProperCase)
  5.     If Len(StrTxt) > 1 Then
  6.       AddressTxt = "FILE://" & StrText
  7.       Call .Hyperlinks.Add(Anchor:=RanCell, _
  8.                            Address:=AddressTxt, _
  9.                            TextToDisplay:=StrTxt)
  10.     End If
  11.   Next HyperCount
  12. End With
I've tried the effect of this code on some basic dummy data (in Excel 2003) and it worked fine for me (it linked to the spreadsheet I had in my original data).

In truth, I understand why you an error 5 (using a Range reference for TextToDisplay which requires a string variable), but not why your other attempts failed. Try with this and see what you get. Does it link when clicked on?
Aug 26 '08 #7

P: 6
... negative.

It couldn't be a reference library thing, could it? Here are the ones I have:

Access 11.0
OLE Automation
DAO 3.6
ActiveX Data Objects 2.1
Excel 11.0
Office 11.0
Outlook 11.0

That's the only other thing I can think of. All data gets from the correct Access records, is formatted properly, even the hyperlink fields are blue and underlined, but just no data in the hyperlink address field.

Another thing I have noticed, an anomoly. You know how each line is autocorrected for punctuation after that line has been left? Well, no matter what way I type in 'anchor,' it stays that way: AnChOr.

Regardless, thanks for the time in this.
Aug 26 '08 #8

Expert Mod 15k+
P: 31,661
Some interesting points. I can only say that I don't know of any link to References that might explain this.

What I will do is first explain the AnChOr:= thing, then suggest a test for you to carry out.

The VBA editor will generally correct all items that it has knowledge of. This includes reserved words as well as any name which has already been defined anywhere in your code. It will handle named parameters of procedures if they are defined in your code, but unless there is a specific declaration of the procedure and all its parameters (there is none for .Hyperlinks.Add()), then it will not have access to the correct capitalisation of the word. In short, for inbuilt procedures don't expect that to work (although it will for ones you've written yourself).

Now, what I want you to try out is the following :
Open a new workbook (Ctrl-N) in Excel and enter the address of one of your Excel files into cell A1 in normal Windows Explorer format (C:\Home\YourName\Excel\Test.xls for instance - but the file should exist for the test to make sense).

When you navigate away from the cell there should be no hyperlink set up.

Now, hit Alt-F11 (open and switch to the VBA Editor) and Ctrl-G (Switch to the Immediate Pane).

Enter the following and hit enter (use Copy / Paste from here) :
Expand|Select|Wrap|Line Numbers
  1. Call ActiveSheet.Hyperlinks.Add(Anchor:=Range("A1"), Address:="FILE://" & Range("A1").Text, TextToDisplay:=Range("A1").Text)
Do you find this link works when you click on it (It does for me)?
Aug 26 '08 #9

P: 6
Yes, doing that in excel does work...
Aug 27 '08 #10

Expert Mod 15k+
P: 31,661
That tells me that the code should work assuming the data I expect.

Try replacing the last bit with this (I've added some lines to show some extra information) and see what it shows in the Immediate Pane (Ctrl-G). I'd prefer the results for a single cell only if possible, but if not then dump the lot in a post and we'll see what we can determine.
Expand|Select|Wrap|Line Numbers
  1. With oWB.ActiveSheet
  2.   For HyperCount = 2 To .UsedRange.Rows.Count
  3.     Set RanCell = .Cells(HyperCount, 16)
  4.     StrTxt = StrConv(RanCell, vbProperCase)
  5.     If Len(StrTxt) > 1 Then
  6.       AddressTxt = "FILE://" & StrText
  7. Debug.Print RanCell.Address, RanCell.Text
  8. Debug.Print AddressTxt
  9. Debug.Print StrTxt
  10.       Call .Hyperlinks.Add(Anchor:=RanCell, _
  11.                            Address:=AddressTxt, _
  12.                            TextToDisplay:=StrTxt)
  13.     End If
  14.   Next HyperCount
  15. End With
Aug 28 '08 #11

P: 6
Using your code during the Access export:

Expand|Select|Wrap|Line Numbers
  1.     with owb.activesheet
  2. For HyperCount = 2 To .UsedRange.Rows.Count
  3.         Set RanCell = .Cells(HyperCount, 16)
  4.         StrTxt = StrConv(RanCell, vbProperCase)
  5.         If Len(StrTxt) > 1 Then
  6.             AddressTxt = "FILE://" & StrTxt
  7.             Debug.Print RanCell.Address, RanCell.Text
  8.             Debug.Print AddressTxt
  9.             Debug.Print StrTxt
  10.             .Hyperlinks.Add AnChoR:=RanCell, _
  11.                                 Address:=AddressTxt, _
  12.                                 TextToDisplay:=StrTxt
  13.         End If
  14.     Next HyperCount
  15. end with
I got the following:

Expand|Select|Wrap|Line Numbers
  1. $P$2          G:\path\file.pdf
  2. FILE://G:\path\file.pdf
  3. G:\path\file.pdf
The hyperlinks did not link.

I used the following code in the exported excel sheet:

Expand|Select|Wrap|Line Numbers
  1. Dim hypercount As Long
  2. Dim rancell As Excel.Range
  3. Dim StrTxt As String
  4. Dim AddressTxt As String
  6. With ActiveSheet
  8.   For hypercount = 2 To .UsedRange.Rows.Count
  9.     Set rancell = .Cells(hypercount, 16)
  10.     StrTxt = StrConv(rancell, vbProperCase)
  11.     If Len(StrTxt) > 1 Then
  12.       AddressTxt = "FILE://" & StrText
  13. Debug.Print rancell.Address, rancell.Text
  14. Debug.Print AddressTxt
  15. Debug.Print StrTxt
  16.       Call .Hyperlinks.Add(Anchor:=rancell, _
  17.                            Address:=AddressTxt, _
  18.                            TextToDisplay:=StrTxt)
  19.     End If
  20.   Next hypercount
  21. End With
And, interestingly, there was no path after "FILE://":

Expand|Select|Wrap|Line Numbers
  1. $P$2          G:\path\file.pdf
  2. FILE://
  3. G:\path\file.pdf
However, the hyperlink DID work. In the working links, after right-clicking on any one of them and selecting 'Edit Hyperlink', even though the link works, the address line is blank. What the hell...
Aug 28 '08 #12

Expert Mod 15k+
P: 31,661
That's because in the first one you typed in my code and in the second you copy / pasted it in.

There is a bug in my code at line #6 where it uses the variable name StrText instead of StrTxt. It's always VERY important to use Copy / Paste. In this case it was important to copy the error in order that the whole makes sense. Strange I know.

Possibly try it again with all the same code (in this case clearly fix this bug first).
Aug 28 '08 #13

Post your reply

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