Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlink Address and SubAddress not concatenating correctly
2003
The code below ******** does not generate the correct hyperlink information. The actual "Address" is: \\OPC01\VOL1\PROD\PRIVATE\SARBANES\Loan Loss Reserve 20\Lender Liability Reserve 20d\2006\Inventory_Reports_Spreadsheets\Review SOX 1st Stage 20d The actual "SubAddress" (which is a filename) is: 20d contingent liability spreadsheet 8-15-2006.xls If I do a Ctrl-K in i.e. cell AA9 after the code above and look at the "Edit" address I see: \\OPC01\VOL1\PROD\PRIVATE\SARBANES\Loan Loss Reserve 20\Lender Liability Reserve 20d\2006\Inventory_Reports_Spreadsheets\Review SOX 1st Stage 20d#20d contingent liability spreadsheet 8-15-2006.xls NOTE: The "#" between the Address and SubAddress (this # causes the Hyperlink to fail) If I attempt the insert a "\" I get either: #\ or \# depending where I attempt to concatenate it Sub Link() ' ' Code to generate a Hyperlink from information on the Activesheet ' Dim MyCounter As Long MyCounter = Selection.Cells.Row - 1 ActiveSheet.Range("AA:AA").Hyperlinks.Delete For MyCounter = MyCounter + 1 To Cells.Rows.Count With ActiveSheet If UCase(.Cells(MyCounter, "H").Text) = "YES" Then .Cells(MyCounter, "AA").Hyperlinks.Add Anchor:= _ .Cells(MyCounter, "AA"), Address:=.Cells(MyCounter, "A").Value _ SubAddress:=.Cells(MyCounter, "F").Value, TextToDisplay:="L" ' SubAddress:="\" & .Cells(MyCounter, "F").Value, _ ' TextToDisplay:="L" Else Exit For End If End With Next MyCounter End Sub If I manually create the link in cell AA9, I get: \\Opc01\VOL1\PROD\PRIVATE\SARBANES\Loan Loss Reserve 20\Lender Liability Reserve 20d\2006\Inventory_Reports_Spreadsheets\Review SOX 1st Stage 20d\20d contingent liability spreadsheet 8-15-2006.xls Therefore, using VBA I get a "#" inserted after the Address which causes the link to fail. 1) Why does this happen? (String limit?) 2) How to stop or workaround so that I can generate valid Hyperlinks with VBA? Thanks EagleOne |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|