Where is this Document? (All)

Topic

New Reply

Quite often because I’ve opened a document from a shortcut or as an attachment I’m not sure where the document is actually located. This little macro opens the folder with the document itself selected. Thanks to Hans for his contribution.

The same macro can work with Word, Excel and Powerpoint documents

Sub FindMe()
'
'Macro created 25_June_2006 by Owen Gwynne
'

Dim Here_I_am As String
Dim OK As Double
Dim What_I_am As String
Dim Mypath As String
Dim My_Name as String


What_I_am = Application.Name
If What_I_am = "Microsoft Word" Then
    Mypath = ActiveDocument.Path
    My_Name = ActiveDocument.Name
ElseIf What_I_am = "Microsoft Excel" Then
    Mypath = ActiveWorkbook.Path
    My_Name = ActiveWorkbook.Name
ElseIf What_I_am = "Microsoft PowerPoint" Then
    Mypath = ActivePresentation.Path
    My_Name = ActivePresentation.Name
End If

If Mypath = "" Then
    OK = MsgBox("File Not Saved, no current folder defined", vbOKOnly)
Else
    Here_I_am = "explorer /n, """ & Mypath
    Here_I_am = Here_I_am & """ , /select,"""
    Here_I_am = Here_I_am & Mypath & "" & My_Name & """"
    OK = Shell(Here_I_am, vbNormalFocus)

End If

End Sub

Reply | Quote

Replies

Edited by HansV to correct error – thanks to Gfamily for alerting me

Thanks.

Some remarks:
1) If the module containing this code has a line Option Explicit near the top – which I recommend very strongly in almost all circumstances – the macro will not run. So if Option Explicit is present, it should be commented out or removed. For this reason, I wouldn’t store other code in the same module.
2) You can use FullName instead of combining Path and Name of the active document/workbook/presentation.
3) If you don’t use the return value of MsgBox and Shell, there is no need to assign it to a variable.
4) You can use Select Case instead of repeated ElseIfs.

Here is the resulting macro:

Sub FindMe()
‘
‘Macro created 25_June_2006 by Owen Gwynne
‘
Dim Here_I_am As String
Dim What_I_am As String
Dim My_Name As String

What_I_am = Application.Name
Select Case What_I_am
Case “Microsoft Word”
My_Name = ActiveDocument.FullName
Case “Microsoft Excel”
My_Name = ActiveWorkbook.FullName
Case “Microsoft PowerPoint”
My_Name = ActivePresentation.FullName
End Select

If My_Name = “” Then
MsgBox “File Not Saved, no current folder defined”
Else
Here_I_am = “explorer /n, /select,””” & My_Name & “”””
Shell Here_I_am, vbNormalFocus
End If
End Sub

Reply | Quote

Hans
As always I learned something from your post, but I cannot see a benefit of the code over the simple menu sequence of File | Properties | General tab.

Reply | Quote

The difference is that this macro opens an instance of Windows Explorer with the correct folder and file selected. If you don’t need that, File | Properties is sufficient (or a macro that simply displays ActiveDocument.FullName in a MsgBox).

Reply | Quote

Hi Don,
If you want to use any of the rightclick functions on the document – for example send it to a zipped file, rename it in the same location, call up the Web publishing wizard or even something as simple as being able to drag and drop it into another folder (rather than having to navigate the through the Save dialog box) this is now only one-step away.

Reply | Quote

Be careful with that – the macro opens the document in Windows Explorer while it is open in its application, so you cannot rename the document or move it to another folder. You must close the document first.

Reply | Quote