Email Hyperlink Question

[UniMord]

I have a spreadsheet residing on a network drive that is updated regularly.
Rather than emailing copies of it to co-workers, I email them (from Outlook) a link to it, so it can never accidentally get forwarded to an outside party. (This also saves space on the server and prevents people from consulting outdated data.)

So far, so good. However, I usually want the recipient to open the spreadsheet to a specific cell.

I'd like to enter something like:

X:\Company Secrets\High Voltage\Keep out\Data Our Competitors Would Die to Get Their Claws On.xlsx#'March 2011'!Z100

The spreadsheet will open, but my reference to 'March 2011!Z100 in the hyperlink just gets ignored.

If there is no simple way of doing this, is there perhaps a way for VBA to determine that the spreadsheet was opened via a hyperlink and extract the range information from the hyperlink opening it (in which case, it'd be a skip and a jump to open to the desired spot)?

 

[DippNCope]

Works for me when I tried, only difference between my URL and yours is the '

mine looks like this X:\Company Secrets\High Voltage\Keep out\Data Our Competitors Would Die to Get Their Claws On.xlsx#March 2011!Z100

 

[UniMord]

If it works for you and not for me, can it have something to do with the Excel version or Operating System? I'm running Excel 2010 and Windows 7 Pro.

It also seems that when I link to .xlsx files with a reference, I get an error, and with .xls files, the spreadsheet opens, but not to the reference inside the workbook.

 

[UniMord]

SOLVED! Email Hyperlink Question

Eureka!

I've been working on this, on and off, and I've finally gotten to the answer.

There are 2 issues involved. In order to select the range referenced in my hyperlink, I need to include it as a command line argument.

For example, "U:\Excel\Excel Testing\Link2SpecificCell.xlsm" /e/Sheet2!$A1:$G$1
(Those double quotes are important if there are spaces in your path.)

It is important to have the /Range follow immediately after the /e, with no spaces for this to work.

Now for the code. I started with the code shown in http://www.vbforums.com/showthread.php?p=2234990, but the Workbook_Open function needed modification to make it usable.

In a Standard Module, enter:

Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineW" () As Long
Declare Function lstrlenW Lib "kernel32" (ByVal lpString As Long) As Long
Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (MyDest As Any, MySource As Any, ByVal MySize As Long)
Function CmdToSTr(Cmd As Long) As String
Dim Buffer() As Byte
Dim StrLen As Long
 
   If Cmd Then
      StrLen = lstrlenW(Cmd) * 2
      If StrLen Then
         ReDim Buffer(0 To (StrLen - 1)) As Byte
         CopyMemory Buffer(0), ByVal Cmd, StrLen
         CmdToSTr = Buffer
      End If
   End If
End Function

Then, in the Workbook module, enter:

Private Sub Workbook_Open()
    Dim CmdRaw As Long
    Dim CmdLine As String
    Dim args
 
    CmdRaw = GetCommandLine
    CmdLine = CmdToSTr(CmdRaw)
    On Error GoTo noArgs
    args = Split(Split(CmdLine, "/e/")(1), "!")
    Sheets(args(0)).Activate
    Range(args(1)).Select
noArgs:
End Sub

This does the trick if you just want a Windows shortcut. However, you can't enter hyperlinks with arguments in Outlook. I found the solution to that at: http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Q_23546832.html.

The trick is to make a batch file and instead of a link to the document, you enter a link to the batch file. So in my case, I included in my email the following link: U:\Excel\Excel Testing\Link2SpecificCell.cmd.

Then, in that file, I entered:

@echo off
call excel "U:\Excel\Excel Testing\Link2SpecificCell.xlsm" /e/Sheet2!$A1:$G$1

This now allows me to email links to people in my company, and have it open up to the specified range.

This entailed a LOT of research and testing. I will be very gratified if this will be of help to anyone else who might need this.

 

[UniMord]

BrettDJ offers a WSH/VBS solution on a parallel thread. Aside from its simplicity, it has several advantages over the VBA/CMD solution I presented above.
See http://www.mrexcel.com/forum/showpost.php?p=2825256 for details.