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", _
' Get next file
lngCount = lngCount + 1
strFile = Dir
' Display count of files processed
MsgBox lngCount & " files processed in folder " & FOLDER_NAME, _
vbInformation + vbOKOnly + vbDefaultButton1, _
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