VBA Drag & Drop filepath

[jorispk]

Hi guys,

Yesterday I got some stuff working with dropping content from listbox to listbox.

No I'm trying to get a path from an external file, but I'm still not able to figure out a way to get the filepath...

This is what I have:

Private Sub test_BeforeDragOver(ByVal Cancel As MSForms.ReturnBoolean, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal DragState As Long, _
    ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
    Cancel = True
    Effect = 1
End Sub
Private Sub test_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, ByVal Action As Long, ByVal Data As MSForms.DataObject, ByVal X As Single, _
    ByVal Y As Single, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
    Cancel = True
    Effect = 1
    Dim MyDataObject As DataObject
    Set MyDataObject = New DataObject
    'MyDataObject = GetObject(Data.Files(1))
    Me.test = MyDataObject
End Sub

I thought maybe something as Data.Files(1) would work but it doesn't. The Drag and drop feature works fine though if I say for example Me.test = "check" than it becomes that after dragging the file.

Any suggestions how to get the filepath??

Thank you

 

[sktz]

Hoping to revive this post. I downloaded the sample and it works perfectly, but I don't understand it well enough to make a change I'm looking for. I want the file path to print to a textbox instead of a listbox, because I only want to allow one file at a time. I'm guessing the below is the section that will need to change, but I'm just not sure how.

Do While GetMessage(tMsg, 0, 0, 0) And IsWindow(hwnd)
If tMsg.message = WM_DROPFILES Then
HDROP = tMsg.wParam
lFilesCount = DragQueryFile(HDROP, &HFFFFFFFF, 0, 0)
If lFilesCount Then
For i = 0 To lFilesCount - 1
ListBox1.AddItem Left(sFileName, DragQueryFile(HDROP, i, sFileName, Len(sFileName)))
Next i
End If
Call DragFinish(HDROP)
End If
Call TranslateMessage(tMsg)
Call DispatchMessage(tMsg)
Loop

 

[Jaafar Tribak]

Hoping to revive this post. I downloaded the sample and it works perfectly, but I don't understand it well enough to make a change I'm looking for. I want the file path to print to a textbox instead of a listbox, because I only want to allow one file at a time. I'm guessing the below is the section that will need to change, but I'm just not sure how.

Contrary to ListBoxes, TextBoxes are windowless controls so they don't have a HWND which is necessary for registering the control as an ole drag and drop target.

But we can use a workaround by placing the textbox inside a frame control as the latter does have a hwnd and can be registered as an ole target. There is still room for improvement on this code but it should work as is.

Here is file demo


I have added two textboxes each one inside its own frame control. The first textbox is a single line textbox that accepts only one file at a time and the second textbox is multiline and therefore will accept multiple files.

Option Explicit

Private Sub UserForm_Initialize()
    Me.Frame2.TextBox2.MultiLine = True
    Call EnableDragAndDrop(Me.Frame1, PublicEventHandlerName:="OnFileDrop")
    Call EnableDragAndDrop(Me.Frame2, PublicEventHandlerName:="OnFileDrop")
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Call EnableDragAndDrop(Me.Frame1, , bEnableDrop:=False)
    Call EnableDragAndDrop(Me.Frame2, , bEnableDrop:=False)
End Sub

Private Sub CommandButton1_Click()
    Unload Me
End Sub


'________________________________________File Drop Event Hnadler__________________________________________

Public Sub OnFileDrop(ByVal TargetObj As Object, ByVal FilePathName As String, ByVal KeyState As Integer)
    If TypeOf TargetObj Is MSForms.Frame Then
        With TargetObj.Controls(0&)
            If .MultiLine Then
                .Text = IIf(Len(.Text) = 0&, FilePathName, .Text & vbLf & FilePathName)
            Else
                .Text = FilePathName
            End If
        End With
    End If
End Sub

 

[sktz]

Thank you so much Jaafar! That works as desired. If I want to stick with the single-line only, I think I should simplify the form's code by removing these lines. Can you please confirm that won't cause any issues?

Private Sub UserForm_Initialize()
Me.Frame2.TextBox2.MultiLine = True
Call EnableDragAndDrop(Me.Frame1, PublicEventHandlerName:="OnFileDrop")
Call EnableDragAndDrop(Me.Frame2, PublicEventHandlerName:="OnFileDrop")
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Call EnableDragAndDrop(Me.Frame1, , bEnableDrop:=False)
Call EnableDragAndDrop(Me.Frame2, , bEnableDrop:=False)
End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub


'________________________________________File Drop Event Handler__________________________________________

Public Sub OnFileDrop(ByVal TargetObj As Object, ByVal FilePathName As String, ByVal KeyState As Integer)
If TypeOf TargetObj Is MSForms.Frame Then
With TargetObj.Controls(0&)
If .MultiLine Then
.Text = IIf(Len(.Text) = 0&, FilePathName, .Text & vbLf & FilePathName)
Else
.Text = FilePathName
End If
End With
End If
End Sub

 

[Jaafar Tribak]

Thank you so much Jaafar! That works as desired. If I want to stick with the single-line only, I think I should simplify the form's code by removing these lines. Can you please confirm that won't cause any issues?

Yes. That will work just fine.

Option Explicit

Private Sub UserForm_Initialize()
    Call EnableDragAndDrop(Me.Frame1, PublicEventHandlerName:="OnFileDrop")
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Call EnableDragAndDrop(Me.Frame1, , bEnableDrop:=False)
End Sub

Private Sub CommandButton1_Click()
    Unload Me
End Sub

'________________________________________File Drop Event Hnadler__________________________________________

Public Sub OnFileDrop(ByVal TargetObj As Object, ByVal FilePathName As String, ByVal KeyState As Integer)
    If TypeOf TargetObj Is MSForms.Frame Then
        With TargetObj.Controls(0&)
            .Text = FilePathName
        End With
    End If
End Sub

 

[sktz]

Thanks again! This is a key feature to a tool I'm working on, so I really appreciate your help

 

[Jaafar Tribak]

Thanks again! This is a key feature to a tool I'm working on, so I really appreciate your help

You are most welcome and I am glad the code was of help to your project.

 

[sktz]

Back again. Two things:
1. When I try to use the multi-line textbox, the files don't drag in.
2. Would it be possible to populate multiple paths at once by dragging in several files at once?

Thanks in advance!

 

[Jaafar Tribak]

Back again. Two things:
1. When I try to use the multi-line textbox, the files don't drag in.
2. Would it be possible to populate multiple paths at once by dragging in several files at once?

Thanks in advance!

Are you using the linked file domo in post#62 ?

 

[sktz]

No, because the folks I'm making it for requested multiple lines I decided to go with a ListBox, using code from post #12. (Thanks anyway for the help with the texbox -- it's all a big VBA learning experience for me as well)

 

[sktz]

To clarify, yes I was using that demo file itself and the multi-line textbox wasn't working for me, so I switched to a listbox. And now I'm wondering about dragging in multiple files at once, please