• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Syntax for DOS command line with leading space

polarisking

Member
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
74752
 

shrivallabha

Excel Ninja
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.

Code:
    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

Member
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

Excel Ninja
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.

Code:
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:

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

Code:
"" /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.

Code:
""" /b """
Thus it becomes triple quote which appears tricky to uninitiated.
 
Top