-
May 23rd, 2017, 10:06 AM
#1
Thread Starter
Fanatic Member
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!
-
May 23rd, 2017, 10:13 AM
#2
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
-
May 23rd, 2017, 10:23 AM
#3
Re: Convert folder name and file location to a clickable link in excel
-
May 23rd, 2017, 10:35 AM
#4
Thread Starter
Fanatic Member
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...
-
May 23rd, 2017, 01:04 PM
#5
Thread Starter
Fanatic Member
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.
-
May 23rd, 2017, 02:04 PM
#6
Re: Convert folder name and file location to a clickable link in excel
-
May 23rd, 2017, 03:49 PM
#7
Re: Convert folder name and file location to a clickable link in excel
Originally Posted by ssabc
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
-
Jun 2nd, 2017, 11:51 AM
#8
Thread Starter
Fanatic Member
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
-
Jun 2nd, 2017, 05:05 PM
#9
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
-
Jun 5th, 2017, 07:54 AM
#10
Thread Starter
Fanatic Member
Re: Convert folder name and file location to a clickable link in excel
Attached represents the current output.
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!
-
Jun 5th, 2017, 12:35 PM
#11
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.
-
Jun 5th, 2017, 04:38 PM
#12
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|