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