Drag and drop file to get its flename/path in Excel

Question

I have a table like so

File    |    Filename     |...
foo     | \a\b\c\foo.txt  |
foo2    | \a\b\c\foo2.txt | 
foo3    | \a\b\c\foo3.txt |

Currently, every time I add a file, I have to manually enter its Filename (path) by SHIFT+ RIGHT CLICKing the file and selecting "Copy as Path", then pasting that in Excel.

Question: Can someone provide a MWE macro in VBA which would allow a user to drag the file, drop it in a cell, then place the filename/path of that file in that cell (NOTE: I am not trying to embed the actual file, just grab its file name/path).

Existing Solution(s): The closest example I found requires weird shortcuts like pressing "\" before dragging and dropping, and also uses additional libraries.

Goal: The goal here is to use Excel for project management. There are existing web interfaces fro Project Management, notably Jira, however, they are not detailed enough. I carry out a great deal of legal and political work, and it is extremely important to know who authored a file, to whom that file was disseminated to, what was the medium of dissemination (e-mail, courier, personal delivery), as well as file version control. I find that, short of enterprise level solutions such as SAP, such a tool does not exist. I currently have work sheets for the different levels of classifications (ie. Project, task, sub task, sub sub task) then I have files associated to one of those classifications. Furthermore, if I e-mail a document, then I have to save the outlook file, bring it into this project management solution and link that also. The same applies to files that are couriered out and I want to keep a record of the FedEx receipt. The problem is that I have to manually copy a file to the main folder, encrypt it, rename it and then copy the file path and paste it into Excel. It is not mission impossible, but when there are 100's of files recorded like this every day, it really becomes a chore. My goal is to drag and drop files and have VBA automate it. I posted this question here because a great number of the Excel questions are asked here. However, given the negative comments and the close requests, I believe such a highly complex programming question might have better been asked on a more advanced sub site like Stack Overflow.

Drag and drop file to get its flename/path in Excel

Solution

1. In the folder where your files reside.
2. SHIFT+Right click on empty white space in directory.
3. Choose: Open command windows here
4. Type this command >: FOR %A IN (*.*) DO ECHO %~fA >> files.txt

There is now a new file created: files.txt

Drag and drop this file onto EXCEL and the names and paths of the files will fill in the cells automatically.

Confused? Follow images.

Comments

[Ravindra Bawane]

"Can someone provide a MWE macro...". Short answer: No. We are not a script-writing service. We are happy to assist with a specific technical issue once you've started building your own solution. However, part of your issue is that you are not adding functions to Excel (something already happens when you drag-drop a file into Excel) you are changing the default behavior. This is almost guaranteed to require additional libraries.

[puk]

@music2myear then could you "assist me with the specific technical issue" of not being able to drop files into Excel and grab their path as I have "started building this as a solution" but I currently am dependent on the OS to give me the path.

[Ravindra Bawane]

Use the EDIT button below your question to adjust/tweak/change so that we can focus on the question.

[yass]

Take a look at VBA drag and drop file to user form to get filename and path stackoverflow question-20569959

[Ƭᴇcʜιᴇ007]

AFAIK there is no cell-level drag-drop event exposed in Excel's VBA, so there's no way to know an object has been dropped onto a cell. At least not in pure VBA. You'd probably need to tap into Win32 (assuming you're using Windows as your OS), or using an intermediate form that contains an ActiveX control.

[puk]

I don't see why people are voting to close this: it is a well formatted question, it will add value to other Excel users who are looking for a similar answer, and all the best answers on StackExchange come with MWEs

[ejbytes]

Not sure what any of that means. But have you tried, or are familiar with, running a dos command? In a directory you could easily get all the files names in current directory with a small command: FOR %A IN (*.*) DO ECHO %~fA >> files.txt Then drag and drop the files.txt onto your Excel file and all the filenames and paths will fill in automatically.

[puk]

@ejbytes I am using this as a project management software and files are added incrementally. My ultimate goal is so that you drop a file into a cell and it reads the file, renames it and drops into into a backend share folder

[Ravindra Bawane]

Forgive my ignorance, but what does "MWE" mean in this context? Also, StackExchange is a developer QA site, and is about writing and critiquing code. Super User is a computer support QA site, and is not about writing and critiquing code.

[puk]

Although I applaud your out-of-the-box approach, and the great explanation, this solution will, ultimately, not work because users of my project management software (in Excel) are constantly dropping files in. As I mentioned in the comments above, my goal is to drop files, read the file name and copy the file to a backend shared folder.

[ejbytes]

@puk Project management for software? Dropping files in? Dropp files to where? And why would you track what files are in a folder? You want to update what's in a folder, in the form of a link, in an Excel spreadsheet, so you know whats in the folder? Perhaps if you explained what the process is use for in your question. I still can't grasp what your goals are. I've worked in projects in software development and I don't really get the goal you seek.

[ejbytes]

@puk I don't want to go out of the scope of community policy. So I'll explain what you can do to help you along. The line argument I posted can be saved to a "batch file" that can be activated and produce the files.txt file at any time. Once you've learned how to create a batch file, you can programExcel (via VBA) to call and run the batch file, then grab Each Line from the files.txt file and insert them automatically into the Excel sheet. It's very simple coding. But you'd have to do some research if none of that is in your arsenal of expertise. We'll HELP if you get stuck.