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

microsoft excelpathurl

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

Best Answer

  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.

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

Related Question