user5619709 user5619709 - 2 months ago 26
Bash Question

Shell Command in VBA Execution

I'm trying to run two lines of commands using shell. I haven't been able to find a good source on how to actually execute shell in VBA. So far, I have been able to figure out how to open a specific directory.

Sub shellCMD()
Shell ("cmd.exe /k CD\Users\n808037\Desktop\OTHER")
End Sub


This will at least lead me to the directory where I need to go. However, now that I've gotten to the directory I need to go, I need to execute a command after. That is

copy *.csv merged.csv

How do I do this in shell?

Answer

Each Shell call runs in its own process, so you can't run separate commands by calling Shell consecutively. Generally, you'd want to either run multiple commands as a batch file or script if you were going to do a lot of processing. In this case, just specify the full path for copy. There's no need to change the working directory at all:

Shell "cmd.exe /k copy C:\Users\n808037\Desktop\OTHER\*.csv C:\Users\n808037\Desktop\OTHER\merged.csv"