Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Microsoft Access 2010 -- vba Batch file/fso scripting

    I'm not sure if this is the correct forum for this post, if not, please let me know where it should go and I will close this post and open in the correct forum.

    I have a batch file that works perfectly that I call from Access vba:
    Code:
    Call Shell(Environ$("COMSPEC") & " /c  \\MyPath\GetFileNames_v1.bat", vbNormalFocus)
    I would prefer it to be fso scripting but not quite sure how to write the code. None-the-less, I would like to modify the code to capture the path and file size also, can someone assist me with this please?

    Code:
    @ECHO OFF
    
    CLS
    ECHO.
    ECHO EXTRACT FILE NAMES JOB...
    ECHO.
    SET /P confirmRun=ARE YOU SURE TO CONTINUE(Y/N)?:
    IF "%confirmRun%"=="" GOTO Error
    IF "%confirmRun%"=="N" GOTO Error
    ECHO.
    SET /P sourcePath=KEY IN THE SOURCE PATH:
    ECHO.
    SET /P destPath=KEY IN THE DESTINATION PATH:
    ECHO.
    SET /P outPutFileName=KEY IN THE OUTPUT FILENAME(ex: FileName.txt):
    ECHO.
    ECHO CHECKING FOR DIRECTORIES...
    ECHO.
    IF exist "%sourcePath%" (ECHO %sourcePath% FOUND) ELSE ( 
    ECHO %sourcePath% NOT FOUND
    GOTO Error
    )
    ECHO.
    IF exist "%destPath%" (ECHO %destPath% FOUND ALREADY) ELSE (
    mkdir "%destPath%" 
    ECHO %destPath% CREATED
    )
    SET "outputFileFullName=%destPath%\%outPutFileName%"
    ECHO.
    ECHO COMPLETED CHECKING DIRECTORIES!!!
    
    ECHO.
    ECHO PREPARING RESULT...
    
    for /b /s "%sourcePath%" %%i in (*.*) do @echo %%~nxi >>"%outputFileFullName%"
    
    ECHO CHECK RESULT FILE HERE...
    ECHO %outputFileFullName%
    ECHO.
    ECHO JOB COMPLETED SUCCESSFULLY...
    ECHO.
    ECHO.
    SET /P confirmRun=JOB ENDS HIT ENTER TO EXIT:
    EXIT
    GOTO End
    :Error
    ECHO.
    ECHO Job Ends! Bye bye!!
    ECHO.
    ECHO.
    SET /P confirmRun=JOB ENDS HIT ENTER TO EXIT:
    EXIT
    :End
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Roberta,

    Is there any reason you don't want this coded directly in VBA?

    If you're just using the file names gathered in your Access VBA code you could load them directly into an array and process from there no need to create a file at all. You would have the additional benefit of only one place to maintain the code. You also wouldn't have to worry about someone deleting the external file accidentally.

    Let us know if we should proceed down this path?

    Code similar to this example:
    Code:
    Sub FilterDirectory()
    
        Dim zSearchDir As String  '*** Directory to Search ***
        Dim zDestDir   As String  '*** Where to copy files ***
        Dim zFound     As String
        Dim lFileCnt   As Long
    
        zSearchDir = "G:\BEKDocs\Excel\VBA\" '*** MUST have trailing \ ***
        zDestDir = "G:\Test\"
       
        zFound = Dir(zSearchDir & "*.*") '*** Get First File ***
        
        Do While zFound <> vbNullString
       
          '*** ONLY copy files w/o designated prefixes ***
          
          Select Case Left(zFound, 3)
                Case "CDW"
                Case "DWA"
                Case "VBA"  '*** For my test you can delete ***
                Case Else   '*** Found a file to process    ***
                '  Do your processing here!
                '*** Start Test Code ***
                  Debug.Print zFound
                  lFileCnt = lFileCnt + 1
                '*** End   Test Code ***
                FileCopy zSearchDir & zFound, zDestDir & zFound
          End Select
          
          zFound = Dir()   '*** Get Next File ***
          
        Loop
    HTH
    Last edited by RetiredGeek; 2016-05-09 at 17:07.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Retired Geek --

    Yes I use the file names in my db and your suggestion sounds great, however, there are a couple of hurdles to overcome, the path name will change each month, i.e. \\mypath\Project2\MM_MMMYY and I'm not very good with coding Array's. I didn't think anyone would want to assist with that so I didn't ask. But would certainly love explore that option and give it a try.

    Thanks.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Roberta,

    Could you post the code that reads and processes the file created by your batch file? I'll do my best to integrate it.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Retired,

    I am currently just running the batch file which creates a text file. I manually open the newly created text file then copy and paste into my table. Yes very clunky but it's the only way I know how to do it at this point. The one thing I want to add, that isn't included in the batch process, is to capture the path and file size. I can't thank you enough for all your help, it's greatly appreciated.

    Batch File:
    Code:
    @ECHO OFF
    
    
    CLS
    
    ECHO.
    ECHO EXTRACT FILE NAMES JOB...
    
    ECHO.
    SET /P confirmRun=ARE YOU SURE TO CONTINUE(Y/N)?:
    
    IF "%confirmRun%"=="" GOTO Error
    IF "%confirmRun%"=="N" GOTO Error
    
    ECHO.
    SET /P sourcePath=KEY IN THE SOURCE PATH:
    
    ECHO.
    SET /P destPath=KEY IN THE DESTINATION PATH:
    
    ECHO.
    SET /P outPutFileName=KEY IN THE OUTPUT FILENAME(ex: FileName.txt):
    
    ECHO.
    ECHO CHECKING FOR DIRECTORIES...
    
    ECHO.
    
    IF exist "%sourcePath%" (ECHO %sourcePath% FOUND) ELSE ( 
    ECHO %sourcePath% NOT FOUND
    GOTO Error
    )
    
    ECHO.
    
    IF exist "%destPath%" (ECHO %destPath% FOUND ALREADY) ELSE (
    mkdir "%destPath%" 
    ECHO %destPath% CREATED
    )
    
    SET "outputFileFullName=%destPath%\%outPutFileName%"
    
    ECHO.
    ECHO COMPLETED CHECKING DIRECTORIES!!!
    
    
    ECHO.
    ECHO PREPARING RESULT...
    
    
    for /b /s "%sourcePath%" %%i in (*.*) do @echo %%~nxi >>"%outputFileFullName%"
    
    
    ECHO CHECK RESULT FILE HERE...
    
    ECHO %outputFileFullName%
    
    ECHO.
    ECHO JOB COMPLETED SUCCESSFULLY...
    
    ECHO.
    ECHO.
    SET /P confirmRun=JOB ENDS HIT ENTER TO EXIT:
    
    EXIT
    
    GOTO End
    :Error
    ECHO.
    ECHO Job Ends! Bye bye!!
    ECHO.
    ECHO.
    SET /P confirmRun=JOB ENDS HIT ENTER TO EXIT:
    EXIT
    :End
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Roberta,

    Could you post your Access Table Definition.
    Mostly I want the field names and types/length.

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Roberta,

    Ok, here's some code that I've tested and I think it does what you want.

    Of course you'll have to adjust it to your Database, Table, and Field names but the rest should work as written.

    You'll get prompted for the directory to search:
    GFilesInput.PNG

    I've provided a Stub search path and you can remove that from the code if you wish (InputBox command). If you use it remember to use the End key before finishing the stub or you will over write it!

    I've also included some error checking for the User clicking the Cancel button or not providing any data and also if no files are found in the specified location.

    You'll also get a message at the end telling you how many files were added to the database. Note: the way I setup my DB if I process the same directory twice it duplicates the files! Depending on your database table structure you may need to add code to trap duplicate record errors!

    GFilesTable.PNG
    Code:
    Option Compare Database
    Option Explicit
    
    Sub ReadDirectory()
    
        Dim zSearchDir As String  '*** Directory to Search       ***
        Dim zFound     As String
        Dim lFileCnt   As Long    '*** Number of Files Processed ***
        Dim lFileLen   As Long    '*** Length of the File        ***
        Dim dbsTest    As DAO.Database
        Dim rstGFiles  As DAO.Recordset
    
        zSearchDir = InputBox("Enter the Path to Search", "Search Path Entry:", _
                              "G:\BEKDocs")
        
        If (zSearchDir = "") Then
          MsgBox "No path to search was provided...EXITING!", _
                  vbOKOnly + vbCritical, "No User Input"
          Exit Sub
        End If
        
        If (Not (Right(zSearchDir, 1)) = "\") Then zSearchDir = zSearchDir + "\"
        zFound = Dir(zSearchDir & "*.*") '*** Get First File ***
        
        If (zFound = "") Then
          MsgBox "No files matching search pattern found!" & vbCrLf & _
                 "...EXITING!", _
                  vbOKOnly + vbCritical, "No Matched Files"
          Exit Sub
        End If
        
        Set dbsTest = CurrentDb
        Set rstGFiles = dbsTest.OpenRecordset("GFiles")
        Do While zFound <> vbNullString
       
          lFileCnt = lFileCnt + 1
          lFileLen = FileLen(zSearchDir & zFound)
          With rstGFiles
              .AddNew      'Add a new blank record
              rstGFiles!FileName = zFound
              rstGFiles!FileLength = lFileLen
              rstGFiles!FilePath = zSearchDir
              .Update
          End With
          
          zFound = Dir()   '*** Get Next File ***
          
        Loop
    
        rstGFiles.Close
        dbsTest.Close
        Set rstGFiles = Nothing
        Set dbsTest = Nothing
        
        MsgBox "There were " & Format(lFileCnt, "#") & " Files Processed.", _
                  vbOKOnly + vbInformation, "Processing Statistics:"
        
    End Sub   'ReadDirectory
    When done don't forget to either close and open your Table or Refresh it (F5)!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am speechless....I love this. Thank you so much.

    There are 2 minor problems--

    1. If the path I'm searching has a space in it I get "no files matching search pattern". If I put the Open/Close Quotes around the path (Dir1\Data Sources\) then I get "run time error 52" bad file name..

    2. It doesn't search sub folders

    For #1, I don't have a clue how to handle the space within the path.
    For #2 I will see what I can do to modify the sub folder issue.

    Again, thank you so much.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Roberta,

    This is not a problem with the code. I just tested it with a directory with spaces and it worked fine.

    You need to check what you are entering in the input box. You need the entire drive\path, e.g. C:\Users\Documents\Excel... or if you are hitting a network resource the entire url, e.g. \\MyBookLive\CMShared\...

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Roberta,

    Here's an updated version with a Folder Picker for selecting the search directory. This should eliminate problems with proper specification of the directory to be searched.

    Code:
    Option Compare Database
    Option Explicit
    
    Sub ReadDirectory()
    
        Dim zSearchDir As String  '*** Directory to Search       ***
        Dim zFound     As String
        Dim lFileCnt   As Long    '*** Number of Files Processed ***
        Dim lFileLen   As Long    '*** Length of the File        ***
        Dim dbsTest    As DAO.Database
        Dim rstGFiles  As DAO.Recordset
        Dim fd         As FileDialog
    
        Set fd = Application.FileDialog(msoFileDialogFolderPicker)
     
        With fd
            .AllowMultiSelect = False
    '*** Uncomment the line below to set an Initial Folder ***
    '        .InitialFileName = "G:\BEKDocs\Excel\"  '*** Make sure you have the trailing \ ***
            .Show
        End With 'FD
        
        If (fd.SelectedItems.Count = 0) Then
          MsgBox "User presed Cancel or selected no folder...EXITING", _
                 vbCritical + vbOKOnly, "No usable User Input"
          Exit Sub
        End If
        
        zSearchDir = fd.SelectedItems(1)
        
        If (zSearchDir = "") Then
          MsgBox "No path to search was provided...EXITING!", _
                  vbOKOnly + vbCritical, "No User Input"
          Exit Sub
        End If
        
        If (Not (Right(zSearchDir, 1)) = "\") Then zSearchDir = zSearchDir + "\"
        zFound = Dir(zSearchDir & "*.*") '*** Get First File ***
        
        If (zFound = "") Then
          MsgBox "No files matching search pattern found!" & vbCrLf & _
                 "...EXITING!", _
                  vbOKOnly + vbCritical, "No Matched Files"
          Exit Sub
        End If
        
        Set dbsTest = CurrentDb
        Set rstGFiles = dbsTest.OpenRecordset("GFiles")
        Do While zFound <> vbNullString
       
          lFileCnt = lFileCnt + 1
          lFileLen = FileLen(zSearchDir & zFound)
          With rstGFiles
              .AddNew      'Add a new blank record
              rstGFiles!FileName = zFound
              rstGFiles!FileLength = lFileLen
              rstGFiles!FilePath = zSearchDir
              .Update
          End With
          
          zFound = Dir()   '*** Get Next File ***
          
        Loop
    
        rstGFiles.Close
        dbsTest.Close
        Set rstGFiles = Nothing
        Set dbsTest = Nothing
        
        MsgBox "There were " & Format(lFileCnt, "#") & " Files Processed.", _
                  vbOKOnly + vbInformation, "Processing Statistics:"
        
    End Sub   'ReadDirectory
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good Morning,

    I appreciate all your efforts very much and wish I had your talent and knowledge.

    I've tried everything under the sun to get either of your options to process when the path has a space in it and it doesn't. It's shame because it works beautifully when the path doesn't include a space in the path.

    The paths that I'm using for both options is a network path (see examples below).

    \\myCompany\MyPath\this\one\has spaces\ -- doesn't work returns msg: "No files matching search pattern found!, exiting"

    \\myCompany\MyPath\this\one_uses_underscores\ -- works perfectly.

    If you have any other suggestions, I'm open but I totally understand you have other projects to work on and again, appreciate all your efforts. My only other option for now, is to continue to run the batch file and manually load to Access.

    Thanks so much for your help and guidance.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  12. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Roberta,

    There must be something in your environment that is out of the ordinary.

    Here's a picture of my Test file to show you that spaces don't cause me any problems.
    pathspaces.PNG

    There has to be a solution but trying to find it would require access to your environment to run debugging code to see what is going on, sorry!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •