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.
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
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
I'm so sorry for being a pain. I take it the UpdatePath is where I want to store them in the end, not on the desktop? How do I set the DragDropPath in the TreeControl? It currently errors with "Run-time error 53: File not found" and stops on line Set ObjFile = FSO.GetFile(UpdatePath)
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,267
Office Version
  1. 2010
Platform
  1. Windows
put a file on the desktop. then drag it to your treeview control. it will trigger an event which will tell you the file path. yes update path is your folder where you want to store everything. also make sure the destination folder exists
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
put a file on the desktop. then drag it to your treeview control. it will trigger an event which will tell you the file path. yes update path is your folder where you want to store everything. also make sure the destination folder exists
I created a textbox on the userform called MyFileName where the file path is showing, is this correct? I just can't work out where to reference the MyFileName textbox to use the path that's in it. I also want to choose the filename that is saved as based on other textbox's on the userform, is that possible?
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,267
Office Version
  1. 2010
Platform
  1. Windows
if textbox1 has the directory, and textbox 2 has the file name you want to use...

VBA Code:
UpdatePath=TextBox1.value & "\" & textbox2.value
FSO.CopyFile MyFileName.Value, trim(UpdatePath), True
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166

ADVERTISEMENT

if textbox1 has the directory, and textbox 2 has the file name you want to use...

VBA Code:
UpdatePath=TextBox1.value & "\" & textbox2.value
FSO.CopyFile MyFileName.Value, trim(UpdatePath), True
That makes sense. It is still giving me a "Run-time 53: File not found" on this line Set ObjFile = FSO.GetFile(UpdatePath)

This is the code I have on it:

VBA Code:
Sub SaveFile()

Dim FSO As Object, ObjFile As Object

    UpdatePath = "C:\Users\glynn\OneDrive\Documents\Work\Test Upload Files\" & UFAddEntry.TextBox2.Value  ' path you want to store your attachments
        Debug.Print UpdatePath
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set ObjFile = FSO.GetFile(UpdatePath)
    FSO.CopyFile Trim(FileName1.Value), UpdatePath, True

End Sub

The path to "Test Upload Files" has been created and is accessible so I can't work out what is the issue.
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
I changed MyFileName textbox to FileName1 to prevent some confusion later.
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,267
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

try saving it to your PC, not a cloud service. i had trouble with that code on cloud
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
try saving it to your PC, not a cloud service. i had trouble with that code on cloud
Sorry for the delay, I had to go out. I've just changed it to my desktop but still getting the same error.
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,267
Office Version
  1. 2010
Platform
  1. Windows
maybe try it with this. was not thinking right last night clearly

VBA Code:
Sub SaveFile()

Dim FSO As Object, ObjFile As Object

UpdatePath = "C:\Users\glynn\OneDrive\Documents\Work\Test Upload Files\" & UFAddEntry.TextBox2.Value ' path you want to store your attachments
Debug.Print UpdatePath
Set FSO = CreateObject("Scripting.FileSystemObject")
Set ObjFile = FSO.GetFile(Trim(FileName1.Value))
FSO.CopyFile Trim(FileName1.Value), UpdatePath, True

End Sub
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
maybe try it with this. was not thinking right last night clearly

VBA Code:
Sub SaveFile()

Dim FSO As Object, ObjFile As Object

UpdatePath = "C:\Users\glynn\OneDrive\Documents\Work\Test Upload Files\" & UFAddEntry.TextBox2.Value ' path you want to store your attachments
Debug.Print UpdatePath
Set FSO = CreateObject("Scripting.FileSystemObject")
Set ObjFile = FSO.GetFile(Trim(FileName1.Value))
FSO.CopyFile Trim(FileName1.Value), UpdatePath, True

End Sub
I know that feeling well.

That seems to do the trick, thanks. Is it supposed to transfer the file extension across? The file I have dragged across is a pdf but when it saves, it does not have the .pdf extension on it so needs to have a program chosen to open it. I will predominantly doing this for PDF's so if it is a pain I will just add the .pdf to the filename and if it is another file type I will do it manually.

Thanks for all your help, it's been a massive help.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,339
Messages
5,624,101
Members
416,011
Latest member
chengkoonwing

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