CODEWITHSUNDEEP
excelvbadrag-and-drop
Jeanjean
Jeanjean

Reputation: 863

Drag and drop multiple files to Userform

I am trying to drag and drop more than one file on a Userform to get their paths. I managed it with one file thanks to this.

When I change FilePath = Data.files(1) to FilePath = Data.Files(2) or (i), I get a message "Table Expected". Should I create a Table and Redim it?

My work so far:

This one does the job (opening the file and copying it into a selected sheet)

Dim Wb, FilePath As String
Dim WbIni, WbCib As Workbook

Private Sub CommandButton2_Click()

If FilePath = vbNullString Then
    MsgBox "Aucun fichier n'a été importé", vbCritical, "Anomalie"
    Unload UserForm1
    Exit Sub
End If

Set WbCib = Workbooks.Open(Filename:=FilePath)
MsgBox WbCib.Name

i = WbCib.ActiveSheet.Range("A1").End(xlDown).Row
WbCib.ActiveSheet.Range("A1:A" & i).Copy
ActiveSheet.Paste Destination:=WbIni.Worksheets("Target").Range("A1:A" & i)
WbIni.Sheets("Target").Activate
WbCib.Close

Unload UserForm1

End Sub

This one initializes my TreeView for the drag and drop

Private Sub UserForm_Initialize()
Wb = ThisWorkbook.Name
Set WbIni = ActiveWorkbook

TreeView1.OLEDropMode = ccOLEDropManual
End Sub

This one gives me the file path. I think I need to loop it.

Private Sub TreeView1_OLEDragDrop(Data As MSComctlLib.DataObject, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)

    FilePath = Data.Files(1)
    Workbooks(Wb).Activate

    MsgBox FilePath

End Sub

Upvotes: 3

Views: 3045

Answers (1)

Jeanjean
Jeanjean

Reputation: 863

Thanks to @R.Roe's comment, I managed to do what I wanted :

Dim x, y As Integer
Dim PathTable As String
Dim FilePath As Variant

Private Sub TreeView1_OLEDragDrop(Data As MSComctlLib.DataObject, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)
Dim i As Integer

Workbooks(Wb).Activate

'Counting file paths I dropped
For Each FilePath In Data.Files()
    i = i + 1
Next FilePath

'Redim my table
ReDim PathTable(i)

i = 1

'Adding data to my table
For x = 0 To UBound(PathTable) - 1
    PathTable(x) = Data.Files(i)
    i = i + 1
Next x

'Just to make sure it works
For x = 0 To UBound(PathTable) - 1
    MsgBox PathTable(x)
Next x

End Sub

Cheers!

Upvotes: 1

Related Questions