Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2007
    Location
    Harrisburg, PA
    Posts
    44

    Question Unanswered: Reporting data from a folder?

    I want to figure out a way to create an excel file or an access table from taking the names of all the files in a folder and turinging it into a list. I feel like the is very off the wall but it would help out PERFECTLY.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You can use the dir() to make a file of the directory listing. Click Start -> Run then type command (or cmd) to get to the dos shell. Once at the dos shell, you can go to the root folder by entering: cd\ at the prompt (c:\)

    Once at the root folder (or if you want to go to a specific folder, enter cd\<foldername>), then enter: dir >> filename.csv (where filename can be anything you want to call it.) This sends the directory listing to a csv file. You can also enter: dir/s >> filename.csv which sends the directory and sub directories to the filename.csv. You can then open the filename.csv with excel or import into Access (extension can also be .csv, .xls, .txt, etc...) The >> means redirect which essentially means you're redirecting the directory listing to a filename (the good old dos days.) Once filename.csv is created (in the folder your at in the prompt), you can then open windows explorer and you'll see that filename.csv file in that folder.

    In the code bank, http://www.dbforums.com/showpost.php...8&postcount=40 (Storing Files Locations in a table....), I have an Access mdb which utilizes the Dir command and will store all the files from the directory you specify (see PopulateTable form) into a table which you can then utilize (for file type enter *.* for all files or if you want to just store specific file types, enter that file type (i.e. *.doc).)

    Note: if you go to the dos shell with the command, keep in mind that dos shell doesn't like folders with spaces in the name or names longer than 8 characters. You'll most likely need to utilize the 8 character syntax (i.e. cd\FolderNa~1) when in the dos shell. Type: Exit at the prompt to exit the dos shell (or click the X in the upper right to close where you'll get an error which you can click End Now.)
    Last edited by pkstormy; 12-07-07 at 14:04.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Oct 2007
    Location
    Harrisburg, PA
    Posts
    44
    I think that will work but, what if I am trying to get to data on a networked folder? CMD says it does not use UNC paths so I am unsure as to how to get to what I need.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You probably won't be able to do the cmd and go to a networked drive. The app I created in the code bank let's you select any networked drive and will populate the table with the filenames. Try the app in the code bank. You don't need to use all the features of it but it will get the job you need done quickly. Open it with the shift key!!, open the PopulateTable form, and click the button to populate the dbo_LookupFiles table with the filenames. You can then easily export the dbo_LookupFiles table. Other than that, I'm sorry but I don't know any other ways offhand. I always use this to quickly populate filenames of a directory so maybe I've become too accustomed to it but it takes seconds to do it.
    Last edited by pkstormy; 12-07-07 at 17:28.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Oct 2007
    Location
    Harrisburg, PA
    Posts
    44
    that thing is amazing! It did exactly what I needed. I was confused because everything was hidden for awhie... and I must have forgotten to read the shift key thing. Now it is great!!!

    Thanks PK!

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Glad you liked it. Thanks for the feedback!
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Try out the other features if you get a chance.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Andy, please do not cross post. 1 question in 1 topic only. If you're not sure where to put it, give it your best guess and a moderator will move it to the right place if necessary
    George
    Home | Blog

  9. #9
    Join Date
    Dec 2006
    Location
    Momence, IL USA
    Posts
    56

    Using ADO to "query" the file system

    I don't mean to hijack the thread after it seems to be resolved, but my question is relevent and I can't think of a better place to post it. (Feel free to move this.)

    Is there an OleDb provider that lets you retrieve a set of files with a "Select * From Thisfolder" type of query on a directory (maybe Active Directory)?
    I hear that the Internet Publishing Provider, MSDAIPP.dso, can return a recordset containing all the files in an IIS virtual directory. Is there some other directory that contains the file system info, and that can be accessed through ADO?
    Thanks
    Don't Bogart That Squishee!

Posting Permissions

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