3

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

2

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

0

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

  • Unfortunately that didn't seem to work for me. The cmd window pops up quickly and disappears as if it didn't actually execute the code. Batch files are blocked on my system, so that may have something to do with it. – ampsoccer17 Apr 11 '17 at 17:40
  • @ampsoccer17 Try %comspec% /k test.cmd instead – DavidPostill Apr 11 '17 at 17:42
  • I changed the vba code to C:\Test\test.cmd and that fixed that issue, but led me back to my first issue that batch files are blocked on my system so it could not run. – ampsoccer17 Apr 11 '17 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.