| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to extract "destination address" from Hyperlink - VBA
Hi,
I have following function: Function HL_SheetName(komorka As Range) As String If komorka.Hyperlinks.Count = 0 Then Exit Function End If Dim poz As Integer HL_SheetName = komorka.Hyperlinks(1).SubAddress poz = InStr(1, HL_SheetName, "!") poz = poz - 1 HL_SheetName = Mid(HL_SheetName, 1, poz) poz = InStr(1, HL_SheetName, "'") If poz = 1 Then poz = Len(HL_SheetName) poz = poz - 2 HL_SheetName = Mid(HL_SheetName, 2, poz) End If End Function This function returns SubAddress from hyperlink ( actually name of the Sheet ). It works, but sometimes I get wrong sheet name, and hyperlink works well. For example: Cell A1 have hyperlink to Sheet2!A1. I enter the formula in B1: HL_SheetName(A1) , and I get in B1: Sheet2. Cell A2 have hyperlink to Sheet3!A1. I enter the formula in B2: HL_SheetName(A2) , and I get in B2: Sheet2. WTF?? Both hyperlinks works fine. I don't understand. With the hyperlinks(1).TextToDisplay is the same situation... Where exactly is the destination address in the structure of hyperlink? ( SubAddress, Address , ..... ?? ) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to extract "destination address" from Hyperlink - VBA
I'm guessing you're in 2007. I not aware of HL_SheetName functionality in
2003. Or is this a UDF? Barb Reinhardt "spwmarluk" wrote: Hi, I have following function: Function HL_SheetName(komorka As Range) As String If komorka.Hyperlinks.Count = 0 Then Exit Function End If Dim poz As Integer HL_SheetName = komorka.Hyperlinks(1).SubAddress poz = InStr(1, HL_SheetName, "!") poz = poz - 1 HL_SheetName = Mid(HL_SheetName, 1, poz) poz = InStr(1, HL_SheetName, "'") If poz = 1 Then poz = Len(HL_SheetName) poz = poz - 2 HL_SheetName = Mid(HL_SheetName, 2, poz) End If End Function This function returns SubAddress from hyperlink ( actually name of the Sheet ). It works, but sometimes I get wrong sheet name, and hyperlink works well. For example: Cell A1 have hyperlink to Sheet2!A1. I enter the formula in B1: HL_SheetName(A1) , and I get in B1: Sheet2. Cell A2 have hyperlink to Sheet3!A1. I enter the formula in B2: HL_SheetName(A2) , and I get in B2: Sheet2. WTF?? Both hyperlinks works fine. I don't understand. With the hyperlinks(1).TextToDisplay is the same situation... Where exactly is the destination address in the structure of hyperlink? ( SubAddress, Address , ..... ?? ) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to extract "destination address" from Hyperlink - VBA
Ignore the previous response.
"spwmarluk" wrote: Hi, I have following function: Function HL_SheetName(komorka As Range) As String If komorka.Hyperlinks.Count = 0 Then Exit Function End If Dim poz As Integer HL_SheetName = komorka.Hyperlinks(1).SubAddress poz = InStr(1, HL_SheetName, "!") poz = poz - 1 HL_SheetName = Mid(HL_SheetName, 1, poz) poz = InStr(1, HL_SheetName, "'") If poz = 1 Then poz = Len(HL_SheetName) poz = poz - 2 HL_SheetName = Mid(HL_SheetName, 2, poz) End If End Function This function returns SubAddress from hyperlink ( actually name of the Sheet ). It works, but sometimes I get wrong sheet name, and hyperlink works well. For example: Cell A1 have hyperlink to Sheet2!A1. I enter the formula in B1: HL_SheetName(A1) , and I get in B1: Sheet2. Cell A2 have hyperlink to Sheet3!A1. I enter the formula in B2: HL_SheetName(A2) , and I get in B2: Sheet2. WTF?? Both hyperlinks works fine. I don't understand. With the hyperlinks(1).TextToDisplay is the same situation... Where exactly is the destination address in the structure of hyperlink? ( SubAddress, Address , ..... ?? ) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to extract "destination address" from Hyperlink - VBA
I duplicated your example, but was not able to duplicate the error.
The function exited immediately after hitting "If komorka.Hyperlinks.Count = 0 Then " and a check of the 'komorka' shows that it is equal to 100, not a range reference to A1. --JP On Oct 23, 12:13*pm, spwmarluk wrote: Hi, I have following function: Function HL_SheetName(komorka As Range) As String If komorka.Hyperlinks.Count = 0 Then * * * *Exit Function End If Dim poz As Integer HL_SheetName = komorka.Hyperlinks(1).SubAddress poz = InStr(1, HL_SheetName, "!") poz = poz - 1 HL_SheetName = Mid(HL_SheetName, 1, poz) poz = InStr(1, HL_SheetName, "'") If poz = 1 Then poz = Len(HL_SheetName) poz = poz - 2 HL_SheetName = Mid(HL_SheetName, 2, poz) End If End Function This function returns SubAddress from hyperlink ( actually name of the Sheet ). It works, but sometimes I get wrong sheet name, and hyperlink works well. For example: Cell A1 have hyperlink to Sheet2!A1. I enter the formula in B1: HL_SheetName(A1) , and I get in B1: Sheet2. Cell A2 have hyperlink to Sheet3!A1. I enter the formula in B2: HL_SheetName(A2) , and I get in B2: Sheet2. WTF?? Both hyperlinks works fine. I don't understand. With the hyperlinks(1).TextToDisplay is the same situation... Where exactly is the destination address in the structure of hyperlink? ( SubAddress, Address , ..... ?? ) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to extract "destination address" from Hyperlink - VBA
What I meant was "a check of the "komorka" variable shows that it is
equal to the value in cell A1, not a range reference to A1" --JP On Oct 23, 12:53*pm, JP wrote: I duplicated your example, but was not able to duplicate the error. The function exited immediately after hitting "If komorka.Hyperlinks.Count = 0 Then " and a check of the 'komorka' shows that it is equal to 100, not a range reference to A1. --JP On Oct 23, 12:13*pm, spwmarluk wrote: |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to extract "destination address" from Hyperlink - VBA
See example: http://rapidshare.com/files/157022208/example1.zip.html
I copied a piece of my XLS file with VBA functions. What is wrong with these hyperlinks?? On Oct 23, 6:58*pm, JP wrote: What I meant was "a check of the "komorka" variable shows that it is equal to the value in cell A1, not a range reference to A1" --JP On Oct 23, 12:53*pm, JP wrote: I duplicated your example, but was not able to duplicate the error. The function exited immediately after hitting "If komorka.Hyperlinks.Count = 0 Then " and a check of the 'komorka' shows that it is equal to 100, not a range reference to A1. --JP On Oct 23, 12:13*pm, spwmarluk wrote: |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to extract "destination address" from Hyperlink - VBA
On Oct 24, 9:27*am, spwmarluk wrote:
See example:http://rapidshare.com/files/157022208/example1.zip.html I copied a piece of my XLS file with VBA functions. What is wrong with these hyperlinks?? On Oct 23, 6:58*pm, JP wrote: What I meant was "a check of the "komorka" variable shows that it is equal to the value in cell A1, not a range reference to A1" --JP On Oct 23, 12:53*pm, JP wrote: I duplicated your example, but was not able to duplicate the error. The function exited immediately after hitting "If komorka.Hyperlinks.Count = 0 Then " and a check of the 'komorka' shows that it is equal to 100, not a range reference to A1. --JP On Oct 23, 12:13*pm, spwmarluk wrote: PS. I have Excel 2002 SP3 (from MS Office XP PRO). |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to extract "destination address" from Hyperlink - VBA
OK, I think I figured out what I was doing wrong. I was creating cell
references, not hyperlinks, that's why the code kept exiting. I created a workbook with three worksheets: Sheet1, Sheet2, Sheet3. I placed your code in a standard module in that workbook. In Sheet1!A1 I created a hyperlink to Sheet2!A1 (InsertHyperlink). In Sheet1!A2 I created a hyperlink to Sheet3!A1. When I used your UDF, cells B1 and B2 correctly displayed "Sheet2" and "Sheet3" respectively. I duplicated your workbook and got the correct results. Not sure why it doesn't work for you. I am using Excel 2003, not sure if that makes a difference. I can send you a copy of the workbook if you'd like. --JP On Oct 24, 3:27*am, spwmarluk wrote: See example:http://rapidshare.com/files/157022208/example1.zip.html I copied a piece of my XLS file with VBA functions. What is wrong with these hyperlinks?? On Oct 23, 6:58*pm, JP wrote: What I meant was "a check of the "komorka" variable shows that it is equal to the value in cell A1, not a range reference to A1" --JP On Oct 23, 12:53*pm, JP wrote: I duplicated your example, but was not able to duplicate the error. The function exited immediately after hitting "If komorka.Hyperlinks.Count = 0 Then " and a check of the 'komorka' shows that it is equal to 100, not a range reference to A1. --JP On Oct 23, 12:13*pm, spwmarluk wrote:- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to extract "destination address" from Hyperlink - VBA
Ok, I solved the problem.
In some cells I had more than one hyperlink. Thanks. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to extract "destination address" from Hyperlink - VBA
Glad to hear it!
--JP On Oct 24, 9:26*am, spwmarluk wrote: Ok, I solved the problem. In some cells I had more than one hyperlink. Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| =HYPERLINK("#"&CELL("address", ADDRESS(MATCH(VALUE(B3),QuoteNotes, | Excel Worksheet Functions | |||
| =HYPERLINK("#"&CELL("address", ADDRESS(MATCH(VALUE(B3),Range ... ? | Excel Worksheet Functions | |||
| How do I create a macro to remove path from hyperlink "address"? | Excel Discussion (Misc queries) | |||
| Multiple "source" workbooks linked to single "destination" workboo | Excel Worksheet Functions | |||
| VBA code store address with areas separated with "," and I need it sometimes with ";" instead | Excel Programming | |||
Linear Mode
