VBA drag and drop file to user form to get filename and path

Question

I'd like to learn a new trick, but I'm not 100% confident it is possible in VBA, but I thought I'd check with the gurus here.

What I'd like to do is eschew the good-old getopenfilename or browser window (it has been really difficult to get the starting directory set on our network drive) and I'd like to create a VBA user form where a user can drag and drop a file from the desktop or a browser window on the form and VBA will load the filename and path. Again, I'm not sure if this is possible, but if it is or if someone has done it before I'd appreciate pointers. I know how to set up a user form, but I don't have any real code outside of that. If there is something I can provide, let me know.

Thanks for your time and consideration!

Answer 1

I figured out a way to achieve this. As far as I can tell, it can only be done using a treeview control. You may have to right click your toolbox to find and add it. It will be there under "additional controls" or something like that. You'll need two things aside from the control.

In the UserForm_Initialize sub you will need the following line of code to enable drag and drop: TreeView1.OLEDropMode = ccOLEDropManual:

UserForm_Initialize()
    TreeView1.OLEDropMode = ccOLEDropManual
End Sub

Then you will need the Private Sub TreeView1_OLEDragDrop event. I've omitted all the parameters to save space. They should be easy enough to find. In that sub simply declare a string, maybe strPath or something like that to hold the file name and path and set strPath = Data.Files(1) and that will get the file name and path of a file that the user drags to the TreeView control. This assumes that the user only drags one file at a time, but as far as I can tell this should be something that can be done dragging multiple files if you experiment with it.

Private Sub TreeView1_OLEDragDrop(Data As MSComctlLib.DataObject, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)
    StrPath = Data.Files(1)
End Sub

Edit: You will also need to add a reference to Microsoft Windows Common Controls 6.0

I've also added example code.

Answer 2

I know this is an old thread. Future readers, If you are after some cool UI, you can checkout my Github for sample database using .NET wrapper dll. Which allows you to simply call a function and to open filedialog with file-drag-and-drop function. Result is returned as a JSONArray string.

code can be simple as

Dim FilePaths As String
    FilePaths = gDll.DLL.ShowDialogForFile("No multiple files allowed", False)
'Will return a JSONArray string.
'Multiple files can be opend by setting AllowMulti:=true

here what it looks like;