Solved

Convert .bat file to VBA

Posted on 2014-11-30
11
482 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

738 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