CODEWITHSUNDEEP
vbaexcelcommand-linecommand-prompt
QHarr
QHarr

Reputation: 84475

How to return value of command line variable to immediate window

Situation:

I want to return the actual value of a string variable from Windows Command Prompt.

The command 

cd C:\Users\User\Desktop\Testfolder && (for /f "eol=: delims=" %F in ('dir /b /od *.csv') do @set "newest=%F" ) && echo %newest%

when executed in command prompt window, correctly returns the value e.g. "Test2.csv"

In the code below I am unable to return this value to the immediate window in Excel; instead, I get the command I issued, which was "%newest%"

Code:

Public Sub Test
    Debug.Print CreateObject("wscript.shell").exec("cmd /c cd C:\Users\User\Desktop\Testfolder && (for /f ""eol=: delims="" %F in ('dir /b /od *.csv') do @set ""newest=%F"" ) && echo %newest%").StdOut.ReadAll
End Sub

Result in command prompt window:

Result

Result in immediate window:

Immediate window

What I have tried:

I have looked at a large number of resources; a few of which I listed in references at end. Various attempts include:

1) I have attempted escaping, which I didn't expect to see work, with: 

echo %%newest%%   '<==== give %Test2.csv%  in CLI and %%newest%% in immediate

2) Adding waits, for example:

Debug.Print CreateObject("wscript.shell").exec("cmd /c cd C:\Users\User\Desktop\Testfolder && (for /f ""eol=: delims="" %F in ('dir /b /od *.csv') do @set ""newest=%F"" ) && PING localhost -n 2 >NUL && echo %newest%").StdOut.ReadAll

3) Piping to a text file and reading back:

 CreateObject("wscript.shell").exec ("cmd /c cd C:\Users\User\Desktop\Testfolder && (for /f ""eol=: delims="" %F in ('dir /b /od *.csv') do @set ""newest=%F"" )&& echo %newest% > C:\Users\User\Desktop\Testfolder\fileInfo.txt") '.StdOut.ReadAll
 Debug.Print CreateObject("Scripting.FileSystemObject").OpenTextFile("C:\UsersUser\Desktop\Testfolder\fileInfo.txt", ForReading).ReadAll

In the last example, on one occasion this wrote out the actual value to the file but after that continued to write "%newest%"

Clearly I am not picking up the result of the executed statement from the command line.

How do I return the actual value please?

Additional info:

This is a series of commands which change to a specified directory; loops overs the csv files in that folder; finds the most recently modified file and returns its name.

References:

  1. Get Return Value of Shellexecute
  2. Reading output from shell
  3. Setting and using variable within same command line in windows cmd
  4. Scripting - Waits

Upvotes: 3

Views: 2960

Answers (1)

QHarr
QHarr

Reputation: 84475

So credit to @FlorentB for the answer which was to use the environment variable expansion flag. This, incorporated into final answer, gave the following which returned the filename of the last modified file from a specified directory.

CreateObject("wscript.shell").exec("cmd /V /C cd " & myDir & " && (for /f ""eol=: delims="" %F in ('dir /b /od *.csv') do @set ""newest=%F"" ) && echo !newest!").StdOut.ReadAll

Upvotes: 2

Related Questions