VBA to run shell command but to run from different directory

Question

I have code as follows:

ChDir (ActiveWorkbook.Path)
ShellString = "cmd.exe /k cpdf -split " + Chr(34) + ".\" + Replace(File, ".csv", ".pdf") + Chr(34) + " -o temp/x_%%%.pdf"
Shell ShellString, vbNormalFocus

When I run the code, it does nothing, because it can't find cpdf.exe.

Cpdf.exe exists in the same path as my Active Workbook. The ChDir command did not do the trick.

When I run the code, I get

'cpdf' is not recognized as an internal command....

And I am left at the following prompt:

C:\Users\ksmith\Documents

This tells me that the command was trying to run from that folder, and that is why it failed.

How do I run Shell from Desired folder in VBA? ChDir doesn't seem to do the trick, as some people had suggested...

Can you specify the full path to the executable in the command line so avoid the changing of directory? Possibly, getting working from the command line before translating to vba? Then always option of executing by BATCH file. — QHarr
– QHarr, Commented Dec 20, 2017 at 21:17
Is your workbook on the C: drive? Or another drive? (E.g. if your workbook was located in "L:\Temp1\Temp2" then you also need to ChDrive "L" for the ChDir to be useful because the o/s keeps track of the "current" directory on each drive and the default is to use the "current" directory on the "current" drive.) — YowE3K
– YowE3K, Commented Dec 20, 2017 at 21:31
cool. i will try and let you know. workbook is on network drive. — Heap of Pinto Beans
– Heap of Pinto Beans, Commented Dec 20, 2017 at 21:33

YowE3K · Accepted Answer · 2017-12-20 22:16:50Z

The ChDir command only changes the "current" directory on the drive specified, but does not affect which drive is currently "current".

So, if

your computer has a "C" drive on which the "current" directory is "C:\Users\ksmith\Documents", and
you also have an "L" drive on which the "current" directory is "L:\abc\def", and
the directory containing your workbook is "L:\Temp1\Temp2"

then executing

ChDir ActiveWorkbook.Path

will change the "current" directory of the "L" drive to be "L:\Temp1\Temp2", but the "current" drive will continue to be "C" and the C drive's "current" directory will continue to be "C:\Users\ksmith\Documents".

You therefore need to also change the "current" drive. Providing you have a mapped drive (i.e. it won't work with a UNC specified drive) you can do this by using:

ChDrive ActiveWorkbook.Path
ChDir ActiveWorkbook.Path

Collectives™ on Stack Overflow

VBA to run shell command but to run from different directory

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