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

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
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.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,276
Office Version
  1. 2010
Platform
  1. Windows
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
VBA Code:
TreeView1.OLEDropMode = ccOLEDropManual

then add code for the OLEDragDrop event
VBA Code:
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

Board Regular
Joined
Oct 20, 2017
Messages
166
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
VBA Code:
TreeView1.OLEDropMode = ccOLEDropManual

then add code for the OLEDragDrop event
VBA Code:
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

Well-known Member
Joined
May 20, 2004
Messages
3,276
Office Version
  1. 2010
Platform
  1. Windows
the filename is required for whatever code you are going to implement. what will you save if you dont know its name?
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166

ADVERTISEMENT

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

Well-known Member
Joined
May 20, 2004
Messages
3,276
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Oct 20, 2017
Messages
166

ADVERTISEMENT

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

Well-known Member
Joined
May 20, 2004
Messages
3,276
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Oct 20, 2017
Messages
166
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

Well-known Member
Joined
May 20, 2004
Messages
3,276
Office Version
  1. 2010
Platform
  1. Windows
so you can set the DragDropPath from your TreeView control

VBA Code:
    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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,832
Messages
5,627,150
Members
416,223
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top