File Path

[austin350s10]

Is there a way to have a macro pop-up a box with the file path of the excel workbook. I'm know that this is easy but there's a catch!

PROBLEM: I need the file path for the workbook to be copyable. The user needs to copy and paste the file path into a separate program.

THOUGHT: I was thinking some sort of message box that is triggered by a macro that has an Input field pre-populated with the full file path, that can be copied, and instructions for what the user needs to do.

QUESTION: Is this doable?

 

[Misca]

You could create a UserForm with a TextBox. This piece of code types the path of the workbook to the Textbox1:

Private Sub UserForm_Initialize()
TextBox1.Text = ThisWorkbook.Path
End Sub

 

[Misca]

I also found an easier way to do the same.

This one copies the path directly to the clipboard:

Dim DataObj As New MSForms.DataObject
    Dim S As String
    S = ThisWorkbook.Path
    DataObj.SetText S
    DataObj.PutInClipboard
    
MsgBox "This workbook's path " & vbNewLine & vbNewLine & S & vbNewLine & vbNewLine & "was copied to the clipboard."

 

[austin350s10]

Sorry I'm a little new to this. How would I apply the above code to a button as a macro?

 

[Misca]

1) Press Alt + F11 to open the VBA Editor.
2) In the editor choose Insert --> Module. Now you're ready to type the code.
3) Type "Sub MyMacro" (without the quotes) and press Enter. The editor fills the "End Sub" for you. Now between those two lines paste the code from above.
4) Add the button to your worksheet (or whatever you prefer). Right click the object and select "Assign Macro". Select "MyMacro" from the list and press OK.