Copy the location of an Excel file to the clipboard
Posted: Fri Oct 18, 2013 5:17 pm
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, ...
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