[AccessD] Running a Dir command from within Access vba

Jim Lawrence accessd at shaw.ca
Sun Mar 12 14:55:43 CDT 2017


I do think running at the command prompt is by far the most inclusive and fastest.

Jim

----- Original Message -----
From: "jack drawbridge" <jackandpat.d at gmail.com>
To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com>
Sent: Sunday, March 12, 2017 12:46:01 PM
Subject: Re: [AccessD] Running a Dir command from within Access vba

Thanks Doug.
I don't want to go through the directories and subs - necessarily.
I'm trying to help someone who has many 1000's of images.

I can get the list if I use the command prompt and manually enter  the dir
with parms.

After searching, I think I need a bat file.

I have
@ECHO OFF
CHDir c:\users\mellon\documents\TestImages
DIR /s/b *.jpg >c:\users\mellon\documents\TestImages\MyLatestJpgs.txt
CLS
EXIT
as a bat file MyDir.bat.

It works from the command line when I use
c:\users\mellon\Documents>  MyDir

But it doesn't work when I use the following Shell
 Sub TestShellDir()
    Dim retval

10  On Error GoTo TestShellDir_Error
20  Kill "C:\users\mellon\documents\TestImages\MyLatestJpgs.txt"
30  retval = Shell("cmd   c:\users\mellon\documents\MyDir.bat",
vbNormalFocus)


50  DoEvents
60  Debug.Print "Image File List created for Folder and Files   " & Now
70  On Error GoTo 0
80
90  Exit Sub

I've tried various things but no luck. It opens the command prompt window,
but doesn't create the output file. If I copy the Dir line from the bat
file, and paste it into the command prompt, it works???

I was hoping to be able to generate a file list from his multiple folders,
subfolders.

jack

On Sun, Mar 12, 2017 at 3:07 PM, Doug Steele <dbdoug at gmail.com> wrote:

> Here is some butchered code I pulled out of an old database.  It does a
> recursive search to get all file names down through all subfolders of the
> starting folder.  The original code did a bunch of processing on each file
> name found; I've cut this code out and hopefully left a skeleton that you
> can understand. You need to set a reference to the Microsoft Scripting
> Runtime to get access to properties like .files, .subfolders etc.
>
> Public Sub GetFiles(strStartingPath, strPath)
>
>     Dim File
>     Dim Subfols
>     Dim SubFol
>     Dim strSubFolderPath As String
>     Dim CurrentPath As String
>     Dim CurrentFile As String
>     Dim FullPathFile As String
>     Dim files
>
>     Set files = strStartingPath.files
>
>     For Each File In files
>
>         'remove any trailing backslash in MyPath name
>         If Right(strPath, 1) = "\" Then strPath = Left(strPath,
> Len(strPath) - 1)
>                 CurrentFile = File.Name
>                 'add processing for this File.Name, like writing it to a
> table
>                 CurrentPath = strPath
>                 FullPathFile = CurrentPath & "\" & CurrentFile
>     Next
>
>     'recursive search on subfolders
>     Set Subfols = strStartingPath.subfolders
>     For Each SubFol In Subfols
>         If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
>         strSubFolderPath = strPath & SubFol.Name
>         GetFiles SubFol, strSubFolderPath
>
>     Next
>
> End Sub
>
>
> On Sun, Mar 12, 2017 at 9:51 AM, jack drawbridge <jackandpat.d at gmail.com>
> wrote:
>
> > Hi again,
> >
> > Does anyone have sample vba to run a Dir command?
> > I'm trying to get a listing of all *.jpg files from a directory and its
> > subdirectories and put the list in a text file in a specific directory.
> >
> > I can get this to work going to command prompt and entering the string
> > directly, but I want to do this from inside Access.
> >
> > cd  \users\mellon\documents\testImages Dir \s\b *.jpg >MyJpgs.txt
> >
> > Thanks,
> > jack
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


More information about the AccessD mailing list