CODEWITHSUNDEEP
vbaexcelcmdexcel-2010
David Gard
David Gard

Reputation: 12047

Run-time error when using the 'Run' method of the 'WScript.Shell' object

I'm trying to run a command prompt via VBA to execute a couple of commands, but I have run in to an error.

Run-time error '-214702894(80070002)':
Method 'Run' of object 'IWshShell3' failed

I have tried wrapping file paths in quotes (as below), but the error persists which ever method I use.

Shell.Run "cd """ & MyDocumentsPath & """", 1, True
Shell.Run "cd " & Chr(34) & MyDocumentsPath & "Chr(34)", 1, True

I also tried to directly type a file path (with no spaces), but that failed too.

Shell.Run "cd ""C:\Users\GardD""", 1, True

Is anybody able to spot any issues? Here is my full code -

Dim Shell As Object ' An instance of the 'Shell' object
Set Shell = VBA.CreateObject("WScript.Shell")

Dim MyDocumentsPath As String   ' The path to the current users 'My Documents' folder
MyDocumentsPath = GetMyDocumentsPath

Shell.Run "cd " & MyDocumentsPath, 1, True  ' Change the Shell start location to the users 'My Documents' folder
Shell.Run DIR_CMD & DIR_FILE_PATH, 1, True  ' Output the full file paths of all files in the users 'My Documents' folder

Set Shell = Nothing ' Destroy the 'Shell' object

Upvotes: 1

Views: 6400

Answers (1)

Alex K.
Alex K.

Reputation: 175748

cd is not an executable; its a command that only exists within a (cmd.exe) console session, ditto for dir if that's what's in DIR_CMD.

You could Shell.Run "cmd /c cd c:\temp & dir *.xls", 1, true

Probably better to use VBA to do whatever you want to do at the command line.

Upvotes: 4

Related Questions