Running dos command from access vba

rogerdjr
rogerdjr used Ask the Experts™
on
Trying to run

        Dim RetVal As Long
        RetVal = Shell("for %%i in (C D E F G H I J K L M N O P Q R S T U V W X Y Z) DO @if exist %%i: dir %%i:\*.* /s /b >E:\zzz\Zip-Bak\DayBkUp\DirectoryListHistory\%%i.txt")



Get error run time error 53 file not found?

It runs fine in a dos batch file

Trying to create a series of text files with a list of all files on each drive on my computer

Thanks
DIR-ALL-Drives.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
1. Because "for" is an internal command of cmd.exe, not a stand-alone executable.
2. You're not running this command as a batch file, so you need to use a single percent sign for the loop variable.
Try
"cmd.exe /c for %i in (C D E F G H I J K L M N O P Q R S T U V W X Y Z) DO @if exist %i: dir %i:\*.* /s /b >E:\zzz\Zip-Bak\DayBkUp\DirectoryListHistory\%i.txt"

Open in new window

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
hi Roger,

another option is to use a different way instead. Here is a free tool to load file names and other information into Access:

www.AccessMVP.com/strive4peace/DB_ListFiles.htm


have an awesome day,
crystal

Author

Commented:
Tried single % still get the error message
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
Using single percent signs doesn't help without calling cmd.exe. You would have needed to put the string I provided into the Shell() command.
Here's the full version based on your sample:
Dim RetVal As Long
RetVal = Shell("cmd.exe /c for %i in (C D E F G H I J K L M N O P Q R S T U V W X Y Z) DO @if exist %i: dir %i:\*.* /s /b >E:\zzz\Zip-Bak\DayBkUp\DirectoryListHistory\%i.txt")

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial