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

 

[traveler4]

I did not experience the issue you describe. Anyways, I have now updated the code so that it will also work with more tha one ListBox . Also, I now will be using GetLongPathNameW + longer string buffers so, hopefully, this will probably remedy the above issue you are experiencing.

Thanks, @Jaafar Tribak, it's always great to get more than expected .

I have tried your new code (from post #37) and have the following findings:
A) Drag&Drop from Local Disk with NTFS: works correctly
B) Drag&Drop from USB Stick with exFAT: works correctly
C) Drag&Drop from Network Drive with NTFS: works correctly with a short filename. With a long filename (>255 characters), I get a run-time error:

My reported test result from Sunday (post #33) was obtained under test constellation C.
Btw, Adobe Acrobat Pro DC (32-bit) behaves identically: it opens long named documents under constellations A and B, but under C it does not (without any error message).

Again, this is rather for information. I'm grateful for the Drag&Drop to work in cases A and B and can do without C.

 

[Jaafar Tribak]

@traveler4
Thanks for letting us know.

C) Drag&Drop from Network Drive with NTFS: works correctly with a short filename. With a long filename (>255 characters), I get a run-time error:

I guess you could check if the path is UNC or not and then adjust the code accordingly... Perform the check in the Drop Function right after the line : sShortPathName = Left(sShortPathName, Ret)

The following functions can be used to easily find out:

#If VBA7 Then
    Private Declare PtrSafe Function PathIsNetworkPathW Lib "shlwapi" (ByVal pszPath As LongPtr) As Long
    Private Declare PtrSafe Function PathIsUNCW Lib "shlwapi" (ByVal pszPath As LongPtr) As Long
#Else
    Private Declare Function PathIsNetworkPathW Lib "shlwapi" (ByVal pszPath As Long) As Long
    Private Declare Function PathIsUNCW Lib "shlwapi" (ByVal pszPath As Long) As Long
#End If

 

[MountainMain]

Jaafar,

Does the Larache link in your signature block mean you live there? One of my sons (we're Americans) visited there in college and really liked it. I haven't been to Morocco but I have been to Algeria several times. in fact, I was there on 9/11 and that was a weird time to be there...

Anyway, I do a lot of API-related coding and I am not convinced you need the function "GetLongPathNameW". You are using it on the return from the call to DragQueryFileW and in the MS documentation for that function I don't see anything where it says that the buffer for the file path (lpszFile) is a short pathname. I've never run into that situation with any other file path-related functions. I think it is Unicode and allows up to 32,767 characters like the other file/path Windows Unicode functions, negating the need for GetLongPathNameW if the buffer size you tell Windows you have for DragQueryFileW (cch parameter) is the size of your large Byte array used for the buffer.

In my modifications to your code (which is excellent BTW), I set a Byte array that I ReDim to 32,768 and then I re-use it over and over to catch the file paths and from there I use RtlMovememory and SysAllocStringLen to get the bytes into a string. It seems to work well. I haven't tried it on a string longer than 16,383 characters but it seems okay up to that point so I wouldn't expect any issues up to the Unicode max of 32,767. I can send you my version if you are interested.

Also, I am guessing your code could be a bit more efficient if you passed back an array of the file paths instead of each one individually.

Finally, I am not sure about KeyStates. You make an array for this and pass back a value for each file path. But since it is the same for all file paths retrieved from a drop, why not just make it one value for all of them instead of an array?

 

[traveler4]

I guess you could check if the path is UNC or not and then adjust the code accordingly... Perform the check in the Drop Function right after the line : sShortPathName = Left(sShortPathName, Ret)

The following functions can be used to easily find out:
PathIsNetworkPathW()
PathIsUNCW()

Thankyou for the advice.
I have included the functions into the code (see image) and got the output which can be seen in the Immediate-window. First, I dragged a file from local disk (C:\) and then from the network drive (U:\). In both cases, len(path+name+suffix) were < 255.

A) As the path is not UNC in any case, I do not see how to "adjust the code accordingly".
B) If I would drop a file which exceeds the 255-char-limit, I would get the exception at ReDim sFileNames(lFilesCount - 1) already, so I would never get to sShortPathName = Left(sShortPathName, Ret) to access sShortPathName and do something differently depending on whether it is UNC or not.

 

[traveler4]

B) If I would drop a file which exceeds the 255-char-limit, I would get the exception at ReDim sFileNames(lFilesCount - 1) already, so I would never get to sShortPathName = Left(sShortPathName, Ret) to access sShortPathName and do something differently depending on whether it is UNC or not.

I have just found out, that the size limit is actually not 255 as previously assumed, but 259 (still supported) / 260 (fails).

 

[MountainMain]

I have been playing around with drops from Windows Explorer and Directory Opus. It appears that the maximum length of a path is around 260 characters. To some extent this makes sens because all of the operations using the Windows Shell have this limit. I suspect that the drag and drop functions are part of Shell although it is possible that it is a limit of the COM function "CallDispFuncCOM" because it returns 0 when the number of dropped files is 1 or more. I made a text file inside of the very long path (about 300 characters) and I can't open it in Notepad, WordPad and Notepad++ but it won't open in any of these and that's not even through a drag-n-drop but just through a right click and Open or Edit command. There are not many programs that can deal with paths longer than 260 characters and I think that is what is happening here.

 

[Jaafar Tribak]

@MountainMain

Does the Larache link in your signature block mean you live there?

Yes. That's my lovely small home town where I now live

Thanks for all the good points you have raised.

It appears that the maximum length of a path is around 260 characters

Definitely. That's the MAX_PATH and windows won't let you exceed it .

You are using it on the return from the call to DragQueryFileW and in the MS documentation for that function I don't see anything where it says that the buffer for the file path (lpszFile) is a short pathname

True. But it does appear to "randomly" return the path in short format (see second listbox entry below). That's the reason I suggested using GetLongPathNameW. Although I don't think that's the reason for the issue traveller4 is having.

I set a Byte array that I ReDim to 32,768 and then I re-use it over and over to catch the file paths

That should work and is probably more efficient than repeatedly re-allocating buffer memory for each path.

Also, I am guessing your code could be a bit more efficient if you passed back an array of the file paths instead of each one individually.

I wanted to keep the pseudo event as simple and intuitive as possible. I guess passing an array arg to the user would be less intuitive. Sure, the user could always use the List Property for populating the ListBox straight from the passed array instead of using AddItem. However, In order to pass the array to CallByName, the array must be of Variant type which is slower. (I need to use CallByName because the name of the Pseudo-event handler can only be known at runtime since it is the user who is supposed to decide which name to give to the pseudo-event)

Finally, I wanted to make the code more generic so it is not restricted to ListBoxes and can be extended to other windowed controls such as Frames, Multipages and the Form itself all of which don't obviously have AddItem\List Methods. This means, at some point, the client code will most likely somehow end up extracting the individual items from the passed array anyway.

Finally, I am not sure about KeyStates. You make an array for this and pass back a value for each file path. But since it is the same for all file paths retrieved from a drop, why not just make it one value for all of them instead of an array?

Good point. I will make the necessary correction.

Thank you very much.

 

[MountainMain]

Note for non-nerds: The following discussion is all about Windows API calls so don't look for these functions in "normal" VBA code...

You can exceed MAX_PATH with file operations as long as you don't use the Shell, including ShellExecute and ShellExecuteEX. However, there are several funcitons enabling you to use path lengths up to 32,767 characters. If you start a new process with CreateProcessW you can use very long names with the caveat that there is no documented way to start a new process with this with elevated privileges. You can use very long names in CopyFileW, CopyFileExW, CopyDirectoryExW, etc. and you can move/rename files with MovefileW, MoveFileWithProgressW, etc. and you can delete files with DeleteFileW. Then there is GetFileSizeW, GetFileSizeExW, GetFileAttributesW, SetFileAttributesW and so on. I am close to finishing my file manager for VBA and I'll put it out for you guys to see/use but I use all of these functions and more to a) get paths longer than MAX_PATH and b) to use complete Unicode.

This is the way Windows should have been all along but somewhere along the way someone decided to limit Windows Explorer 9which uses Windows Shell) to only allow MAX_PATH and although the core Windows API funcitons can use very long path names, Explorer can't and so over time many (most?) programs decided to conform with that so nowadays not very many programs can deal with anything longer than MAX_PATH. MS is trying to change that as part of Windows 10+ but my guess is that since it takes a registry modification, not many users or companies will allow or do it. The best thing for users and programmers would be to modify the Shell routines to exceed MAX_PATH but apparently there is a fear that this will cause compatibility problems somewhere. Built-in procedures for VBA (and VB6) are all ANSI and as such are not Unicode so they can't deal with very long pathnames either. To get that you have to go to API functions. Once you wrap them in regular functions the API nature is hidden, just like for the drop target code in this thread.

BTW, where did you get the code for the DragDrop using DragAcceptFiles and DragQueryFile? There isn't mcuh on all of that when you Google it.At one time I had thought there was probably a C++ version on PlanetSourceCode but that went offline a couple of years ago. You did some good sleuth work on that!

 

[Jaafar Tribak]

@MountainMain

However, there are several funcitons enabling you to use path lengths up to 32,767 characters

Nice & clear explanation. I was actually reading on the subject which is kind of confusing. Thanks for clearing this up for me.

BTW, where did you get the code for the DragDrop using DragAcceptFiles and DragQueryFile?

From nowhere. I came up with it myself after experimenting with lightweight objects. I saw that the RegisterDragDrop conviniently takes a hwnd which could be easily associated with the lightweight IDropTarget interface. So It thought maybe I could hopefully call its Methods such as the Drop "event" and make it work.... And it did work.

Obviously, I would have no idea were it not for the help of smart and helpful vb6 people in various forums from whom I have learnt a lot (still learning) over the years.

 

[Jaafar Tribak]

@traveler4

I do not see how to "adjust the code accordingly

ShellDragNDrop MultipleListBoxes_V2.xlsm

Please take a look at the new workbook in the above link and see what happens.

Regards.