VBA drag file to user form to get file name and path

Question

I would like to learn a new trick, but I am not 100% sure, this is possible in VBA, but I thought that I would check with the gurus here.

What I would like to do is to refuse the good getopenfilename or browser (it was really difficult to get the initial directory installed on our network drive), and I would like to create a VBA user form where the user can drag the file from the desktop or to the browser window in form, and VBA will load the file name and path. Again, I'm not sure if this is possible, but if it is, or if someone has done it before I appreciate the pointers. I know how to set up a custom form, but I have no real code outside of this. If there is anything that I can provide, let me know.

Thank you for your time and attention!

Answer 1

I have found a way to achieve this. As far as I can tell, this can only be done using the treeview control. You may need to right-click on the toolbar to find and add it. He will be there under “additional control” or something like that. You will need two things besides control.

In the UserForm_Initialize routine, you 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 omitted all options to save space. They should be easy enough to find. In this routine, just declare a line, maybe strPath or something like that, to save the file name and path, and set strPath = Data.Files(1) and this will get the file name and path to the file that the user drags into Treeview the 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 by dragging and dropping several 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 

Change: You will also need to add a link to Microsoft Windows Common Controls 6.0

I also added sample code.

Answer 2

I know this is an old topic. Future readers: If you need some cool user interface, you can check out my Github for an example database using the .NET wrapper dll. Which allows you to simply call a function and open a dialog using the drag-and-drop file function. The result is returned as a JSONArray string.

the code may be as 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 

this is how it looks;

Answer 3

Matt's solution is excellent, you just need to make sure:

You must add a TreeView control that supports the OLEDragDrop operation:

Right-click the Toolbar pane, showing all available controls. Select "Additional controls ..." Turn on: "Microsoft TreeView control, version 6.0"

Answer 4

@MattB and @Joanna, I'd rather comment instead of the answer, but I don't have 50+. @ Jean-FIC managed to get multiple files by drag and drop based on this stream, repeating them with

 For Each FilePath In Data.Files() i = i + 1 Next FilePath 

Full code at this link. Thanks for sharing this code.

Answer 5

I got it to work using the Event WorkbookOpen application. When a file is dragged onto an open Excel worksheet, it tries to open the file in Excel as a separate workbook that will trigger the above event. This is a little painful, but I used this https://bettersolutions.com/vba/events/excel-application-level-events.htm link as a link.

The only problem is that if the file is not an Excel file, it will have a pop-up window and you will not be able to run VBScript to get rid of it, because the event will not be fired until you open the pop-up window. Part of my code below:

 Public WithEvents App As Application Private Sub App_WorkbookOpen(ByVal Wb As Workbook) Dim path, pathExt As String path = Wb.Name pathExt = Mid(path, InStrRev(path, ".")) If pathExt = ".pdf" Then Application.DisplayAlerts = False Workbooks(Wb.Name).Windows(1).Visible = False Dim n As String n = Wb.FullName Wb.Close Call DragnDrop.newSheet(n) Application.DisplayAlerts = True End If End Sub 

Edit: forgot that you need to initialize application events by placing the code below in any module

 Option Explicit 'Variable to hold instance of class clsApp Dim mcApp As clsApp Public 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 Application End Sub 

And then paste this code into ThisWorkbook Workbook_Open ()

 'Initialize the Application Events Application.OnTime Now, "'" & ThisWorkbook.FullName & "'!Init"