Syntax for DOS command line with leading space

[polarisking]

This is driving me nuts. I'm merging all files with the mask *.txt into one .txt file using a DOS shell command.

The command is Shell Environ$("COMSPEC") & " /c Copy C:\TEST\*.txt C:\TEST\CombinedFile.txt "

I'd like to use variables for both the From and To environments. Because there's a leading space before the To environment, DOS is expecting a end result format of

" "Path and Target Filename"" (that's what you see if you hover over the variable name in VBA)

I cannot seem to get there, no matter how I try. Any advice?

This works: Target = " ""C:\Users\PL18861\Desktop\Work Area\WA001\AllFiles.txt""" 'Notice leading space
This does not: Target = " " & Range("Target") & Chr(34)

Target = " " & Range("Target") & Chr(34)

Call Shell(Environ("COMSPEC") & " /c copy " & Src_Pattern & Target, vbMinimizedNoFocus)

If you hover over the word target in the line above, this is how it resolves

 

[shrivallabha]

This is driving me nuts. I'm merging all files with the mask *.txt into one .txt file using a DOS shell command.

The command is Shell Environ$("COMSPEC") & " /c Copy C:\TEST\*.txt C:\TEST\CombinedFile.txt "

I'd like to use variables for both the From and To environments. Because there's a leading space before the To environment, DOS is expecting a end result format of

" "Path and Target Filename"" (that's what you see if you hover over the variable name in VBA)

I cannot seem to get there, no matter how I try. Any advice?

This works: Target = " ""C:\Users\PL18861\Desktop\Work Area\WA001\AllFiles.txt""" 'Notice leading space
This does not: Target = " " & Range("Target") & Chr(34)

Target = " " & Range("Target") & Chr(34)

Call Shell(Environ("COMSPEC") & " /c copy " & Src_Pattern & Target, vbMinimizedNoFocus)

If you hover over the word target in the line above, this is how it resolves
View attachment 74752

See below code with two demonstrations.

    Src_Pattern = "C:\Temp\feed test\*.txt"
    Target = "C:\Temp\feed test\Combined.txt"
    '\\ With Quotes
    Call Shell(Environ("COMSPEC") & " /c copy """ & Src_Pattern & """ """ & Target & """", vbMinimizedFocus)
    '\\ Another way using Chr(34)
    'Call Shell(Environ("COMSPEC") & " /c copy " & Chr(34) & Src_Pattern & Chr(34) & " " & Chr(34) & Target & Chr(34), vbMinimizedFocus)

You can comment & uncomment the Shell Calls to test it at your end.

 

[polarisking]

This was perfect! Thank you so much.

I'm using

Call Shell(Environ("COMSPEC") & " /c copy """ & Src_Pattern & """ """ & Target & """", vbMinimizedFocus)

One last question: I need to add the parameter /b immediately following Src_Pattern. How would I place it? The triple quotes are (and I'm usually not this stupid) confusing me.

Assuming you're in the directory containing the files, it would look like copy *bucket*.txt /b allfiles.txt

You may know this already, but the /b parameter eliminates the chr(30) marker at the end of allfiles.txt.

 

[shrivallabha]

This was perfect! Thank you so much.

I'm using

Call Shell(Environ("COMSPEC") & " /c copy """ & Src_Pattern & """ """ & Target & """", vbMinimizedFocus)

One last question: I need to add the parameter /b immediately following Src_Pattern. How would I place it? The triple quotes are (and I'm usually not this stupid) confusing me.

Assuming you're in the directory containing the files, it would look like copy *bucket*.txt /b allfiles.txt

You may know this already, but the /b parameter eliminates the chr(30) marker at the end of allfiles.txt.

Great! Thanks for confirming. The code with /b switch will look like below.

Call Shell(Environ("COMSPEC") & " /c copy """ & Src_Pattern & """ /b """ & Target & """", vbMinimizedFocus)

There is a simple way of understanding double quotes as and when you are using them in VBA. Suppose the following is required string as an output:

" /b "

To produce a single " in VBA, the first action is to replace the double quote (") with pair of them ("") as below:

"" /b ""

Post this step, we need to wrap it with another pair of double quotes. These are for VBA to understand that we are passing a string to it.

""" /b """

Thus it becomes triple quote which appears tricky to uninitiated.