none
Problem getting my head around running a cmd file from within a vbscript (Excel macro) RRS feed

  • Question

  • Excel 2007 on Win 7 Pro 64-bit.

    Trying to refine a script that has been working well for some time.  In the original, I had this line to execute a command file named 'get_linux_reports.cmd' while passing it several command line parms

    Shell cstLogBase & "\get_linux_reports.cmd " & my_pwd & " " & LogDir & " " & SourceSvr & " " & SourceFile & " " & SourceDir

    As I made refinements to other functions of the script, I started having problems arising from what appears to be the fact that control returns to the vbscript immediately, instead of waiting for the command script to complete.  So poking around on the web for other examples (progamming com objects is not my strong suite) I came up with this:

    Dim ftpcmd As String
    ftpcmd = cstLogBase & "\get_linux_reports.cmd " & my_pwd & " " & LogDir & " " & SourceSvr & " " & SourceFile & " " & SourceDir
    Dim objShell
    Set objShell = WScript.CreateObject("WScript.Shell")
    objShell.Run ftpcmd, 1, True

    (that was after several iterations of just trying to get past compile/syntax issues)

    With that, at execution time, I get "run time error 424 - object required" at the line 'set objShell ...'

    Monday, May 18, 2015 3:16 PM

Answers

  • You are no in the Excel developers forum here they will help with VBA issues.

    You are trying to us a COM object inside of a VBA project.  This will not always work as expected.

    You can try it this way:

    Set objShell = CreateObject("WScript.Shell")

    If that doesn't work you will have to use the VBA shell.

    The WScript object is one of those things that cannot be run inside of VBA.  You can try using the Shell VBA command.

    I would use the full ShellExecute method:

    Public Declare Function ShellExecute Lib "Shell32.dll" Alias "ShellExecuteA" _ (ByVal hwnd As Long, _ ByVal lpOperation As String, _ ByVal lpFile As String, _ ByVal lpParameters As String, _ ByVal lpDirectory As String, _ ByVal nShowCmd As Long) As Long Sub RunYourProgram() Dim RetVal As Long On Error Resume GOTo EExit RetVal = ShellExecute(0, "open", "<full path to program>", "<arguments>", _ "<run in folder>", SW_SHOWMAXIMIZED)
    Exit Sub
    EExit:
    ' handle errors End Sub

    See: http://www.suodenjoki.dk/us/productions/articles/vbashellexecute.htm



    \_(ツ)_/


    • Edited by jrv Tuesday, May 19, 2015 5:02 PM
    • Marked as answer by edstevensdba Tuesday, May 19, 2015 6:09 PM
    Tuesday, May 19, 2015 5:00 PM

All replies

  • Hi edsevensdba,

    According to the descirption, this error occured when you create "WScript.Shell" object. Since the issue is more relative to the script developing, I would like to move it to the script developing forum.

    This forum is for developers discussing developing issues involve Excel product, if you have problem with developing Excel automation or customization and etc.,  please feel free to open a new thread in this fourm.

    Sorry for the confusion and thanks for your understanding.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, May 19, 2015 9:17 AM
    Moderator
  • You are asking a VBA question in a scripting forum.  Please post in correct forum for Excel.

    One note - there is not enough information to help you with this.


    \_(ツ)_/


    • Edited by jrv Tuesday, May 19, 2015 10:34 AM
    Tuesday, May 19, 2015 10:34 AM
  • This thread has been moved by moderators 3 times since I originally posted.  If you guys can' agree on where it belongs, how am I supposed to know.  I don't care where it is,  let's just make up our mind and get on with the issue at hand.

    Survey?  Given the history of this thread so far?

    Tuesday, May 19, 2015 4:43 PM
  • You are asking a VBA question in a scripting forum.  Please post in correct forum for Excel.

    One note - there is not enough information to help you with this.


    \_(ツ)_/


    Well, it appears from the history so far that not even the moderators can make up their mind where the thread belongs.

    As for 'not enough information',  I've already provided the relevant block of code, and the error message that it produces. I'll be glad to provide whatever else is needed.  Just tell me what.

    Tuesday, May 19, 2015 4:45 PM
  • You are no in the Excel developers forum here they will help with VBA issues.

    You are trying to us a COM object inside of a VBA project.  This will not always work as expected.

    You can try it this way:

    Set objShell = CreateObject("WScript.Shell")

    If that doesn't work you will have to use the VBA shell.

    The WScript object is one of those things that cannot be run inside of VBA.  You can try using the Shell VBA command.

    I would use the full ShellExecute method:

    Public Declare Function ShellExecute Lib "Shell32.dll" Alias "ShellExecuteA" _ (ByVal hwnd As Long, _ ByVal lpOperation As String, _ ByVal lpFile As String, _ ByVal lpParameters As String, _ ByVal lpDirectory As String, _ ByVal nShowCmd As Long) As Long Sub RunYourProgram() Dim RetVal As Long On Error Resume GOTo EExit RetVal = ShellExecute(0, "open", "<full path to program>", "<arguments>", _ "<run in folder>", SW_SHOWMAXIMIZED)
    Exit Sub
    EExit:
    ' handle errors End Sub

    See: http://www.suodenjoki.dk/us/productions/articles/vbashellexecute.htm



    \_(ツ)_/


    • Edited by jrv Tuesday, May 19, 2015 5:02 PM
    • Marked as answer by edstevensdba Tuesday, May 19, 2015 6:09 PM
    Tuesday, May 19, 2015 5:00 PM
  • One step forward!  ;-)

    Now my code looks like this.  Excuse me for not encloseing it in code tags, but when I try that it becomes severely malformed, with all lines concatenated into a single line.

    Dim ftpcmd As String
    ftpcmd = cstLogBase & "\get_linux_reports.cmd " & pwd_itdwprod & " " & LogDir & " " & SourceSvr & " " & SourceFile & " " & SourceDir
    Dim objShell
    Set objShell = CreateObject("WScript.Shell")  ' this is the line that changed.
    objShell.Run ftpcmd, 1, True

    Now, instead of error on "Set objShell", we get to "objShell.run", which returns "run-time error '-2147024894 (8000700002)'   Method 'Run' of object IWshShell3' failed."

    Tuesday, May 19, 2015 5:45 PM
  • Found it!

    When building a string value to assign to the variable 'ftpcmd', which is the command to be executed by objShell.run, the first part is a variable containing the name of the directory where the actual .cmd file is located.  That directory has embedded spaces in the name.  When I relocated the .cmd file to simply C:\temp (and adjusted the value of 'ftpcmd' accordingly) it worked like a champ.

    Thanks for getting me over the first hurdle.

    Tuesday, May 19, 2015 6:09 PM
  • 0x80070002 equates to "file not found".


    \_(ツ)_/

    Tuesday, May 19, 2015 7:41 PM