Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink creation problem
Not really an excel guy so I need help. I have a dataset that is
imported to excel. One of the columns contains a hyperlink to an external pdf file. When the spreadsheet is first opened the column isn't recognized as a hyperlink. However if I click into it to edit it then exit (without making any changes) the hyperlink becomes active for as long as I have the workbook open. When the workbook is saved, closed and reopened the hyperlink appears to be valid. However when you click on it you get a 404 error. until you F2 and exit the field. At which point it works. To say I'm completely stumped is an understatement. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink creation problem
Try this code to repair the hyperlinks. You didn't say what range they are
in so adjust the code to reflect your range. Sub FixHyperlinks() Dim Lrow As Long Dim rng As Range Dim c As Range 'change the B in the next 2 lines to your column Lrow = Cells(Rows.Count, "B").End(xlUp).Row Set rng = Range("B1:B" & Lrow) For Each c In rng If c.Hyperlinks.Count = 0 And Not c.Value = "" Then ActiveSheet.Hyperlinks.Add Anchor:=c, Address:=c.Text End If Next c End Sub Mike F wrote in message oups.com... Not really an excel guy so I need help. I have a dataset that is imported to excel. One of the columns contains a hyperlink to an external pdf file. When the spreadsheet is first opened the column isn't recognized as a hyperlink. However if I click into it to edit it then exit (without making any changes) the hyperlink becomes active for as long as I have the workbook open. When the workbook is saved, closed and reopened the hyperlink appears to be valid. However when you click on it you get a 404 error. until you F2 and exit the field. At which point it works. To say I'm completely stumped is an understatement. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink creation problem
Works perfectly the first time. It doesn't work on re-entry. Still get
a 404 error. But this is still good enough. I can make the spreadsheet readonly and run this on open every time. Thanks very much for the help. On Oct 17, 3:46 am, "Mike Fogleman" wrote: Try this code to repair the hyperlinks. You didn't say what range they are in so adjust the code to reflect your range. Sub FixHyperlinks() Dim Lrow As Long Dim rng As Range Dim c As Range 'change the B in the next 2 lines to your column Lrow = Cells(Rows.Count, "B").End(xlUp).Row Set rng = Range("B1:B" & Lrow) For Each c In rng If c.Hyperlinks.Count = 0 And Not c.Value = "" Then ActiveSheet.Hyperlinks.Add Anchor:=c, Address:=c.Text End If Next c End Sub Mike wrote in message oups.com... - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
entering emails without hyperlink creation | Excel Discussion (Misc queries) | |||
Problem with function creation | Excel Programming | |||
Automating hyperlink creation | Excel Worksheet Functions | |||
entering emails without hyperlink creation | Excel Discussion (Misc queries) | |||
Hyperlink Creation in Excel X for Mac | Excel Worksheet Functions |