Solved

Convert .bat file to VBA

Posted on 2014-11-30
11
441 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
 
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article was inspired by a question here at Experts Exchange (http://www.experts-exchange.com/Software/Photos_Graphics/Images_and_Photos/Q_28629170.html). The requirements stated in that question are (1) reduce the file size of a large number of…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

867 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now