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]

it should have the full path. do a debug.print FileName1.Value to check

 

[K1600]

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]

i just did a trial with this rudimentary userform and it worked. try setting FileName1 = Data.Files(1) ?

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]

i just did a trial with this rudimentary userform and it worked. try setting FileName1 = Data.Files(1) ?

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]

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

 

[K1600]

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]

haha. and tomorrow comes...

Gotta love MS

 

[K1600]

Sorry for the delay, I think you jinxed me, the update did not go according to plan!!!

Anyhow, this is what I currently have:

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

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

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.

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]

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

Dim FileName1 as string

 

[K1600]

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

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.