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.
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,277
Office Version
  1. 2010
Platform
  1. Windows
it should have the full path. do a debug.print FileName1.Value to check
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
it should have the full path. do a debug.print FileName1.Value to check
That shows it with the .pdf but I am assuming it is because I have set the filename to save it as using UFAddEntry.TextBox2.Value. That said, it's not the end of the world, I will just only use it for PDF files.
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,277
Office Version
  1. 2010
Platform
  1. Windows
i just did a trial with this rudimentary userform and it worked. try setting FileName1 = Data.Files(1) ?

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)
    Label1.Caption = Data.Files(1)
End Sub

Private Sub UserForm_Initialize()
    TreeView1.OLEDropMode = ccOLEDropManual
End Sub
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
i just did a trial with this rudimentary userform and it worked. try setting FileName1 = Data.Files(1) ?

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)
    Label1.Caption = Data.Files(1)
End Sub

Private Sub UserForm_Initialize()
    TreeView1.OLEDropMode = ccOLEDropManual
End Sub
That's how it is already from your initial code.
 

diddi

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

ADVERTISEMENT

post the whole code and i will have a quick look. also gives a solution to the opening post
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
post the whole code and i will have a quick look. also gives a solution to the opening post
My machine has just started a Windows update, so I'll do it in the morning if that's OK.
 

diddi

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

ADVERTISEMENT

haha. and tomorrow comes...
1613177128061.png

Gotta love MS
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
Sorry for the delay, I think you jinxed me, the update did not go according to plan!!!

Anyhow, this is what I currently have:

VBA Code:
Private Sub UserForm_Initialize()
    'Enabes drag/drop element
    TreeView1.OLEDropMode = ccOLEDropManual
End Sub

Code:
Private Sub TreeView1_OLEDragDrop(Data As MSComctlLib.DataObject, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)
    FileName1 = Data.Files(1)
    Dim FSO As Object, ObjFile As Object
End Sub

Code:
Sub SaveFile3()
    Dim FSO As Object, ObjFile As Object

    UpdatePath = "C:\Users\glynn\OneDrive\Documents\Work\Test Upload Files\" & UFAddEntry.TextBox2.Value
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set ObjFile = FSO.GetFile(Trim(FileName1.Value))
    FSO.CopyFile Trim(FileName1.Value), UpdatePath, True
End Sub

This all works with the exception of it not dragging the file extension with it into the new location.

Whilst trying different options, I have also tried the following which moves the file rather than copying it. This also has the same issue in that it doesn't bring the file extension across with it.

Code:
Sub SaveFile4()
    UpdatePath = "C:\Users\glynn\OneDrive\Documents\Work\Test Upload Files\" & UFAddEntry.TextBox2.Value

    'Moves file from temp folder to new folder
    Name UFAddEntry.FileName1 As _
        UpdatePath
End Sub
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,277
Office Version
  1. 2010
Platform
  1. Windows
FileName1 needs to be declared as a global variable within this UF module. ie the Dim should be at the very top before any subs are declared. it is not shared between subs unless you declare it

VBA Code:
Dim FileName1 as string
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
FileName1 needs to be declared as a global variable within this UF module. ie the Dim should be at the very top before any subs are declared. it is not shared between subs unless you declare it

VBA Code:
Dim FileName1 as string
Massive apologies, I thought I had replied to this but clearly not!

I tried declaring the filename but it still wasn't playing ball. I have done it so it will only take a PDF instead which will cover 99.9% of my uploads.

Thank you so much for all your help with this one.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,314
Members
416,239
Latest member
Counselor85027

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