Embedding Variable in Command Line into VBA

Question

I am trying to embed the below command line into VBA:

for /f "skip=10 delims=" %%A in ('dir /a:-d /b /o:-d /t:c *.log ^2^>nul') do if exist "%%~fA" del "%%~fA

What I tried was:

Set objShell = CreateObject("WScript.Shell")
cmdLine = "cmd /k for /f ""skip=2 delims="" %%A in ('dir /a:-d /b /o:-d /t:c *.xls ^2^>nul') do if exist ""%%~fA"" del ""%%~fA"""
retVal = objShell.Run(cmdLine, 1, True)

It didn't work.

However, if I saved the code as .cmd, it worked.

Did you want there to be another quote at the end of "%%~fA?, your top example doesn't how it, but your bottom example does ""%%~fA"" — Jason Brady
– Jason Brady, Commented Jun 7, 2016 at 18:25
@Mat'sMug, the command didn't delete the files I intended to; also the retVal returns 1. — lovechillcool
– lovechillcool, Commented Jun 7, 2016 at 18:33
@JasonBrady for the other quotes, the syntax is correct. You can use Debug.Print cmdLine to check whether the string returned match your expectations. — lovechillcool
– lovechillcool, Commented Jun 7, 2016 at 18:37

lovechillcool · Accepted Answer · 2016-06-18 04:42:24Z

1

Finally, I found the issue. It's not about the single quote.

You just need to change %%A into %A. However, if you save the code in .bat/.cmd, %%A works.

It is very finicky dealing with command line.

answered Jun 18, 2016 at 4:42

lovechillcool

7822 gold badges13 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Embedding Variable in Command Line into VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related