VBA to drag and drop file to UserForm then save it using data from UserForm fields

[K1600]

Is it possible to drag & drop a file from an e-mail (Microsoft Outlook if that matters) onto a UserForm and then save the file in a new folder with the folder and filename created from data inputted in other fields in the UserForm?

What I basically want is to drag a file into one of three drag/drop zones on UserForm UFAddEntry, depending on what the file is. Then when I click the CmdAdd command button, as well as submitting the data entered on the form into my spreadsheet, it also creates a new folder using the TxtDate textbox (but reversing the date and placing a . in the place of a / ) and then the value from TxtVRM textbox, each separated by " - " . The filename would be the same as the folder name but with "Report Type 1", "Report Type 2" or "Report Type 3" after the TxtVRM data depending which of the three drag/drop zones the file was dropped in to. The initial destination path for the folder to be placed in will always remain the same.

So it would then look like this:

Folder: 2021.02.11 - A123ABC
Filename: 2021.02.11 - A123ABC - Report Type 1

Thinking about it now, I could obviously do with a way of checking that the folder or file path don't already exist too.

Thanks in advance.

 

[diddi]

you can obtain the filename of an item drag dropped onto a user form by using the treeview control. you will need to add it as a reference

initialise the control on userform startup using

TreeView1.OLEDropMode = ccOLEDropManual

then add code for the OLEDragDrop event

Private Sub TreeView1_OLEDragDrop(Data As MSComctlLib.DataObject, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)
    MyFileName = Data.Files(1)
End Sub

i use this for adding photos to work orders managed by an excel program

 

[K1600]

you can obtain the filename of an item drag dropped onto a user form by using the treeview control. you will need to add it as a reference

initialise the control on userform startup using

TreeView1.OLEDropMode = ccOLEDropManual

then add code for the OLEDragDrop event

Private Sub TreeView1_OLEDragDrop(Data As MSComctlLib.DataObject, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)
    MyFileName = Data.Files(1)
End Sub

i use this for adding photos to work orders managed by an excel program

Unless I am misunderstanding, I don't think I need to find the filename.

The file would have been sent to me in an e-mail and I want to drag it from the e-mail onto the UserForm and to then save it on my computer.

 

[diddi]

the filename is required for whatever code you are going to implement. what will you save if you dont know its name?

 

[K1600]

the filename is required for whatever code you are going to implement. what will you save if you dont know its name?

Ah ok, so I would need to get the filename of it in the e-mail which I am guessing will be in a temp folder somewhere which I would obtain using your code and then write code to save it from the temp folder to where I want it? Sorry if I seem a bit thick with this, I have never had any experience of drag/drop before so not sure how the concept works.

Thanks for your help.

 

[diddi]

all drag drop does is send the event to your userform that a file named X has been dropped there. it is up to you to open / store / manipulate it.
so you might set up a file scripting object and use it to copy the file to another location. in my application i copy images from users phone to a folder on PC then attach an index number so i can review the images when looking at a job order.
i dont know what you will get if you drag drop an attachment from inside outlook. but hopefully you are correct that it points to Users/temp folder somewhere

 

[K1600]

all drag drop does is send the event to your userform that a file named X has been dropped there. it is up to you to open / store / manipulate it.
so you might set up a file scripting object and use it to copy the file to another location. in my application i copy images from users phone to a folder on PC then attach an index number so i can review the images when looking at a job order.
i dont know what you will get if you drag drop an attachment from inside outlook. but hopefully you are correct that it points to Users/temp folder somewhere

Yep, got you now. I've managed to add that and it works for a file already on the computer when dragged from windows explorer but not from e-mail. When I drag it across from the e-mail it errors with 'Run-time error 461: Specified format doesn't match format of data' on the MyFileName = Data.Files(1) row.

If I can sort this bit, do you know how I then get it to save the file to where I want it?

 

[diddi]

ok i suspected it would do that. you will need to dump the attachments onto the desktop or where ever. then move them to your filing system using vba. so open email. dump file to desktop. then dragdrop to your userform, at which point you can relocate them

i dont use outlook

 

[K1600]

ok i suspected it would do that. you will need to dump the attachments onto the desktop or where ever. then move them to your filing system using vba. so open email. dump file to desktop. then dragdrop to your userform, at which point you can relocate them

i dont use outlook

I had a feeling that might be the case, that's not the end of the world to be fair and will still make my process much quicker. Do you know what I code I need to add to save the file from the file path I have now got?

 

[diddi]

so you can set the DragDropPath from your TreeView control

    Dim FSO As Object, ObjFile as Object

    UpdatePath=  ' path you want to store your attachments
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = FSO.GetFile(UpdatePath)
    FSO.CopyFile DragDropPath, UpdatePath, True
    FSO.DeleteFile DragDropPath