Copy the location of an Excel file to the clipboard

Ideas and tips for enhancing your TM1 application
Post Reply
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Copy the location of an Excel file to the clipboard

Post by Wim Gielis »

Hello all

This is more of an Excel topic, but I wanted to share this piece of code with you.
I use it to store the path and name of an Excel file in the clipboard, when I need to email the file.
The code is part of my personal macro workbook and it's executed from the QAT.

If the file has not been saved yet, no location exists, and the code will ask you to save the file first.

Note that you must add a reference to the "Microsoft Form 2.0 Object Library".
If anyone knows a simple way to the clipboard without needing this reference, let me know.

Can you please try this code on your environment, particularly network lcoations, ...

Code: Select all

Public Sub FilenameForOutlook()

' Wim Gielis
' October 2013

    Dim sFileName As String
    
    Application.ScreenUpdating = False
    
    With ActiveWorkbook

        If (Len(.Path) > 0) And (Len(Dir(.Path, vbDirectory)) > 0) Then

            ''''' FILE WAS ALREADY SAVED '''''

            'save the file
            If Not .Saved Then

                Application.DisplayAlerts = False
                Application.EnableEvents = False
                On Error Resume Next
                .Save
                On Error GoTo 0
                Application.DisplayAlerts = True
                Application.EnableEvents = True

            End If

            'get the fullname
            sFileName = .FullName

        Else

            ''''' WFILE WAS NOT ALREADY SAVED '''''

            With Application.FileDialog(msoFileDialogSaveAs)

                'a file was picked
                If .Show Then

                    'save the file
                    Application.DisplayAlerts = False
                    Application.EnableEvents = False
                    On Error Resume Next
                    .Execute
                    On Error GoTo 0
                    Application.DisplayAlerts = True
                    Application.EnableEvents = True

                    'get the fullname
                    sFileName = ActiveWorkbook.FullName
                    
                End If

            End With

        End If

    End With

    'store in the clipboard
    If Len(sFileName) Then
        If Len(Dir(sFileName)) Then
            With New DataObject
                .SetText sFileName
                .PutInClipboard
            End With
        End If
    End If

End Sub
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
mauriciosaucedo
Posts: 6
Joined: Wed Aug 27, 2008 9:39 pm
OLAP Product: Cognos TM1
Version: 9.5.2
Excel Version: 2010
Location: Monterrey, MX

Re: Copy the location of an Excel file to the clipboard

Post by mauriciosaucedo »

Hi Wim

Another option is to add the Document Location command to the quick access toolbar, that way you don't need any libraries.
I use Excel 2007 but I think its also available in 2010
Post Reply