My craziest idea yet! Drag and Drop files to UserForm to rename and file them...

[jmpatrick]

One of our most time-intensive tasks is downloading PDF files, renaming them and moving them to the correct folder on our server. It's a slow process since the naming convention must be followed exactly in order for our spreadsheet links to work. As I pondered making this a more efficient process I started imagining a UserForm that had list boxes with all the necessary data needed to name the PDF files. Below that is a series of frames or hotspots, one for each file category. With the UserForm open the user would simply drag and drop the PDF file to the correct hotspot and the macro would automatically rename the file and place it in the correct folder based on information entered in the text boxes. It would look something like this:

In this example, any PDF file dragged and dropped to the Drag Plot Plan Here frame would be moved to a folder and renamed based on the information in the three text boxes:

\\SERVER\Builder\Forest_Hills_1119\FH_069\FH_069_plot_plan.pdf

I have written the macro to create the link based on the list box data, what I need is the actual drag and drop component so that a file dragged to the hotspot would trigger the macro and start the magic.

Thanks for reading!

jp

 

[bobsan42]

Check this thread for obtaining the file name:
VBA drag and drop file to user form to get filename and path
The rest should be a piece o'cake.

 

[bobsan42]

BTW, what do you want to drag?
Is it a file from a folder, or from an email?
Or is it a hyperlink which needs to be downloaded? If this is the case things will be much simpler. A textbox on a userform will accept a hyperlink dropped onto it? Then it may be (probably) much simpler to download the file and save it to a specified location.

 

[jmpatrick]

BTW, what do you want to drag?
Is it a file from a folder, or from an email?
Or is it a hyperlink which needs to be downloaded? If this is the case things will be much simpler. A textbox on a userform will accept a hyperlink dropped onto it? Then it may be (probably) much simpler to download the file and save it to a specified location.

Very simple. I need to drag an already downloaded PDF (that has a random name like FH69PLOTPLANREVISED.pdf) to the Drag Plot Plan Here frame and have a macro rename it based on the list box data on the UserForm, then move it in to the correct folder.

I have a macro that will properly format the file name, and another that will move it to the correct folder. I just need ability to drag and drop on to the UserForm frame and have the macro fire.

 

[bobsan42]

in addition to the link above, check this one: VBA to drag and drop file to UserForm then save it using data from UserForm fields