+ Reply to Thread
Results 1 to 8 of 8

import data from .txt file selected by user

  1. #1
    Registered User
    Join Date
    02-27-2008
    Location
    Irving, Texas
    MS-Off Ver
    MS Office 2016
    Posts
    96

    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
    Last edited by ahartman; 02-27-2008 at 05:09 PM. Reason: clarifying intentions for an all-in-one desired function

  2. #2
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    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.
    Please Read Forum Rules Before Posting
    Wrap VBA code by selecting the code and clicking the # icon or Read This
    How To Cross Post politely

    Top Excel links for beginners to Experts

    If you are pleased with a member's answer then use the Scales icon to rate it
    If my reply has assisted or failed to assist you I welcome your Feedback.

  3. #3
    Registered User
    Join Date
    02-27-2008
    Location
    Irving, Texas
    MS-Off Ver
    MS Office 2016
    Posts
    96

    not quite what I was looking for

    Quote 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.
    Last edited by ahartman; 02-28-2008 at 10:27 AM. Reason: update for another code snippet I found
    -Adam Hartman
    Mechanical Engineer

  4. #4
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    I am not sure what you are after, but the attached link may be worth a look

    http://www.vbaexpress.com/kb/getarticle.php?kb_id=837

    Regards

    Dav

  5. #5
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628
    see attached file... I hope it's what you need.

    Regards,
    Antonio
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    02-27-2008
    Location
    Irving, Texas
    MS-Off Ver
    MS Office 2016
    Posts
    96

    running it now...

    Quote 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.
    Last edited by ahartman; 02-28-2008 at 01:40 PM.

  7. #7
    Registered User
    Join Date
    02-27-2008
    Location
    Irving, Texas
    MS-Off Ver
    MS Office 2016
    Posts
    96

    Working prototype

    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.

  8. #8
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628
    I'm sorry for long time in running code.

    On my company pc (very old and with only 256Mb Ram) it takes 25 seconds for 6000 files.

    Regards,
    Antonio

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1