Solved

Convert .bat file to VBA

Posted on 2014-11-30
11
493 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

691 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