Avatar of jonlake
jonlake
Flag for Guernsey asked on

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?
Microsoft AccessVisual Basic ClassicWindows Batch

Avatar of undefined
Last Comment
Nick67

8/22/2022 - Mon
SOLUTION
Nick67

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
aikimark

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.
jonlake

ASKER
I'm getting this:

vba error
SOLUTION
aikimark

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jonlake

ASKER
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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Nick67

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
aikimark

Have you tried the Shell function?
Have you tried the wscript.shell object's Run method?
bonjour-aut

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jonlake

ASKER
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
Nick67

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