Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Where is this Document? (All)

    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

    <pre>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</pre>


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Where is this Document? (All)

    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

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Where is this Document? (All)

    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.
    Regards
    Don

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Where is this Document? (All)

    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).

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Where is this Document? (All)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Where is this Document? (All)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •