That's very possible. Based on some previous work i've done, I created a function called OpenFile() that returns the path name of the selected file as a string:
VBA Code:
''==========================================================================================================================
'' Name: OpenFile()
'' Desc: Opens file dialog and prompts user to select a file
'' Called by: cmd_ImportData_Click()
'' Args: N/A
'' Comments: (1) Default path is E:\! Sewing (usb)
'' (2) Limits selection to one file at a time
'' (3) Code from https://www.chicagocomputerclasses.com/excel-vba-display-a-file-open-dialog-and-open-the-file-excel-functions/
''==========================================================================================================================
Private Function OpenFile() As String
Dim fullpath As String
On Error Resume Next
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False 'user can only select one file not multiple
.Filters.Add 'If you want to filter by image extensions only or all file types
.InitialFileName = "E:\..." 'initial location to open the dialog
.Show
fullpath = .SelectedItems.Item(1)
End With
'Quit the procedure if the user didn't select the type of file we need.
If InStr(fullpath, "Sew3") = 0 Then
MsgBox ("Incorrect file type selected.")
Exit Function
End If
On Error GoTo 0
Workbooks.Open fullpath
OpenFile = fullpath
End Function
Depending on your needs, you might not need the section to verify the file name to ensure the correct type of file is used. If not, just remove that if function and the two
On Error
lines.
In your main function (button click event function) you want to call
OpenFile()
to know the file path to copy, and then actually do the copying. Lastly, write the destination path to whatever cell you want in the worksheet. Something like:
VBA Code:
Private Sub buttonName_Click()
Dim sourceFile As String
Dim fso As Object
Dim destinationPath As String
sourceFile = OpenFile
destinationPath = "..."
Set fso = VBA.CreateObject("Scripting.FileSystemObject")
sourceFile
Call fso.CopyFile(sourceFile, "destinationPath")
SheetName.Range("A1").Value = destinationPath
End Sub
Look at
this post at StackOverflow for this same solution, or the Microsoft Documentation for
FileSystemObjects for all the possible things you can do.