Loop Through Folder, Rub Batch File, Move File

This concerns an evolving process.

I would like to use VBA within an MS Access for to loop through a target folder looking for CSV files. When it finds the first file I need to rename it as Data.csv. Then I need the VBA to execute a batch file which processes it through some modelling software we use. Following that I need to rename it back to its original name but with the word Processed added. I would then like to move the processed file to a folder called (imaginatively) Processed.

So far, with the help of EE guru's I have got to:

' Declare constants
Const FOLDER_NAME As String = "C:\Temp\YourCSVFolder\"  ' Your CSV folder

' Declare variables
Dim strFile As String
Dim lngCount As Long

' Process CSV files
strFile = Dir(FOLDER_NAME & "\.csv")
Do Until strFile = ""

    ' Extract summary data
    Shell """C:\Program Files\monarch\program\monarch"" " & _
          """G:\Financial Data Download\" & strFile & """ " & _
          """G:\Financial Data Download\MonarchModels\FX_CrossRates.xmod"" " & _
          """G:\Financial Data Download\FX_Cross_Rates.accdb"" /t", _
          vbHide

    ' Get next file
    lngCount = lngCount + 1
    strFile = Dir
Loop

' Display count of files processed
MsgBox lngCount & " files processed in folder " & FOLDER_NAME, _
       vbInformation + vbOKOnly + vbDefaultButton1, _
       "Process Files"


Rather than what I have now, I need the Shell to call the following Dos/Batch:

"C:\Program Files\Monarch\Program\Monarch.exe" "C:\Temp\Sales_Analysis_Test\CSV_Export.xprj" /pxall

Thanks
jonlakeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GRayLCommented:
For a Start, I believe this:

strFile = Dir(FOLDER_NAME & "\.csv")

should be:

strFile = Dir(FOLDER_NAME & "\*.csv")

0
jonlakeAuthor Commented:
Good spot, any thoughts on the rest of my conundrum?
0
Bill PrewCommented:
How about this?

' Declare constants
Const FOLDER_NAME As String = "C:\Temp\YourCSVFolder\"  ' Your CSV folder
Const PROCESSED As String = "C:\Temp\Processed\"

' Declare variables
Dim strFile As String
Dim lngCount As Long

' Process CSV files
strFile = Dir(FOLDER_NAME & "*.csv")
Do Until strFile = ""
    ' Rename file
    Name FOLDER_NAME & strFile As FOLDER_NAME & "data.csv"

    ' Extract summary data
    Shell """C:\Program Files\Monarch\Program\Monarch.exe"" ""C:\Temp\Sales_Analysis_Test\CSV_Export.xprj"" /pxall", vbHide

    ' Rename file
    Name FOLDER_NAME & "data.csv" As Replace(PROCESSED & strFile, ".csv", "-processed.csv")

    ' Get next file
    lngCount = lngCount + 1
    strFile = Dir
Loop

' Display count of files processed
MsgBox lngCount & " files processed in folder " & FOLDER_NAME, _
       vbInformation + vbOKOnly + vbDefaultButton1, _
       "Process Files"

Open in new window

~bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

GRayLCommented:
Bill:  The Monarch.exe line somehow needs to process the renamed csv file.  I think the asker made an error when he provided the 'rather than' string.
0
jonlakeAuthor Commented:
I'm getting an error on the line:

Name FOLDER_NAME & strFile As FOLDER_NAME & "data.csv"

Could that be a reference library that's in place?
0
jonlakeAuthor Commented:
I'm getting “Run-time error '75': Path/File access error”
0
jonlakeAuthor Commented:
I should add, it's on the Rename line:

Name FOLDER_NAME & "data.csv" As Replace(PROCESSED & strFile, ".csv", "-processed.csv")
0
jonlakeAuthor Commented:
I think I know what is happening. The batch file routine isn't finishing before the VBA rename routine kicks in. Is there a way to delay the rename until the batch file has run?
0
jonlakeAuthor Commented:
No, I was wrong. It still blows up an error.
0
Bill PrewCommented:
What are the two const settings in your script, what directories are you processing?

~bp
0
jonlakeAuthor Commented:
I'm almost there. I can get the code to process the batch file and wait for the application to finish before looping back. The only thing I need to do now if to fix the file rename and move element. Here is my full code:

Option Explicit

''' *************************************************************************
''' Module Constant Declaractions Follow
''' *************************************************************************
''' Constant for the dwDesiredAccess parameter of the OpenProcess API function.
Private Const PROCESS_QUERY_INFORMATION As Long = &H400
''' Constant for the lpExitCode parameter of the GetExitCodeProcess API function.
Private Const STILL_ACTIVE As Long = &H103


''' *************************************************************************
''' Module Variable Declaractions Follow
''' *************************************************************************
''' It's critical for the shell and wait procedure to trap for errors, but I
''' didn't want that to distract from the example, so I'm employing a very
''' rudimentary error handling scheme here. This variable is used to pass error
''' messages between procedures.
Public gszErrMsg As String


''' *************************************************************************
''' Module DLL Declaractions Follow
''' *************************************************************************
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long


Public Sub DemoShellAndWait()

    On Error GoTo ErrorHandler
   
    ''' Clear the error mesaage variable.
    gszErrMsg = vbNullString
   
    ''' Notification that the demo is about to begin.
    MsgBox "You are about to be redirected to the Windows Calculator.", vbInformation, "Shell and Wait Demo"
   
    ''' Shell out to the Windows Calculator.
    If Not bShellAndWait("Monarch.exe", vbNormalFocus) Then Err.Raise 9999
   
    ''' This message box will not display until you have dismissed the Calculator.
    MsgBox "You have finished using the Windows Calculator.", vbInformation, "Shell and Wait Demo"
   
    Exit Sub
   
ErrorHandler:
    ''' If we ran into any errors this will explain what they are.
    MsgBox gszErrMsg, vbCritical, "Shell and Wait Demo"
End Sub


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' Comments:   Shells out to the specified command line and waits for it to
'''             complete. The Shell function runs asynchronously, so you must
'''             run it using this function if you need to do something with
'''             its output or wait for it to finish before continuing.
'''
''' Arguments:  szCommandLine   [in] The command line to execute using Shell.
'''             iWindowState    [in] (Optional) The window state parameter to
'''                             pass to the Shell function. Default = vbHide.
'''
''' Returns:    Boolean         True on success, False on error.
'''
''' Date        Developer       Action
''' --------------------------------------------------------------------------
''' 05/19/05    Rob Bovey       Created
'''
Private Function bShellAndWait(ByVal szCommandLine As String, Optional ByVal iWindowState As Integer = vbHide) As Boolean

    Dim lTaskID As Long
    Dim lProcess As Long
    Dim lExitCode As Long
    Dim lResult As Long
   
    On Error GoTo ErrorHandler

    ''' Run the Shell function.
    lTaskID = Shell(szCommandLine, iWindowState)
   
    ''' Check for errors.
    If lTaskID = 0 Then Err.Raise 9999, , "Shell function error."
   
    ''' Get the process handle from the task ID returned by Shell.
    lProcess = OpenProcess(PROCESS_QUERY_INFORMATION, 0&, lTaskID)
   
    ''' Check for errors.
    If lProcess = 0 Then Err.Raise 9999, , "Unable to open Shell process handle."
   
    ''' Loop while the shelled process is still running.
    Do
        ''' lExitCode will be set to STILL_ACTIVE as long as the shelled process is running.
        lResult = GetExitCodeProcess(lProcess, lExitCode)
        DoEvents
    Loop While lExitCode = STILL_ACTIVE
   
    bShellAndWait = True
    Exit Function
   
ErrorHandler:
    gszErrMsg = Err.Description
    bShellAndWait = False
End Function
Private Sub Monarch_Click()

Const FOLDER_NAME As String = "C:\Temp\Sales_Analysis_Test\"  ' Your CSV folder
Const PROCESSED As String = "C:\Temp\Processed\"

Dim strFile As String
Dim lngCount As Long

    ' Process CSV files
    strFile = Dir(FOLDER_NAME & "*.csv")
    Do Until strFile = ""
    ' Rename file
    Name FOLDER_NAME & strFile As FOLDER_NAME & "data.csv"
   
    On Error GoTo ErrorHandler
   
    ''' Clear the error mesaage variable.
    gszErrMsg = vbNullString
       
    ''' Shell out to the Program.
   
    If Not bShellAndWait("C:\Temp\Sales_Analysis_Test\Run_Export.bat", vbNormalFocus) Then Err.Raise 9999
    Kill FOLDER_NAME & "data.csv"
    ' Get next file
    lngCount = lngCount + 1
    strFile = Dir
    Loop
   
    ' Display count of files processed
    MsgBox lngCount & " files processed in folder " & FOLDER_NAME, _
    vbInformation + vbOKOnly + vbDefaultButton1, _
    "Process Files"
   
    Exit Sub
   
ErrorHandler:
   
    MsgBox gszErrMsg, vbCritical, "Monarch Batch Processing Error"
   
End Sub
0
jonlakeAuthor Commented:
With a little added here and there to make it bespoke to my environment (as you might expect), your solution was excellent.

Thank you so much for your help.
0
Bill PrewCommented:
Welcome, glad that was helpful.

~bp
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.