PDA

View Full Version : keyboard shortcut to copy the url of a cell



Aquinax
07-02-2014, 04:42 PM
Basically, I'd like to know whether keyboard shortcut exists which would copy the value of the hyperlink formula of a cell to cache (which then I can insert into browser). Since Excel first makes a check of the hyperlink apparently against a "Microsoft Office Protocol Discovery" and since my urls require some authorization information, the data passed to the browser differs from the original hyperlink.. And hence I'm asking whether a described keys combination exists to copy the hyperlink of a cell. Of course if you know how to make my urls be passed to browser unaltered, you'd do me a very great favour by sharing that knowledge ..

Zack Barresse
07-04-2014, 06:37 PM
Why don't you right-click the cell and select "Open Hyperlink", which should be located toward the bottom of the pop-up menu? The code for this is simply...

YourCellHere.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

'example to work with the active cell...
ActiveCell.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

snb
07-05-2014, 08:32 AM
Are you famiiar with Webqueries in Excel ?

Simon Lloyd
07-05-2014, 10:56 AM
All your Keyboard shortcust can be found here http://www.thecodecage.com/blog/2009/06/excel-keyboard-shortcuts/

Paul_Hossler
07-05-2014, 11:15 AM
you can play around with these: One puts the Excel hyperlink address in to clipboard after some customization, the other just 'follows' the link after some customization

The HL in the cell is just www.Google.com (http://www.Google.com) but I added "/webhp?complete=0&hl=en" to it before putting in clipboard or launching

I just used the OnKey technique to assign macro launching keys




Option Explicit

'uses code 'borrowed from' www.cpearson.com/Excel/Clipboard.aspx (http://www.cpearson.com/Excel/Clipboard.aspx)

Sub CaptureOn()
Application.OnKey "+^%c", "URL2Clipboard"
Application.OnKey "+^%l", "URL2Launch"
End Sub


Sub CaptureOff()
Application.OnKey "+^%c"
Application.OnKey "+^%l"
End Sub


Sub URL2Clipboard()
Dim rCell As Range
Dim sURL As String


If Not TypeOf Selection Is Range Then Exit Sub

Set rCell = Selection.Cells(1, 1)
With rCell
If .Hyperlinks.Count = 0 Then Exit Sub

'https://www.google.com
sURL = .Hyperlinks(1).Address

'add some stuff
sURL = sURL & "/webhp?complete=0&hl=en"

If Not PutInClipboard(sURL) Then Exit Sub

'just to see if it really put it on the clipboard
rCell.Offset(2, 2).Select
ActiveSheet.Paste
End With
End Sub


Sub URL2Launch()
Dim rCell As Range
Dim sURL As String


If Not TypeOf Selection Is Range Then Exit Sub

Set rCell = Selection.Cells(1, 1)
With rCell
If .Hyperlinks.Count = 0 Then Exit Sub

'https://www.google.com
sURL = .Hyperlinks(1).Address

'add some stuff
sURL = sURL & "/webhp?complete=0&hl=en"

'launch
ThisWorkbook.FollowHyperlink Address:=sURL

End With
End Sub