Solved

Convert .bat file to VBA

Posted on 2014-11-30
11
455 Views
Last Modified: 2014-12-17
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?
0
Comment
Question by:jonlake
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 334 total points
ID: 40473056
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
 
LVL 45

Expert Comment

by:aikimark
ID: 40473489
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
 

Author Comment

by:jonlake
ID: 40473751
I'm getting this:

vba error
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 166 total points
ID: 40473855
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
 
LVL 26

Accepted Solution

by:
Nick67 earned 334 total points
ID: 40474050
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
 

Author Comment

by:jonlake
ID: 40474365
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40474426
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
 
LVL 45

Expert Comment

by:aikimark
ID: 40474445
Have you tried the Shell function?
Have you tried the wscript.shell object's Run method?
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 40478546
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
 

Author Closing Comment

by:jonlake
ID: 40497785
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40506006
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

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If like me you are one who spends a lot of time working and scripting with cmd.exe, sometimes it is handy to be able to quickly view a calendar for a given month and year. This script will quickly do just that!  Save the code posted below to a .bat …
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question