Calling an MS-DOS command

[Alan Searle]

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

[Br]

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() ?

--
regards,

Br@dley

[Alan Searle]

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:

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() ?

[Br]

Alan Searle wrote:

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.
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

Br@dley schrieb:

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() ?

--
regards,

Br@dley

[Tom van Stiphout]

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

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

-Tom.

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:

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() ?

[Alan Searle]

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:

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

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

-Tom.

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:

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() ?

[Albert D. Kallal]

> 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 ...

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
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[Alan Searle]

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:

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 ...

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