How to call command line using vba to list file names

Question

I'm trying to write some vba code that sends a line of code to the command prompt and executes it. I have that part down, but I need help getting the actual code to work.

I want to list all of the files in a specific folder that are the .doc file extension, but I want to exclude the first three characters of the filename that gets printed to my output text file. (Note: I'm using vba because this is one of several different commands I'd like to get into a single vba macro, and batch files are blocked for me so I'd like to work directly with the command prompt)

The following code works and gives me the file names without the file extension (ie. ABC201704.doc will return as ABC201704)

%comspec% /c for %i in (C:\Test\ABC*.doc) do @echo %~ni >> C:\Test\Output.txt

However, I don't know how to modify this so that it doesn't include the first 3 characters (ie. it would return 201704 instead of ABC201704). Any help would be greatly appreciated! I tried using the following link, but I couldn't figure out how to get that to work for my situation.

Any way to get the first few characters of filename in DOS batch file Programming

Answer 1

A few people on stackoverflow were able to help me get my answer if anyone is curious:

cmd /v:on /c "for %a in ("C:\Test\ABC*.doc") do set docname=%~na & echo !docname:~3! >> C:\Test\Output.txt"

https://stackoverflow.com/questions/43352753/how-to-use-command-prompt-to-list-file-names-in-directory-but-exclude-1st-3-char?noredirect=1#comment73769999_43352753

Answer 2

I don't know how to modify this so that it doesn't include the first 3 characters

Use the following batch file (test.cmd):

@echo off 
setlocal enabledelayedexpansion
for %%i in (C:\Test\ABC*.doc) do (
   set _file=%%~ni 
   set _file=!_file:~3!
   echo !_file!
   ) >> C:\Test\Output.txt
endlocal

Then in your VBA code:

%comspec% /c test.cmd

---

Further Reading

• An A-Z Index of the Windows CMD command line - An excellent reference for all things Windows cmd line related.
• enabledelayedexpansion - Delayed Expansion will cause variables to be expanded at execution time rather than at parse time.
• for - Conditionally perform a command on several files.
• variables - Extract part of a variable (substring).