VBA drag and drop file to user form to get filename and path
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 = ccOLEDropManualEnd 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.
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;
I got it to work by using Application Event WorkbookOpen. When a file gets dragged onto an open Excel Sheet it will try to open that file in Excel as a separate workbook which would trigger the above event. It's a bit of a pain but I used this link https://bettersolutions.com/vba/events/excel-application-level-events.htm as a reference.
Only issue is that if the file isn't an Excel file then it will have a popup and you can't run a VBScript to get rid of it since the Event won't run until you address the popup. A portion of my code below:
Public WithEvents App As ApplicationPrivate Sub App_WorkbookOpen(ByVal Wb As Workbook)Dim path, pathExt As Stringpath = Wb.NamepathExt = Mid(path, InStrRev(path, "."))If pathExt = ".pdf" ThenApplication.DisplayAlerts = FalseWorkbooks(Wb.Name).Windows(1).Visible = FalseDim n As Stringn = Wb.FullNameWb.CloseCall DragnDrop.newSheet(n)Application.DisplayAlerts = TrueEnd IfEnd Sub
Edit:Forgot that you need to initialize the Application Events by posting the below code in any module
Option Explicit'Variable to hold instance of class clsAppDim mcApp As clsAppPublic Sub Init() 'Reset mcApp in case it is already loaded Set mcApp = Nothing 'Create a new instance of clsApp Set mcApp = New clsApp 'Whatever you named your class module 'Pass the Excel object to it so it knows what application 'it needs to respond to Set mcApp.App = Application 'mcApp.Whatever you named this Public 'WithEvents App As ApplicationEnd Sub
And then paste this code in ThisWorkbook Workbook_Open()
'Initialize the Application EventsApplication.OnTime Now, "'" & ThisWorkbook.FullName & "'!Init"