I would like to run an MS-DOS command from Access (VBA) in order to read
a directory tree (into an external file). The command works OK from the
command (DOS) window (in XP) but I can't seem to get it to work from
MS-Access (2003). I tried a simplified command, DIR, but also this
wouldn't work.

I have been using the following syntax:

Shell("DIR")

.... which is maybe wrong?

I'm sure that this is a simple thing and am hoping that someone out
there can give me a quick tip?

Many thanks,
Alan Searle

Alan Searle wrote:[color=blue]
> I would like to run an MS-DOS command from Access (VBA) in order to
> read a directory tree (into an external file). The command works OK
> from the command (DOS) window (in XP) but I can't seem to get it to
> work from MS-Access (2003). I tried a simplified command, DIR, but
> also this wouldn't work.
>
> I have been using the following syntax:
>
> Shell("DIR")
>
> ... which is maybe wrong?
>
> I'm sure that this is a simple thing and am hoping that someone out
> there can give me a quick tip?
>
> Many thanks,
> Alan Searle[/color]

Can't you use repeated calls to Dir() ?

--
regards,

Br@dley

Hi Br@dley,

Yes, I have been using dir() but want to retrieve a whole directory tree
and have found that there is a rather neat MS-DOS one-liner that will do
all the leg-work for me. This is ...

dir c:\mydir /a:d /b /s>dirs.txt

i.e. it will throw the whole directory tree for the base directory
c:\mydir out to the text file dirs.txt.

If I can get it to run from VBA, then it will save me a lot of coding.
All I need to know is how to call DOS from VBA: the shell() command
doesn't seem to work.

Or maybe you know of a better/simplier way for 'grabbing' full listings
of directory trees in VBA?

Many thanks,
Alan.

Br@dley schrieb:[color=blue]
> Alan Searle wrote:
>[color=green]
>>I would like to run an MS-DOS command from Access (VBA) in order to
>>read a directory tree (into an external file). The command works OK
>>from the command (DOS) window (in XP) but I can't seem to get it to
>>work from MS-Access (2003). I tried a simplified command, DIR, but
>>also this wouldn't work.
>>
>>I have been using the following syntax:
>>
>>Shell("DIR")
>>
>>... which is maybe wrong?
>>
>>I'm sure that this is a simple thing and am hoping that someone out
>>there can give me a quick tip?
>>
>>Many thanks,
>>Alan Searle[/color]
>
>
> Can't you use repeated calls to Dir() ?
>[/color]

Alan Searle wrote:[color=blue]
> Hi Br@dley,
>
> Yes, I have been using dir() but want to retrieve a whole directory
> tree and have found that there is a rather neat MS-DOS one-liner that
> will do all the leg-work for me. This is ...
>
> dir c:\mydir /a:d /b /s>dirs.txt
>
> i.e. it will throw the whole directory tree for the base directory
> c:\mydir out to the text file dirs.txt.
>
> If I can get it to run from VBA, then it will save me a lot of coding.
> All I need to know is how to call DOS from VBA: the shell() command
> doesn't seem to work.
>
> Or maybe you know of a better/simplier way for 'grabbing' full
> listings of directory trees in VBA?
>
> Many thanks,
> Alan.[/color]

Not sure if this will help....

My guess would be to use the FileSystem object.

http://www.mcse.ms/archive147-2004-6-818078.html

http://pubs.logicalexpressions.com/p...cle.asp?ID=410



[color=blue]
> Br@dley schrieb:[color=green]
>> Alan Searle wrote:
>>[color=darkred]
>>> I would like to run an MS-DOS command from Access (VBA) in order to
>>> read a directory tree (into an external file). The command works OK
>>> from the command (DOS) window (in XP) but I can't seem to get it to
>>> work from MS-Access (2003). I tried a simplified command, DIR, but
>>> also this wouldn't work.
>>>
>>> I have been using the following syntax:
>>>
>>> Shell("DIR")
>>>
>>> ... which is maybe wrong?
>>>
>>> I'm sure that this is a simple thing and am hoping that someone out
>>> there can give me a quick tip?
>>>
>>> Many thanks,
>>> Alan Searle[/color]
>>
>>
>> Can't you use repeated calls to Dir() ?[/color][/color]

--
regards,

Br@dley

On Tue, 21 Feb 2006 11:39:53 +0100, Alan Searle
<aj_searle@xxxyahoo.com> wrote:

How about:
Shell "cmd.exe /c <yourcommand>"

-Tom.

[color=blue]
>Hi Br@dley,
>
>Yes, I have been using dir() but want to retrieve a whole directory tree
>and have found that there is a rather neat MS-DOS one-liner that will do
>all the leg-work for me. This is ...
>
>dir c:\mydir /a:d /b /s>dirs.txt
>
>i.e. it will throw the whole directory tree for the base directory
>c:\mydir out to the text file dirs.txt.
>
>If I can get it to run from VBA, then it will save me a lot of coding.
>All I need to know is how to call DOS from VBA: the shell() command
>doesn't seem to work.
>
>Or maybe you know of a better/simplier way for 'grabbing' full listings
>of directory trees in VBA?
>
>Many thanks,
>Alan.
>
>Br@dley schrieb:[color=green]
>> Alan Searle wrote:
>>[color=darkred]
>>>I would like to run an MS-DOS command from Access (VBA) in order to
>>>read a directory tree (into an external file). The command works OK
>>>from the command (DOS) window (in XP) but I can't seem to get it to
>>>work from MS-Access (2003). I tried a simplified command, DIR, but
>>>also this wouldn't work.
>>>
>>>I have been using the following syntax:
>>>
>>>Shell("DIR")
>>>
>>>... which is maybe wrong?
>>>
>>>I'm sure that this is a simple thing and am hoping that someone out
>>>there can give me a quick tip?
>>>
>>>Many thanks,
>>>Alan Searle[/color]
>>
>>
>> Can't you use repeated calls to Dir() ?
>>[/color][/color]

Ah-ha, yes, this is exactly what I need.

Thanks very much Tom.

It's simple really, isn't it? :-)

Cheers,
Alan.

Tom van Stiphout schrieb:[color=blue]
> On Tue, 21 Feb 2006 11:39:53 +0100, Alan Searle
> <aj_searle@xxxyahoo.com> wrote:
>
> How about:
> Shell "cmd.exe /c <yourcommand>"
>
> -Tom.
>
>
>[color=green]
>>Hi Br@dley,
>>
>>Yes, I have been using dir() but want to retrieve a whole directory tree
>>and have found that there is a rather neat MS-DOS one-liner that will do
>>all the leg-work for me. This is ...
>>
>>dir c:\mydir /a:d /b /s>dirs.txt
>>
>>i.e. it will throw the whole directory tree for the base directory
>>c:\mydir out to the text file dirs.txt.
>>
>>If I can get it to run from VBA, then it will save me a lot of coding.
>>All I need to know is how to call DOS from VBA: the shell() command
>>doesn't seem to work.
>>
>>Or maybe you know of a better/simplier way for 'grabbing' full listings
>>of directory trees in VBA?
>>
>>Many thanks,
>>Alan.
>>
>>Br@dley schrieb:
>>[color=darkred]
>>>Alan Searle wrote:
>>>
>>>
>>>>I would like to run an MS-DOS command from Access (VBA) in order to
>>>>read a directory tree (into an external file). The command works OK
>>>
>>>>from the command (DOS) window (in XP) but I can't seem to get it to
>>>
>>>>work from MS-Access (2003). I tried a simplified command, DIR, but
>>>>also this wouldn't work.
>>>>
>>>>I have been using the following syntax:
>>>>
>>>>Shell("DIR")
>>>>
>>>>... which is maybe wrong?
>>>>
>>>>I'm sure that this is a simple thing and am hoping that someone out
>>>>there can give me a quick tip?
>>>>
>>>>Many thanks,
>>>>Alan Searle
>>>
>>>
>>>Can't you use repeated calls to Dir() ?
>>>[/color][/color]
>
>[/color]

> Yes, I have been using dir() but want to retrieve a whole directory tree[color=blue]
> and have found that there is a rather neat MS-DOS one-liner that will do
> all the leg-work for me. This is ...[/color]

Well, you can use dir..and some recusrion to traverse the tree....

Here is my code...

The first sub shows how to use the routine....


Sub dirTest()

Dim dlist As New Collection
Dim startDir As String
Dim i As Integer

startDir = "C:\access\"
Call FillDir(startDir, dlist)

MsgBox "there are " & dlist.Count & " in the dir"

' lets printout the stuff into debug window for a test

For i = 1 To dlist.Count
Debug.Print dlist(i)
Next i

End Sub


Sub FillDir(startDir As String, dlist As Collection)

' build up a list of files, and then
' add add to this list, any additinal
' folders

Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant

strTemp = Dir(startDir)

Do While strTemp <> ""
dlist.Add startDir & strTemp
strTemp = Dir
Loop

' now build a list of additional folders
strTemp = Dir(startDir & "*.", vbDirectory)

Do While strTemp <> ""
If (strTemp <> ".") And (strTemp <> "..") Then
colFolders.Add strTemp
End If
strTemp = Dir
Loop

' now process each folder (recursion)
For Each vFolderName In colFolders
Call FillDir(startDir & vFolderName & "\", dlist)
Next vFolderName

End Sub


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.members.shaw.ca/AlbertKallal

Hi Albert,

Thanks very much for this. I'll give it a try and, indeed, it may be
better to use your code than to call DOS commands. I'll weigh up the
pros and cons.

Regards,
Alan.

Albert D. Kallal schrieb:[color=blue][color=green]
>>Yes, I have been using dir() but want to retrieve a whole directory tree
>>and have found that there is a rather neat MS-DOS one-liner that will do
>>all the leg-work for me. This is ...[/color]
>
>
> Well, you can use dir..and some recusrion to traverse the tree....
>
> Here is my code...
>
> The first sub shows how to use the routine....
>
>
> Sub dirTest()
>
> Dim dlist As New Collection
> Dim startDir As String
> Dim i As Integer
>
> startDir = "C:\access\"
> Call FillDir(startDir, dlist)
>
> MsgBox "there are " & dlist.Count & " in the dir"
>
> ' lets printout the stuff into debug window for a test
>
> For i = 1 To dlist.Count
> Debug.Print dlist(i)
> Next i
>
> End Sub
>
>
> Sub FillDir(startDir As String, dlist As Collection)
>
> ' build up a list of files, and then
> ' add add to this list, any additinal
> ' folders
>
> Dim strTemp As String
> Dim colFolders As New Collection
> Dim vFolderName As Variant
>
> strTemp = Dir(startDir)
>
> Do While strTemp <> ""
> dlist.Add startDir & strTemp
> strTemp = Dir
> Loop
>
> ' now build a list of additional folders
> strTemp = Dir(startDir & "*.", vbDirectory)
>
> Do While strTemp <> ""
> If (strTemp <> ".") And (strTemp <> "..") Then
> colFolders.Add strTemp
> End If
> strTemp = Dir
> Loop
>
> ' now process each folder (recursion)
> For Each vFolderName In colFolders
> Call FillDir(startDir & vFolderName & "\", dlist)
> Next vFolderName
>
> End Sub
>
>[/color]