Excel VBA: Running a shell script to both change directory and then run a custom script

Question

I am trying to run this cmd script using vba which basically merges all csv files in a directory and then adds the filename of each file into a column at the end.

for /f %a in ('dir /b *.csv') do for /f "tokens=*" %b in (%a) do echo %b,%a >> all.csv

The problem I have is how to run this command in Excel Vba, while changing the default directory before running the above code.

This is because the for /f %a in ('dir /b *.csv') bit just takes your default directory in the cmd window. In the actual cmd window, I can just use the cd command, but no idea how to automate this with vba.

Link to the original post for the code

Any ideas guys?

Thanks

Automate This · Accepted Answer · 2016-01-18 21:54:52Z

Here is a method for running your code from VBA excel. I hard coded the path because I'm not sure what your input method will be.

Sub mergeCSV()
    Dim myDir As String
    myDir = "C:\Users\username\compile\"

    Dim strCommand As String
    strCommand = "for /f %a in ('dir /b *.csv') do for /f ""tokens=*"" %b in (%a) do echo %b,%a >> all.csv"

    Call Shell("cmd.exe /k cd /d " & myDir & " & " & strCommand, 1)
End Sub

The Shell call first opens the command prompt and then changes the current directory to the one specified in the myDir variable. Then it runs the second command which I put in it's own variable strCommand to make it easier to read and follow. You can make it so the window doesn't stay open by replacing /k with /c

I had two files for test, one with numbers and one with alpha characters. Result file all.csv looks something like this:

Collectives™ on Stack Overflow

Excel VBA: Running a shell script to both change directory and then run a custom script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related