dcsimg
Results 1 to 14 of 14

Thread: Insert text into cell as a hyperlink

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Posts
    276

    Insert text into cell as a hyperlink

    Hello:

    How can I get this to insert to excel as a hyperlink?

    Code:
                .Cells(row + 1, 10) = txtSearchFolder.Text
    My dilemma is that using Hyperlinks.Add requires an absolute position to use.
    https://docs.microsoft.com/en-us/off...hyperlinks.add

    Thanks!

  2. #2
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,356

    Re: Insert text into cell as a hyperlink

    Possibly useful link:

    http://www.vbaexpress.com/forum/show...Hyperlinks-Add

    Code:
    Code:
    Option Explicit
    
    
    Sub Test()
    
        Dim intRow As Integer, intColumn As Integer
        Dim strHyperLinkText As String, strHyperLinkAddress As String, strHyperLinkTip As String
        
        intRow = 0
        intColumn = 0
        strHyperLinkText = "Google"
        strHyperLinkAddress = "https://www.google.com"
        strHyperLinkTip = "Google Search Page"
        
        Call ActiveSheet.Hyperlinks.Add(ActiveSheet.Cells(intRow + 1, intColumn + 1), strHyperLinkAddress, "", strHyperLinkTip, strHyperLinkText)
    
    End Sub

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Posts
    276

    Re: Insert text into cell as a hyperlink

    This is great, thanks!

    Just curious though, and at this point this is not really a programming question.

    Is there a character here preventing this from opening?

    Name:  Screen Shot 06-12-19 at 11.34 AM.jpg
Views: 81
Size:  10.8 KB

  4. #4
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,356

    Re: Insert text into cell as a hyperlink

    What happens if you click on the link? Error message, page not found, etc.? What happens if you copy and paste the url into the address box of your web browser?

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Posts
    276

    Re: Insert text into cell as a hyperlink

    Sorry for the incomplete response.

    Cannot open the specified file.

    This is a windows forms app, all files on the server, no web.

    Display is O:\...
    Hyperlink is its url equivalent: \\ajs-dc.sackett.com\FileExchange\

    When I enter it in File Explorer, it does nothing.

    Thanks!
    Last edited by ssabc; Jun 12th, 2019 at 12:03 PM.

  6. #6
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,356

    Re: Insert text into cell as a hyperlink

    I tested a file hyperlink and it worked for me. However, I did not have any spaces in my file name or link.

    Code:
    Code:
    Option Explicit
    
    
    Sub Test()
    
        Dim intRow As Integer, intColumn As Integer
        Dim strHyperLinkText As String, strHyperLinkAddress As String, strHyperLinkTip As String
        
        intRow = 0
        intColumn = 0
        strHyperLinkText = "Google"
        strHyperLinkAddress = "https://www.google.com"
        strHyperLinkTip = "Google Search Page"
    
        Call ActiveSheet.Hyperlinks.Add(ActiveSheet.Cells(intRow + 1, intColumn + 1), strHyperLinkAddress, "", strHyperLinkTip, strHyperLinkText)
        
        intRow = 1
        intColumn = 0
        strHyperLinkText = "04-04-19.txt"
        strHyperLinkAddress = "C:\Temp\04-04-19.txt"
        strHyperLinkTip = "Test Text File"
        
        Call ActiveSheet.Hyperlinks.Add(ActiveSheet.Cells(intRow + 1, intColumn + 1), strHyperLinkAddress, "", strHyperLinkTip, strHyperLinkText)
    
    End Sub
    If you are using an internal network file link, it should be something like:
    \\ServerName\FolderName\SubFolderName\FileName.ext

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Posts
    276

    Re: Insert text into cell as a hyperlink

    I believe the syntax is correct. If I do this manually, it also does not work.

    But thanks for your help, because according to my calculations it should be working!

  8. #8
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,356

    Re: Insert text into cell as a hyperlink

    In post #3, that is looking like a file created from an Outlook e-mail message. Does it open OK when you actually navigate to that folder and double click on the file?

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Posts
    276

    Re: Insert text into cell as a hyperlink

    Yes it does

  10. #10
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,356

    Re: Insert text into cell as a hyperlink

    If you have spaces or other characters that do not work in a hyperlink, you will need to change the hyperlink to encode those.

    Links:

    https://www.mrexcel.com/forum/excel-...th-spaces.html

    http://www.tushar-mehta.com/publish_...ages_services/
    (Search for Escape at this page to see the code.)

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Posts
    276

    Re: Insert text into cell as a hyperlink

    Hello:

    No difference with this. I think it may have more to do with the length of the hyperlink or something. When I load it manually, I get the same result. Other links work fine with spaces.

    Attachment 169179

    Thanks!

  12. #12
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,356

    Re: Insert text into cell as a hyperlink

    When you save an Outlook message, the file name is usually the message subject. If you have control of the saving process, a better save method would be to save it with a shorter and more useful (for your purposes) name, like MsgRecipientNameYearMonthDayHourMinSec.msg

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,245

    Re: Insert text into cell as a hyperlink

    can you shell or shellexecute the url?

    can you create a windows shortcut that will open the message?
    if so you could make a hyperlink to the shortcut

    if it works you could create shortcuts by code
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  14. #14

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Posts
    276

    Re: Insert text into cell as a hyperlink

    Shortening up the filename did the trick, Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width