Home > database >  Excel Drag/Drop to Get Filename and Path
Excel Drag/Drop to Get Filename and Path

Time:08-02

I have a user form "UserForm1" and am using the following code to obtain the filename and path of a file that the user has dragged and dropped into the TreeView located on the userform.

 Public 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)
    Debug.Print StrPath
    Call PrintPath
End Sub

Then in the UserForm_Initialize I have

TreeView1.OLEDropMode = ccOLEDropManual

I know this code is getting the path and name because I'm able to debug.print it. However, my issue is I can't get this filename and path to be utilized in a module. For instance I have tried to do the following for the simplest of uses (to print the filename and path to cell A1):

    Public Sub PrintPath()
UserForm1.TreeView1.StrPath = Range("A1").Value

'StrPath.Value = Range("A1").Value

'UserForm1.StrPath.Value = Range("A1").Value

'Range("A1").Value = UserForm1.Data.Files(1)

End Sub

All of the commented lines are other versions I have attempted with no avail. I typically get Object does not exist. Sometimes 424 errors. Can anyone point me in the right direction?

Please and thank you!

Chris

CodePudding user response:

The typical way to do this woould be to pass the path as an argument to PrintPath

Public Sub TreeView1_OLEDragDrop(Data As MSComctlLib.DataObject, Effect As Long, _
                     Button As Integer, Shift As Integer, x As Single, y As Single)
    Dim strPath
    strPath = Data.Files(1)
    Debug.Print strPath
    PrintPath strPath '<< pass in the path
End Sub

Public Sub PrintPath(sPath)
    ActiveSheet.Range("A1").Value = sPath
End Sub

CodePudding user response:

I have just now figured out the answer. In the UserForm1 Code I did

Option Explicit
Public StrPath As Variant 

Then in the module I was able to use it with

UserForm1.StrPath

i.e.

Range("A1").Value = UserForm1.StrPath
  • Related