How to return value of command line variable to im

Question

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 in 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

Answer 1

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