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

user_2761919 0
Viewed 35063

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!

userform drag-and-drop vba excel
3 Answers

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;

In Action

user_16844860

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 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 the Application Events by posting the below code 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 in ThisWorkbook Workbook_Open()

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