import data from .txt file selected by user

Hi everyone. I've been working on a homebrewed file cataloging system for our design information and have some separate system ideas that I'd like to put all together in Excel.

Here's the grand goal: Make a catalog of all the files and folders, including subdirectories and their contents which has links to each file (folders not necessary) and can be sorted, populated with extra information for the files, and is generic enough to be a click-and-run type system.

Specifically, I'm using the dir DOS command to make the data file. Each line of the file is it's own data entry, so I don't have to worry about delimiters. I'd like to have an excel macro that will bring up the File->Open dialog window and let the user browse to and select the file they want to open.

I can easily use the macro recorder to do this for a specific file, but I can't figure out how to modify things to let it work for any data file the user cares to select. Alternately, I've found code that lets users select and open the data file they want, but it's a specific example with delimiters involved, and I can't figure out how to prune that code to be what I need.

Does anyone have suggestions? Also, could I get the macro to actually execute the .bat file I'm using to create the data file, and then open the file and pull out the data? EDIT: Could it all be rolled together so that the macro asks the user to browse to the directory to be cataloged, creates the correct DOS dir command batch file, executes that batch file, then imports the data? :EDIT

Thanks in advance to anyone who offers their opinions or advice!

-Adam Hartman
Mechanical Engineer
Zyvex Instruments, LLC
Richardson, TX

Here is some examples to get the file name to be opened & file named for file to be saved as.

The 1st example will show all files in a folder

There are other ways of acheiving what you require - I find using one of these methods the easiest to code & work with

Please Login or Register  to view this content.

Originally Posted by mudraker

Here is some examples to get the file name to be opened & file named for file to be saved as.

The 1st example will show all files in a folder

There are other ways of acheiving what you require - I find using one of these methods the easiest to code & work with

Please Login or Register  to view this content.

Thanks for your advice Mudraker, but I'm afraid that's not what I'm looking for. I'd like the code to accept as input a starting directory, preferably selected through the File->Open dialog box. Once the directory is selected, the code should pull all the folders, files, subfolders and subfiles in as text, and put them into the spreadsheet, one on each line, with their full path, filename, and extension.

I _can_ get this data by doing the following command in DOS:

Please Login or Register  to view this content.

And I _can_ run that command through a .bat file, which captures every file down to the last subfolder. So, at the very least, I'd like to make Excel do the following:

1) Let the user browse to a directory to be cataloged.
2) Take the path of the selected directory and use it to make a customized .bat file (replacing "C:\DESIGN_DATA" in the above example w/ whatever the user's selected).
3) Executing that .bat file to build the .txt file containing the full file listing.
4) Import that .txt file into Excel such that each line in the .txt file is one cell in Excel.

I've gleaned some pieces of this functionality from other people's posts here and have the following:

To browse to a file:

Please Login or Register  to view this content.

To make a .bat file:

Please Login or Register  to view this content.

To run a .bat file:

Please Login or Register  to view this content.

However, I've also seen some examples around here of using VB straight in Excel to replicate the DIR type behavior, without needing to resort to a .bat file running DOS commands. My instincts tell me that I'm making this overly complicated by trying to use .bat files, but I don't have th VB experience to get the directory listing, and especially not in a single, one-line command.

Originally Posted by antoka05

see attached file... I hope it's what you need.

Regards,
Antonio

Antonio,
Thanks for your code. It's running now, but seems to be taking a long time. There are about 3000 files in the directory I'm looking at, so maybe it'll take a while to go through them all. If it works, great. If not, I may have to go back to the .bat file idea just because DOS does it so fast.

EDIT It worked brilliantly! All the useful info is there. The run time was long, so I'd still like to hear from anyone who's got ideas about letting DOS do the directory listing to try to make it faster, though.

Everyone,
Thanks to all for your input on my problem. Below is the working prototype of the code which lets me browse to a folder, then creates a .bat file specific to that folder and then runs the file, making a text file containing all the file and folder names below the selected folder. It's probably not very efficient, or clean, or elegant, but it works for me

Please Login or Register  to view this content.