Strange Problem doing DOS Dir Command in VBA Help Please

Strange Problem doing DOS Dir Command in VBA Help Please

Post by Bugs » Fri, 26 Aug 2005 20:19:00


Hi Everyone.

This problem is driving me crazy.

Im using Access2003.

Basically what I wish to do is create a new record for each jpg bmp or gif
file in the current directory.

I need to just save the path and filename of each file in a table in its own
record

I am trying to do a DOS command to do a DIR of jpg, bmp and gif files, and
save the output to a file C:\Filelist.txt
using the following line.

Shell (Environ$("COMSPEC") & " /c Dir *.jpg *.bmp *.gif /S /B >
C:\Filelist.txt")

I then try to read that file into the Imagepath field with VBA doing the
following:

Dim Q as string

Open "C:\Filelist.txt" For Input As #1

While Not EOF(1)

DoCmd.GoToRecord , , acNewRec
Line Input #1, Q
Imagepath = Q


Wend

Close#1



Works OK first time used, creates a set of new records, one for each jpg bmp
and gif file in the directory. Bewdy.

But on second and subsequent uses (and a change of the current directory) it
creates a duplicate of the previously created set of records instead of the
contents of C:\Filelist.txt (ie Pass1 creates records OK, Pass2 creates same
records as Pass1, Pass3 creates same records as pass2 etc)

I checked C:\Filelist.txt and it has been created successfully each time

Yet VBA reads its contents incorrectly. Seems to ignore the fact that
C:\Filelist.txt has been overwritten.Always seems to
be one pass behind.

I have even tried using a macro to import C:\Filelist.txt as a table and do
an append query but same problem.

The behaviour of C:\Filelist.txt is odd.


Any help would be extremely appreciated.


Thank you.

Joe.
 
 
 

Strange Problem doing DOS Dir Command in VBA Help Please

Post by Allen Brow » Fri, 26 Aug 2005 22:09:44

It would be much easier to use the Dir function in VBA to get the names of
the files in the folder than to shell out to the command prompt and handle a
text file.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://www.yqcomputer.com/
Reply to group, rather than allenbrowne at mvps dot org.

 
 
 

Strange Problem doing DOS Dir Command in VBA Help Please

Post by Terry Kref » Sat, 27 Aug 2005 00:27:32

Shell is an asynchronous call. Follow Allens suggestion and use the Dir
function instead.

--
Terry Kreft
MVP Microsoft Access




own
bmp
it
the
same
do
 
 
 

Strange Problem doing DOS Dir Command in VBA Help Please

Post by Bugs » Sat, 27 Aug 2005 19:42:43

Hi again

That solution worked a treat. Thank you all so much for your help.

Cheers.

Joe.






gif
and
directory)