Convert .bat file to VBA
Hi, I have a batch file which creates a single text file from many, and appends the file name as the first line in each text file (before combining them as one). I can then extract all data in a single pass, but I really need to run it in VBA, passing the text file location to the string as a variable. This is what I have now:
@ECHO OFF
SETLOCAL EnableDelayedExpansion
FOR /f "tokens=*" %%a IN ('DIR /b /a-d "*.txt"') DO (
SET Var=%%a
ECHO !Var:~0,-4!>>TempFile.txt
ECHO.>>TempFile.txt
TYPE "%%a" >>"TempFile.txt"
DEL "%%a"
REN "TempFile.txt" "%%a"
)
copy *.txt Combined.txt
DEL "TempFile.txt"
Currently I run it from inside the text file folder, but I want to pass that location, and the output location as a string variable, allowing me to have the text files in alternative locations, and set within a table called tblFolder_Locations.
Thoughts?
@ECHO OFF
SETLOCAL EnableDelayedExpansion
FOR /f "tokens=*" %%a IN ('DIR /b /a-d "*.txt"') DO (
SET Var=%%a
ECHO !Var:~0,-4!>>TempFile.txt
ECHO.>>TempFile.txt
TYPE "%%a" >>"TempFile.txt"
DEL "%%a"
REN "TempFile.txt" "%%a"
)
copy *.txt Combined.txt
DEL "TempFile.txt"
Currently I run it from inside the text file folder, but I want to pass that location, and the output location as a string variable, allowing me to have the text files in alternative locations, and set within a table called tblFolder_Locations.
Thoughts?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
aikimark
Why convert to VBA when you can add parameters %1 %2 to the code to transfer the command line string values into the batch file commands? Your VBA code simply adds string values (from your tables) to the batch file invoking command.
ASKER
I'm getting this:
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The batch file creates, and can be run independently but I am failing to run it from an onclick event, despite having tried a wide range of shell syntax.
Shell "cmd /c m:\prod\" & ReturnComputerName & ".cmd" is what I use
I call them batch files, but try it with a .cmd ending
The syntax is
Shell "ValidPath"
So
Shell "cmd /c c:\temp\dir.bat"
Should do it
I call them batch files, but try it with a .cmd ending
The syntax is
Shell "ValidPath"
So
Shell "cmd /c c:\temp\dir.bat"
Should do it
Have you tried the Shell function?
Have you tried the wscript.shell object's Run method?
Have you tried the wscript.shell object's Run method?
I suggest to use a more direct appoach.
Assuming that you have a table 'tbl_FileLocations' the following code will work fine:
Assuming that you have a table 'tbl_FileLocations' the following code will work fine:
Private Sub TestConcatFiles()
Dim fs As New Scripting.FileSystemObject
Dim tst As Scripting.TextStream
Dim tss As Scripting.TextStream
Dim rs As DAO.Recordset
Set tst = fs.CreateTextFile("d:\concatenated_text_files.txt", True)
Set rs = CurrentDb.OpenRecordset("select * from tbl_FileLocations")
If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
Set tss = fs.OpenTextFile(rs.Fields(0))
tst.WriteLine rs.Fields(0)
Do While Not tss.AtEndOfStream
tst.WriteLine tss.ReadLine
Loop
rs.MoveNext
Loop
End If
End SubASKER
Sorry for the delay in accepting. Great help from both, though the batch file seems to run in a split second and doesn't actually creat the combined file. When run independently it works perfectly. I'll kep looking for a way to let it run, whilst pausing somehow to let it finish.
Thanks for your help
Thanks for your help
Sorry, I meant to get back to this.
There are ways to detect when a shelled process ends -- which is what you say you need now
http://msdn.microsoft.com/en-us/library/office/ff845544(v=office.15).aspx
API code is tough stuff on a VBA guy's brain, but the explanation is pretty good
There are ways to detect when a shelled process ends -- which is what you say you need now
http://msdn.microsoft.com/en-us/library/office/ff845544(v=office.15).aspx
API code is tough stuff on a VBA guy's brain, but the explanation is pretty good