Results 1 to 12 of 12

Thread: Convert folder name and file location to a clickable link in excel

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Convert folder name and file location to a clickable link in excel

    Hello:

    I have a link like this, just as text:

    C:\Vault\Sales\Projects\Adam\3D Printing customer call campaign.docx

    I would like this to be a local link that can be clicked on to open the file. I need to populate excel with the link, not the text.

    Thanks!

  2. #2
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Convert folder name and file location to a clickable link in excel

    Simply use the HYPERLINK formula

    Syntax

    =HYPERLINK(link location, friendly name)

    Code:
    =HYPERLINK("C:\Vault\Sales\Projects\Adam\3D Printing customer call campaign.docx","3D Printing customer call campaign")
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  3. #3
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Convert folder name and file location to a clickable link in excel


  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Re: Convert folder name and file location to a clickable link in excel

    So I am defining this in VB.NET to populate Excel using ADODB:
    Dim Hyperlink As String = "=HYPERLINK('" & File & "', " & "'" & File & "')"

    I get this in my excel field:
    =HYPERLINK('C:\Vault\Sales\Projects\Rick\Rick Off Subscription Recapture.xlsx', 'C:\Vault\Sales\Projects\Rick\Rick Off Subscription Recapture.xlsx'

    I am looking for a blue link to show up.

    Thanks...

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Re: Convert folder name and file location to a clickable link in excel

    Note, with regard to this question, I am not using the Excel API, but ADODB.

  6. #6
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Convert folder name and file location to a clickable link in excel


  7. #7
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Convert folder name and file location to a clickable link in excel

    Quote Originally Posted by ssabc View Post
    So I am defining this in VB.NET to populate Excel using ADODB:
    Dim Hyperlink As String = "=HYPERLINK('" & File & "', " & "'" & File & "')"

    I get this in my excel field:
    =HYPERLINK('C:\Vault\Sales\Projects\Rick\Rick Off Subscription Recapture.xlsx', 'C:\Vault\Sales\Projects\Rick\Rick Off Subscription Recapture.xlsx'

    I am looking for a blue link to show up.

    Thanks...
    ADO can be used strictly to transfer raw data to a workbook. You cannot use ADO to apply formats or formulas to cells. You may want to see this

    ExcelADO demonstrates how to use ADO to read and write data in Excel workbooks

    To insert formulas you can use Interop. If you are interested then please see THIS
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  8. #8

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Re: Convert folder name and file location to a clickable link in excel

    Hello:

    I am trying to resurrect this issue. How can I do a hybrid of ADODB (old school), which I got working, and Populate Values in Excel? I an trying to establish ranges, and select range F1 to be the hyperlinked value. F1 uses the Ei string path to define itself. I have tried =Hyperlink(F1), but that just shows me that, literally, not the actual value. It does work, it's just that I manually have to go through the whole spreadsheet and hit enter at the end of each line. Surely, there is quick and dirty way to accomplish this??

    Code Below, thanks for all the help on this!

    Code:
            If Trim(A) <> Trim(Salesperson) Or Trim(B) <> Trim(ProjName) Then
                cnt = cnt + 1
                rs.AddNew()
                rs.Fields.Item("Sales Rep").Value = Salesperson
                rs.Fields.Item("Project Name").Value = ProjName
                rs.Fields.Item("File").Value = File
    
                Console.WriteLine("E" & cnt.ToString)
                Dim rng As excel.Range = CType("E" & cnt.ToString, Object)
                Dim url As String = GetURL(rng)
    
                ' rs.Fields.Item("Link to Project").Value = "=Hyperlink(E" & (cnt.ToString) & ")"  ' THIS WORKS
                rs.Fields.Item("Link to Project").Value = url & ")"  ' THIS WILL NOT CAST TO AN EXCEL.RANGE
                rs.Update()
    
            End If

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Convert folder name and file location to a clickable link in excel

    Surely, there is quick and dirty way to accomplish this??
    you could combine excel automation with your use of a recordset, but as it is not possible to know the row number of the active record it would be a lot easier if you have the row number as a field data, or at least a unique field, so that it is easy to find the correct row to edit with excel automation, i doubt that the edited data would update in the recordset unless the recordset is refreshed or reopened, but it should all be able to work
    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

  10. #10

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Re: Convert folder name and file location to a clickable link in excel

    Attached represents the current output.
    Name:  2017-06-05_7-47-55.jpg
Views: 98
Size:  16.8 KB

    All I need is some code to convert column F to show the actual hyperlink. If I click in each cell and do this manually, the conversion occurs. I would prefer to do this in the code.

    This is the line that populates column F. Remmed out in my last post, but it does the trick to show the =Hyperlink(En)
    Code:
    rs.Fields.Item("Link to Project").Value = "=Hyperlink(E" & (cnt.ToString) & ")"
    If I need to go back and select the entire column F and somehow do a return n the formula to display the data, or get an excel row count and do it pone by one, this too would work. What would the syntax be?

    Thank you!

  11. #11
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Convert folder name and file location to a clickable link in excel

    Code:
    Sheets("SheetName").Cells(lngRow, 6) Sheets("SheeteName").Cells(lngRow, 6), CStr(Sheets("SheetName").Cells(lngRow, 6))
    Where lngRow = the row number. Use a loop to update all rows in column 6 that have data.

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Convert folder name and file location to a clickable link in excel

    you could try
    Code:
    for each c in range("f:f")
      if is empty(c) then exit for
      lnk = c.Formula
      shrt = Mid(s, InStrRev(lnk, "\") + 1)
      c.Clear
      c.Hyperlinks.Add c, Replace(Mid(lnk, 2), "'", ""), , , shrt
    next
    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

Posting Permissions

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



Click Here to Expand Forum to Full Width