Run DOS command via VBA

[peterhw]

I have seen an old post elsewhere (https://www.mrexcel.com/forum/excel-questions/541328-run-dos-command-via-vba.html)
and sort of trying the following to create 2 files (or more I guess) concatenated:-

Sub test_bat_file(folderPath As String)Dim RetVal, A, fs
'A.writeline "cd PathAllFilesLocated"
Dim My_list As String, fname As String
    Set fs = CreateObject("Scripting.FileSystemObject")
    fname = folderPath + "Merge.bat"
    Set A = fs.CreateTextFile(fname, True)
    My_list = ""    '   "COPY "
    For int1 = 1 To ListBox1.ListCount - 1
        If ListBox1.Selected(int1) = True Then
            ListBox3.AddItem ListBox1.List(int1)
            My_list = My_list + " " & folderPath & ListBox1.List(int1)
        End If
    Next int1
    int1 = int1
    My_list = My_list + " " & folderPath & TextBox2.Value + ".txt"
    A.writeline My_list     'fname
    A.Close
    RetVal = Shell(fname, 1)
End Sub

I can't see what actually happens - too fast I do have a file called Merge.bat but I don't get a merged file (named ABC as textbox2.value).

Contents of .bat file are:-
1 long string with file names separated by space - simple format is as follows
FileA.txt FileB.txt ABC.txt

actual file names are

C:\Users\HOME\AppData\Roaming\MetaQuotes\Terminal\0A89B723E9501DAD3F2D5CB4F27EBDAB\MQL4\Files\Test_Data\C_03Aug18_EURUSD_6_AUG_start_time_15_00.txt C:\Users\HOME\AppData\Roaming\MetaQuotes\Terminal\0A89B723E9501DAD3F2D5CB4F27EBDAB\MQL4\Files\Test_Data\C_03Aug18_EURUSD_6_AUG_start_time_15_30.txt C:\Users\HOME\AppData\Roaming\MetaQuotes\Terminal\0A89B723E9501DAD3F2D5CB4F27EBDAB\MQL4\Files\Test_Data\ABC.txt

seems to 'nearly work' but just can't locate any output (if there is any)

 

[RoryA]

If your batch file is just a list of file names, then it won't produce anything. To append multiple files, your batch file needs to look like this:

COPY C:\Users\HOME\AppData\Roaming\MetaQuotes\Terminal\0A89B723E9501DAD3F2D5CB4F27EBDAB\MQL4\Files\Test_Data\C_03Aug18_EURUSD _6_AUG_start_time_15_00.txt+C:\Users\HOME\AppData\Roaming\MetaQuotes\Terminal\0A89B723E9501DAD3F2D5CB4F27EBDAB\MQL4\Files\Test_Data\C_03Aug18_EURUSD _6_AUG_start_time_15_30.txt C:\Users\HOME\AppData\Roaming\MetaQuotes\Terminal\0A89B723E9501DAD3F2D5CB4F27EBDAB\MQL4\Files\Test_Data\ABC.txt

with the word COPY at the start, a + sign between each source file name and then the destination file name at the end.

It doesn't even look like you need a batch file here, you could just run a command prompt in the code.

 

[John_w]

If you want to see what happens in the command window change the Shell statement to:

    RetVal = Shell("cmd.exe /k " & Chr(34) & fname & Chr(34), 1)

The /k keeps the command window open and you must type exit to close it.

 

[peterhw]

If you want to see what happens in the command window change the Shell statement to:

    RetVal = Shell("cmd.exe /k " & Chr(34) & fname & Chr(34), 1)

The /k keeps the command window open and you must type exit to close it.

Many thanks to both. Just trying to figure out exactly what I now want to achieve

Very help***

Thanx

 

[peterhw]

Many thanks again.
Modified slightly to move control to the required folder thereby avoiding a long path name for each command and being more 'readable'

Sub test_bat_file(folderPath As String)
Dim RetVal, A, fs
Dim newfolderpath As String
Dim My_list As String, fname As String
    str1 = folderPath
    Set fs = CreateObject("Scripting.FileSystemObject")
    fname = folderPath + "Merge.bat"
    Set A = fs.CreateTextFile(fname, True)
    My_list = "COPY "
    For int1 = 1 To ListBox1.ListCount - 1
        If ListBox1.Selected(int1) = True Then
            ListBox3.AddItem ListBox1.List(int1)
            My_list = My_list + ListBox1.List(int1) + "+"
        End If
    Next int1
    My_list = Left(My_list, Len(My_list) - 1)           '   removes last '+' sign
    int1 = int1
    My_list = My_list + " " + TextBox2.Value + ".txt"   '   text file for MERGED Files
    A.writeline "c:"
    A.writeline "cd\"
    newfolderpath = Replace(folderPath, "C:\", "")
    A.writeline "cd\" & newfolderpath
    A.writeline My_list     'fname
    A.Close
    RetVal = Shell("cmd.exe /k " & Chr(34) & fname & Chr(34), 1)
End Sub

Batch file now reads

c:
cd\
cd\Users\HOME\AppData\Roaming\MetaQuotes\Terminal\0A89B723E9501DAD3F2D5CB4F27EBDAB\MQL4\Files\Test_Data\
COPY A1.txt+A2.txt ANY.txt

Works great
Thanks