Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a VBA Range object from Excel HYPERLINK workbook function
I am trying to create a VBA macro that will chart some data points,
both measured data and limit data. The measured data is listed in a range on another worksheet of the same workbook that is hyperlinked using the workbook function 'HYPERLINK', and I need to retrieve the contents of the hyperlink (which contains full path and filename information) as a range object to pass on the relevant columns to the chart, but I have not found an elegant way to do this. In a message from 1999, <news:en#jj3ni#GA.263@cppssbbsa03, Chip Pearson wrote that: [...] if you are using the =HYPERLINK() worksheet function. In that case, use TheAddress = Mid(Range("A1").Formula, 13, InStr(1, Range("A1").Formula, ",") - 14) to get the address of the hyperlink. <http://groups.google.com/group/microsoft.public.excel.programming/msg/6fec646182dd90a4 I am, however, hoping that there is a more elegant solution available than extracting the range information from the hyperlink in this way, and I hope that you will be able to point me in the right direction. -- Troels Forchhammer TP -- Verification Services The idea of being *paid* to govern is terribly middle-class :-) - Igenlode on AFH-P |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a VBA Range object from Excel HYPERLINK workbook function
On May 10, 1:46 pm, Troels Forchhammer
wrote: I am trying to create a VBA macro that will chart some data points, both measured data and limit data. The measured data is listed in a range on another worksheet of the same workbook that is hyperlinked using the workbook function 'HYPERLINK', and I need to retrieve the contents of the hyperlink (which contains full path and filename information) as a range object to pass on the relevant columns to the chart, but I have not found an elegant way to do this. In a message from 1999, <news:en#jj3ni#GA.263@cppssbbsa03, Chip Pearson wrote that: [...] if you are using the =HYPERLINK() worksheet function. In that case, use TheAddress = Mid(Range("A1").Formula, 13, InStr(1, Range("A1").Formula, ",") - 14) to get the address of the hyperlink. <http://groups.google.com/group/microsoft.public.excel.programming/msg... I am, however, hoping that there is a more elegant solution available than extracting the range information from the hyperlink in this way, and I hope that you will be able to point me in the right direction. -- Troels Forchhammer TP -- Verification Services The idea of being *paid* to govern is terribly middle-class :-) - Igenlode on AFH-P Hello Troels, The easiest way in VBA is to use the SubAddress property of the Hyperlink. This property returns the Worksheet name and Range in this format as a string: "Sheet!A1:A100". Hyperlinks can only be referenced by their index number in the collection, which is 1 based, or by using the displayed text. For example, you have one Hyperlink on the "Sheet 1" which displays "Data Group 2". You can get the worksheet and range by either method: RangeAdress = ActiveSheet.Hyperlinks(1).SubAddress or RangeAddress = ActiveSheet.Hyperlinks("Data Group 2").SubAddress Sincerely, Leith Ross |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook Cell as Target of Hyperlink Object | Excel Programming | |||
Intra-workbook hyperlink: macro/function to return to hyperlink ce | Excel Discussion (Misc queries) | |||
Creating a Range object in .Net | Excel Programming | |||
creating a hyperlink in a shared workbook | Excel Programming |