Hi there
I've been looking into the possibility of dragging / dropping files onto a
userform control - where the control would grab the first files path for use
later... I have managed to get as far as the 'BeforeDropOrPaste' event - and
while I can see this is available in Label controls, I can't see how to
process the 'dropped' data apckage according to files / filenames. Looking at
the MSDN library just lists some constants. Googling & searching this
newsgroup didn't come up with anything.
Any pointers greatly appreciated.
cheers.

Here's an example using the _OLEDragDrop event of a TreeView control. The
dropped item comes in as an MSForms DataObject, which exposes a 'Files'
collection. My example assumes one file; you can put in a loop to process a
grouped selection of files:

** Watch out for line-breaks introduced by the text box control on this HTML
newsgroup interface **



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

Dim strPath As String
Dim strIcon As String

strPath = Data.Files(1)

If Len(Dir(strPath)) 0 Then 'Check file and path exist
ThisWorkbook.Names("FileLeaseData").RefersToRange. Value = strPath
Else
Exit Sub
End If


LoadFilePath Me.tvLeaseDataFile, strPath

End Sub


LoadFilePath is a small string-handler I wrote for populating a TreeView
control with a network path. You can put any code you please in there... But
here goes:



Public Sub LoadFilePath(myTreeView As TreeView, myPath As String)

' This code clears a treeview and repopulates it with the expanded folder
path of file 'myPath'

' A treeview is a better control than a standard textbox for displaying the
full path and name
' of a file in a network folder. It shows muliple lines, displays the folder
path graphically,
' and - best of all - can accept a file as a drag-and-drop from Explorer,
avoiding the need to
' code up a common dialog behind a button allowing the user to find and save
the full path.

' REQUIRED: a Microsoft TreeView Control 6.0 (SP4.0), with an associated
ImageList Control 6.0
' containing four named icons for drives and folders. This example
uses copied Win32
' 16x16 bitmaps named "NetworkDrive", "LocalDrive", "OpenFolder"
and "ClosedFolder".
' Create icons for specific file types and load then into the
ImageList as required: I have Excel, text, and a custom bitmap

' THIS CODE IS IN THE PUBLIC DOMAIN Nigel Heffernan May 2005


Dim strName As String
Dim strFolder As String
Dim NewNode As Node
Dim iStart As Integer
Dim iEnd As Integer
Dim strKey As String
Dim strIcon As String

With myTreeView

.Indentation = 0
.Style = tvwTreelinesPlusMinusPictureText
.Nodes.Clear


'Add the Root:
If Left(myPath, 2) = "\\" Then

iStart = 3
iEnd = InStr(3, myPath, "\")
strFolder = Mid(myPath, iStart, iEnd - iStart)
strKey = Left(myPath, iEnd)
Set NewNode = .Nodes.Add(, , strKey, strFolder, "NetworkDrive",
"NetworkDrive")
NewNode.EnsureVisible

ElseIf InStr(1, myPath, ":\") < 4 Then

iStart = 1
iEnd = InStr(1, myPath, ":\") + 1
strFolder = Mid(myPath, iStart, iEnd - iStart)
strKey = Left(myPath, iEnd - 1)
Set NewNode = .Nodes.Add(, , strKey, strFolder, "LocalDrive",
"LocalDrive")
NewNode.EnsureVisible

End If


'Add the folders:
iStart = iEnd + 1
iEnd = InStr(iStart, myPath, "\")


Do Until iEnd = 0

strFolder = Mid(myPath, iStart, iEnd - iStart)

Set NewNode = .Nodes.Add(strKey, tvwChild, Left(myPath, iEnd),
strFolder, "OpenFolder", "ClosedFolder")
NewNode.EnsureVisible
strKey = Left(myPath, iEnd)
iStart = iEnd + 1
iEnd = InStr(iStart, myPath, "\")

Loop



'Add The file:
strName = Right(myPath, Len(myPath) - iStart + 1)


If LCase(Right(myPath, 4)) = ".xls" Then
If InStr(myPath, "MOPR") 0 Then
strIcon = "LeaseFile"
Else
strIcon = "ExcelFile"
End If
Else
strIcon = "OtherFile"
End If

Set NewNode = .Nodes.Add(strKey, tvwChild, myPath, strName, strIcon,
strIcon)
NewNode.EnsureVisible



End With

End Sub

Here's an example using the _OLEDragDrop event of a TreeView control. The
dropped item comes in as an MSForms DataObject, which exposes a 'Files'
collection. My example assumes one file; you can put in a loop to process a
grouped selection of files:

** Watch out for line-breaks introduced by the text box control on this HTML
newsgroup interface **



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

Dim strPath As String
Dim strIcon As String

strPath = Data.Files(1)

If Len(Dir(strPath)) 0 Then 'Check file and path exist
ThisWorkbook.Names("FileLeaseData").RefersToRange. Value = strPath
Else
Exit Sub
End If


LoadFilePath Me.tvLeaseDataFile, strPath

End Sub


LoadFilePath is a small string-handler I wrote for populating a TreeView
control with a network path. You can put any code you please in there... But
here goes:



Public Sub LoadFilePath(myTreeView As TreeView, myPath As String)

' This code clears a treeview and repopulates it with the expanded folder
path of file 'myPath'

' A treeview is a better control than a standard textbox for displaying the
full path and name
' of a file in a network folder. It shows muliple lines, displays the folder
path graphically,
' and - best of all - can accept a file as a drag-and-drop from Explorer,
avoiding the need to
' code up a common dialog behind a button allowing the user to find and save
the full path.

' REQUIRED: a Microsoft TreeView Control 6.0 (SP4.0), with an associated
ImageList Control 6.0
' containing four named icons for drives and folders. This example
uses copied Win32
' 16x16 bitmaps named "NetworkDrive", "LocalDrive", "OpenFolder"
and "ClosedFolder".
' Create icons for specific file types and load then into the
ImageList as required: I have Excel, text, and a custom bitmap

' THIS CODE IS IN THE PUBLIC DOMAIN Nigel Heffernan May 2005


Dim strName As String
Dim strFolder As String
Dim NewNode As Node
Dim iStart As Integer
Dim iEnd As Integer
Dim strKey As String
Dim strIcon As String

With myTreeView

.Indentation = 0
.Style = tvwTreelinesPlusMinusPictureText
.Nodes.Clear


'Add the Root:
If Left(myPath, 2) = "\\" Then

iStart = 3
iEnd = InStr(3, myPath, "\")
strFolder = Mid(myPath, iStart, iEnd - iStart)
strKey = Left(myPath, iEnd)
Set NewNode = .Nodes.Add(, , strKey, strFolder, "NetworkDrive",
"NetworkDrive")
NewNode.EnsureVisible

ElseIf InStr(1, myPath, ":\") < 4 Then

iStart = 1
iEnd = InStr(1, myPath, ":\") + 1
strFolder = Mid(myPath, iStart, iEnd - iStart)
strKey = Left(myPath, iEnd - 1)
Set NewNode = .Nodes.Add(, , strKey, strFolder, "LocalDrive",
"LocalDrive")
NewNode.EnsureVisible

End If


'Add the folders:
iStart = iEnd + 1
iEnd = InStr(iStart, myPath, "\")


Do Until iEnd = 0

strFolder = Mid(myPath, iStart, iEnd - iStart)

Set NewNode = .Nodes.Add(strKey, tvwChild, Left(myPath, iEnd),
strFolder, "OpenFolder", "ClosedFolder")
NewNode.EnsureVisible
strKey = Left(myPath, iEnd)
iStart = iEnd + 1
iEnd = InStr(iStart, myPath, "\")

Loop



'Add The file:
strName = Right(myPath, Len(myPath) - iStart + 1)


If LCase(Right(myPath, 4)) = ".xls" Then
If InStr(myPath, "MOPR") 0 Then
strIcon = "LeaseFile"
Else
strIcon = "ExcelFile"
End If
Else
strIcon = "OtherFile"
End If

Set NewNode = .Nodes.Add(strKey, tvwChild, myPath, strName, strIcon,
strIcon)
NewNode.EnsureVisible



End With

End Sub