Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference cell address of Hyperlink
As I loop thru Hyperlinks, I need a reference to the active Hyperlink so I
can put a value in a cell Offset(0,7). See the first line following the query Option Explicit Sub UpdateUsage() Dim hyp As Hyperlink Dim hypadr As String For Each hyp In ActiveSheet.Range("K:K").Hyperlinks hypadr = hyp.Address With Worksheets("Update").QueryTables.Add(Connection:=" URL;" & hypadr _ , Destination:=Range("A1")) .name = "" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlAllTables .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .Refresh BackgroundQuery:=False End With ActiveSheet.CurrentHyperlink.Offset(0, 7).Value = _ Worksheets("Update").Range("C7").Value ==this is not right With Worksheets("Update") 'do some other stuff .Rows("1:7").Delete shift:=xlUp End With Next hyp End Sub Mike F |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference cell address of Hyperlink
Mike,
hypadr = hyp.Range.Address ActiveSheet.Range(hypadr).Offset(0, 7).Value =... ought to do it. Regards, Jim Cone San Francisco, USA "Mike Fogleman" wrote in message ... As I loop thru Hyperlinks, I need a reference to the active Hyperlink so I can put a value in a cell Offset(0,7). See the first line following the query Option Explicit Sub UpdateUsage() Dim hyp As Hyperlink Dim hypadr As String For Each hyp In ActiveSheet.Range("K:K").Hyperlinks hypadr = hyp.Address With Worksheets("Update").QueryTables.Add(Connection:=" URL;" & hypadr _ , Destination:=Range("A1")) .name = "" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlAllTables .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .Refresh BackgroundQuery:=False End With ActiveSheet.CurrentHyperlink.Offset(0, 7).Value = _ Worksheets("Update").Range("C7").Value ==this is not right With Worksheets("Update") 'do some other stuff .Rows("1:7").Delete shift:=xlUp End With Next hyp End Sub Mike F |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference cell address of Hyperlink
Not quite Jim, but I got it....
Sub UpdateUsage() Dim hyp As Hyperlink Dim hypadr As String Dim hyprng As Range For Each hyp In ActiveSheet.Range("K:K").Hyperlinks hypadr = hyp.Address Set hyprng = hyp.Range Mike F "Jim Cone" wrote in message ... Mike, hypadr = hyp.Range.Address ActiveSheet.Range(hypadr).Offset(0, 7).Value =... ought to do it. Regards, Jim Cone San Francisco, USA "Mike Fogleman" wrote in message ... As I loop thru Hyperlinks, I need a reference to the active Hyperlink so I can put a value in a cell Offset(0,7). See the first line following the query Option Explicit Sub UpdateUsage() Dim hyp As Hyperlink Dim hypadr As String For Each hyp In ActiveSheet.Range("K:K").Hyperlinks hypadr = hyp.Address With Worksheets("Update").QueryTables.Add(Connection:=" URL;" & hypadr _ , Destination:=Range("A1")) .name = "" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlAllTables .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .Refresh BackgroundQuery:=False End With ActiveSheet.CurrentHyperlink.Offset(0, 7).Value = _ Worksheets("Update").Range("C7").Value ==this is not right With Worksheets("Update") 'do some other stuff .Rows("1:7").Delete shift:=xlUp End With Next hyp End Sub Mike F |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference cell address of Hyperlink
Mike,
Are you sure about... hypadr = hyp.Address ? Jim Cone "Mike Fogleman" wrote in message ... Not quite Jim, but I got it.... Sub UpdateUsage() Dim hyp As Hyperlink Dim hypadr As String Dim hyprng As Range For Each hyp In ActiveSheet.Range("K:K").Hyperlinks hypadr = hyp.Address Set hyprng = hyp.Range Mike F "Jim Cone" wrote in message ... Mike, hypadr = hyp.Range.Address ActiveSheet.Range(hypadr).Offset(0, 7).Value =... ought to do it. Regards, Jim Cone San Francisco, USA |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference cell address of Hyperlink
Yes, I am now. here is the working code:
Sub UpdateUsage() Dim hyp As Hyperlink Dim hypadr As String Dim hyprng As Range Dim mystr As String Dim myval As String Dim i As Long For Each hyp In ActiveSheet.Range("K:K").Hyperlinks hypadr = hyp.Address Set hyprng = hyp.Range With Worksheets("Update").QueryTables.Add(Connection:=" URL;" & hypadr _ , Destination:=Worksheets("Update").Range("A1")) .name = "" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlAllTables .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .Refresh BackgroundQuery:=False End With mystr = Worksheets("Update").Range("A3").Value i = Application.WorksheetFunction.Find(" ", mystr, 5) i = i - 5 myval = Mid(mystr, 5, i) hyprng.Offset(0, 7).Value = myval With Worksheets("Update") .Rows("1:7").Delete Shift:=xlUp End With Next hyp End Sub Mike F "Jim Cone" wrote in message ... Mike, Are you sure about... hypadr = hyp.Address ? Jim Cone "Mike Fogleman" wrote in message ... Not quite Jim, but I got it.... Sub UpdateUsage() Dim hyp As Hyperlink Dim hypadr As String Dim hyprng As Range For Each hyp In ActiveSheet.Range("K:K").Hyperlinks hypadr = hyp.Address Set hyprng = hyp.Range Mike F "Jim Cone" wrote in message ... Mike, hypadr = hyp.Range.Address ActiveSheet.Range(hypadr).Offset(0, 7).Value =... ought to do it. Regards, Jim Cone San Francisco, USA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Coverting hyperlink cell to the web page address | Excel Discussion (Misc queries) | |||
How to extract a web address from a hyperlink cell? | Excel Discussion (Misc queries) | |||
Keeping the hyperlink address hidden in a cell. | Excel Worksheet Functions | |||
How make hyperlink refer to cell content rather than cell address. | Excel Discussion (Misc queries) | |||
how do I add a 'web address' to a cell in excel with a hyperlink? | New Users to Excel |