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

jmpatrick

Board Regular
Joined
Aug 17, 2016
Messages
189
Office Version
  1. 365
Platform
  1. Windows
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:

dragndrop.jpg


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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,854
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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

Board Regular
Joined
Aug 17, 2016
Messages
189
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,144,162
Messages
5,722,848
Members
422,460
Latest member
VBA_Noob01

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