0

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

9
  • 4
    "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. Apr 18 '17 at 19:43
  • 1
    Use the EDIT button below your question to adjust/tweak/change so that we can focus on the question. Apr 18 '17 at 19:53
  • 2
  • 3
    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. Apr 18 '17 at 20:27
  • 1
    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.
    – ejbytes
    Apr 19 '17 at 8:59
1
  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

3
  • 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.
    – puk
    Apr 19 '17 at 15:09
  • @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
    Apr 19 '17 at 19:11
  • 1
    @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.
    – ejbytes
    Apr 19 '17 at 20:13

Not the answer you're looking for? Browse other questions tagged or ask your own question.