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?
jonlakeAsked:
Who is Participating?
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.

Nick67Commented:
You can create and run batch files from within VBA:

Dim batfile As Object
Dim fs As Object

Set fs = CreateObject("Scripting.FileSystemObject")
Set batfile = fs.CreateTextFile("c:\temp\dir.bat", True)
'ok lets build the batchfile to do the Job

batfile.WriteLine ("@ECHO OFF")
batfile.WriteLine ("@ SETLOCAL EnableDelayedExpansion")

batfile.WriteLine ("@ FOR /f ""tokens=*"" %%a IN ('DIR /b /a-d ""*.txt""') DO (")
batfile.WriteLine ("@SET Var=%%a")
batfile.WriteLine ("@ECHO !Var:~0,-4!>>TempFile.txt")
batfile.WriteLine ("@ ECHO.>>TempFile.txt")
batfile.WriteLine ("@ TYPE ""%%a"" >>""TempFile.txt"")
batfile.WriteLine ("@ DEL ""%%a"")
batfile.WriteLine ("@ REN ""TempFile.txt"" ""%%a"")
batfile.WriteLine ("@ )
batfile.WriteLine ("@ copy *.txt Combined.txt")
batfile.WriteLine ("@ DEL ""TempFile.txt"")
batfile.Close

'ok, execute it
Shell "cmd /c c:\temp\dir.bat"
MsgBox "it begins"

'clean up the batch file
fs.DeleteFile "c:\temp\dir.bat", True
MsgBox "done!"

Doubling up the quotes in your original stuff is painful and perhaps I didn't get them all correct.
But you can play with it until you get the VBA to create the exact batch file you need.
It's strictly string creation.
Build a string that is a line that you need in your batchfile.
Write it.
Repeat.
Execute the batchfile with shell
Delete it.

Does that work for you?
0
aikimarkCommented:
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.
0
jonlakeAuthor Commented:
I'm getting this:

vba error
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

aikimarkCommented:
As Nick stated, you will need to double-check his work as well as test it, especially for unmatched quote characters.
batfile.WriteLine ("@ECHO OFF")
batfile.WriteLine ("@ SETLOCAL EnableDelayedExpansion")

batfile.WriteLine ("@ FOR /f ""tokens=*"" %%a IN ('DIR /b /a-d ""*.txt""') DO (")
batfile.WriteLine ("@SET Var=%%a")
batfile.WriteLine ("@ECHO !Var:~0,-4!>>TempFile.txt")
batfile.WriteLine ("@ ECHO.>>TempFile.txt")
batfile.WriteLine ("@ TYPE ""%%a"" >>""TempFile.txt""")
batfile.WriteLine ("@ DEL ""%%a""")
batfile.WriteLine ("@ REN ""TempFile.txt"" ""%%a""")
batfile.WriteLine ("@ ")
batfile.WriteLine ("@ copy *.txt Combined.txt")
batfile.WriteLine ("@ DEL ""TempFile.txt""")
batfile.Close

Open in new window

0
Nick67Commented:
I was afraid of quote hell.
Ok
TYPE "%%a" >>"TempFile.txt" original
Each quote needs to be doubled
TYPE ""%%a"" >>""TempFile.txt""
and then everything wrapped in the writeline syntax
batfile.WriteLine ("TYPE ""%%a"" >>""TempFile.txt"""
Quote hell.
Same problem on each red line I expect.  I didn't get them all -- or on some I can see I didn't place a final set of quotes
These are hopefully correct
batfile.WriteLine ("@ DEL ""%%a""")
batfile.WriteLine ("@ REN ""TempFile.txt"" ""%%a""")
batfile.WriteLine ("@ ")
and
batfile.WriteLine ("@ DEL ""TempFile.txt""")

Chr(34) is a "
So, normally I would have done
TYPE "%%a" >>"TempFile.txt" as
batfile.WriteLine ("Type " & chr(34) & "%%a" & chr(34) & " >>" & chr(34) & "TempFile.txt" &chr(34))
The original has 4 quote, the replacement has 4 Chr(34)'s
Good, I likely got that right.

I tried for a successful navigation of quote hell instead.
Sorry I failed.
Fix 'er up and see how it goes.
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
jonlakeAuthor Commented:
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.
0
Nick67Commented:
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
0
aikimarkCommented:
Have you tried the Shell function?
Have you tried the wscript.shell object's Run method?
0
bonjour-autCommented:
I suggest to use a more direct appoach.

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 Sub

Open in new window

0
jonlakeAuthor Commented:
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
0
Nick67Commented:
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
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.