File Path and Name for Links

Unlike many people with whom I work, I almost never email a workbook. Instead, I save it to an appropriate place on the server and email the link. This seems to confuse the hell out of some people, but if I’m going to change the way things are done, it has to start with me and I have to take a hard line. But that’s not the point of this post. The point is that I need to get the path to these workbooks I want to email. I’m sure many of you remember this gem of a post from 2006 about getting the UNC path from the Web toolbar. That technique found its way into this utility:

Sub GetUNCAddress()

Dim doClip As DataObject
Dim Wb As Workbook
Dim sText As String

Set doClip = New DataObject

'If there is no activeworkbook, you'll get the last open workbook.
'this makes sure that the last open workbook doesn't have a real
'path so you'll realize the error
Application.ScreenUpdating = False
Set Wb = Workbooks.Add
Wb.Close False
Application.ScreenUpdating = True

sText = Application.CommandBars("Web").Controls("&Address:").Text

doClip.SetText sText
doClip.PutInClipboard

Set doClip = Nothing

End Sub

That worked like a peach at my old job where mapped drives were the wild west. At my new job, it is guaranteed that everyone has certain mapped drives. In those cases, I don’t want to confuse them further by giving them a UNC path. So I send them the mapped drive path.

Sub GetMappedAddress()

Dim doClip As MSForms.DataObject

Set doClip = New MSForms.DataObject

doClip.SetText ActiveWorkbook.FullName
doClip.PutInClipboard

Set doClip = Nothing

End Sub

Works great most of the time. One of our mapped drive is considered an “internet location” by Excel. I really don’t know how are servers work around here, but I do know that when I open something from that particular mapped drive, I have to click the Enable Content banner at the top.

This is called Protected View and it’s a pain in the ass. When a workbook is in Protected View and is active, the ActiveWorkbook object is Nothing. The above code fails. The good news is that Application.ActiveProtectedViewWindow is Nothing if that’s not the case.

Sub GetMappedAddress()

Dim doClip As MSForms.DataObject

Set doClip = New MSForms.DataObject

If Not Application.ActiveProtectedViewWindow Is Nothing Then
doClip.SetText Application.ActiveProtectedViewWindow.Workbook.FullName
doClip.PutInClipboard
Else
doClip.SetText ActiveWorkbook.FullName
doClip.PutInClipboard
End If

Set doClip = Nothing

End Sub

Do you want to know what else doesn’t work when Application.ActiveProtectedView is not Nothing? Application.Wait. That’s right, the Wait method fails with Method 'Wait' of object'_Application' failed. That’s just super. Anybody know what else fails in Protected View?

6 thoughts on “File Path and Name for Links

  1. or

    Sub M_snb()
    With ThisWorkbook.Sheets(1)
    .Hyperlinks.Add .Cells(1, 200), ThisWorkbook.FullName
    MsgBox .Hyperlinks(.Hyperlinks.Count).Address
    .Cells(1, 200).ClearContents
    End With
    End Sub

  2. When you put it in the clipboard, it gets converted to UNC notation then ?
    I use FileSystemObject instead.

  3. is there a method to convert a known mapped drive path to UNC with Outlook.
    In my company users will, from Outlook, insert a hyperlink to a file, which shows in the email message as DriveLetter:\\folder\folder\filename
    insted of
    \\s1111386\folder\folder\filename

    So ideally they would add a button to Outlook bar to convert the hyperlink–this would then work for non-excel file types.

    anyone have a code to suggest?

    Dave

  4. Thanks for this! I’ve always used the mapped address, but didn’t know the trick for the UNC. That will be useful.

    I link these macros to buttons on the quick access toolbar. This allows me to get the address in the clipboard with one button click or keyboard shortcut, and then paste it into an email. I also use it for pasting a link into an email when possible (I agree with your theory), but not everyone is on the same servers at my company. Most of the time I use this to be able to quickly paste the file address in the file browser window for an attachment. This works for any email application or web based email, not just outlook.

    I would consider this a really small tool that is a huge time saver over the long haul.

  5. Where I work, different people can have different file shares mapped with the same letter (My “V:\” drive might not be the same as yours), which is why I don’t use or try to get the drive letter via VBA. I just add the Document Location button to the Quick Access Toolbar. With that, when you have the file open, one click on the location in that box on the QAT, CTRL+C to copy, paste as hyperlink in email. Done.

Leave a Reply

Your email address will not be published. Required fields are marked *