🚀 go-pugleaf

If you use VB6 look at the 'new" systemfile object
Steven Reid wrote in message
<#FFYc5LC#GA.187@uppssnewspub04.moswest.msn.net>...
>Hi Everybody,
>
>Could someone tell me a good way in VBA of getting a list of files that
>exist
>within a directory tree within Excel 95.
>
>This could be from one to "lots" of sub directories and multiple levels
>deep.
>
>I am trying to get the details of all files below a certain directory and
>then compare that to a master list of files.
>
>Thanks in Advance
>
>

From a VBA standpoint, you can get this information by calling the DIR
function repeatedly. After getting the files in a particular directory, you
could call it again with the vbDirectory attribute to get a list of
subdirectories and start the whole process over again. Probably kind of
messy, I would think, but it could be done. I think the vb.general folks
will have a good number of solutions of varying use to you within Excel. It
may depend on whether or not you want to use third-party
tools/libraries/controls.

If you're not on a Mac and you're not going to be, and depending on what you
want to do with it - meaning how much parsing you're willing to undertake -
it may be simpler to issue a "DIR /S /B > <filename.txt>" command and parse
the resulting file. You could import that directly into Excel and work with
it pretty easily. Using the /B parameter ("bare format") is the key - it
does not include headers or summary totals, and it includes only the file
name, making it usable on Win 98 and Win NT (didn't test Win95), where the
results of the DIR command are different without the /B parameter.
Interestingly - whether it's good or bad depends on what you're doing -
using the /B and /S parameters together also includes the full path to the
files. If you're going to try this method, you'll need to use the SHELL
method in VBA, and you'll need to loop until the file is closed and
available, since SHELL runs asynchronously.


HTH -

-Frank
Microsoft Excel MVP
Dolphin Technology Corp.
http://isaacsf.home.mindspring.com/vba


Steven Reid wrote in message
<#FFYc5LC#GA.187@uppssnewspub04.moswest.msn.net>...
>Hi Everybody,
>
>Could someone tell me a good way in VBA of getting a list of files that
>exist
>within a directory tree within Excel 95.
>
>This could be from one to "lots" of sub directories and multiple levels
>deep.
>
>I am trying to get the details of all files below a certain directory and
>then compare that to a master list of files.
>
>Thanks in Advance
>
>

You can do this in pure VB code, but it's a little tricky because the Dir()
function can't be used recursively.  So you use Dir() to create an array of
top level dirs, and then search within those for more dirs, etc, until you
have a complete tree.  Now you can use that array to search for files, again
using Dir().

There are several API examples in the various VB pages on the web... try
www.mvps.org/vbnet for starters.  Since the API can be used recursively,
it's more straightforward code, and faster too.

If you'll accept a commercial solution, take a look at my company's Stamina
library.  It has several functions to read directory contents (among 370+
routines written in ASM for VB and VBA).  The most relevant to your question
is probably DxDirTree, used with DxPathFromTree.  It returns an array of a
UDT containing complete information about all the files and dirs in a tree,
starting at any point you specify.  Then there's DxSearchVolume, which
returns the full path name to all files on a drive (or any subtree) which
match a passed spec.

--
     Jim Mack
     MicroDexterity, Inc

     http://www.microdexterity.com

     Sales:  888-891-0700      sales@mdxi.com
     Support: +1 734-453-5872  support@mdxi.com


Steven Reid wrote in message
<#FFYc5LC#GA.187@uppssnewspub04.moswest.msn.net>...
>Hi Everybody,
>
>Could someone tell me a good way in VBA of getting a list of files that
>exist
>within a directory tree within Excel 95.
>
>This could be from one to "lots" of sub directories and multiple levels
>deep.
>
>I am trying to get the details of all files below a certain directory and
>then compare that to a master list of files.

If you want to see a very well done series of macros that accomplishes
this, do a DejaNews Power Search (www.dejanews.com/home_ps.shtml),
specifying the following search criteria:

   Forum: microsoft.public.excel.programming
   Author: Sadler
   Date From: Apr 15 1998
   Date To: Apr 15 1998

Beyond the above info, you need not specify anything else, including a
string to search for.  Just press the Find button and you'll be taken to
his April 15 posting, which was a very well-thought-out answer to a
question of mine in which I wanted to know how to return a list of all
Excel files on an entire hard drive, including all directories and
subdirectories.  You'll probably have to modify the code to meet your
needs, but it's worth looking at.

Regards,
L. Eric Galvez
--
To reply by e-mail, remove "IDetestSPAM"



Steven Reid <Steven_Reid@iName.com> wrote in article
<#FFYc5LC#GA.187@uppssnewspub04.moswest.msn.net>...
Hi Everybody,

Could someone tell me a good way in VBA of getting a list of files that
exist
within a directory tree within Excel 95.

This could be from one to "lots" of sub directories and multiple levels
deep.

I am trying to get the details of all files below a certain directory and
then compare that to a master list of files.

Thanks in Advance