none
Cannot execute batch file from VBA macro RRS feed

  • Question

  • This Excel Script has worked for the last 5 years.  All of a sudden, I'm getting an error "Run time Error '5' - Invalid Procedure Call or Argument. This script creates a batch file under C:\apps just fine but doesn't excute it.  Please help...

    Private Sub CommandButton1_Click()
    Const DELIMITER As String = "," 'or "|", vbTab, etc.
            Dim myRecord As Range
            Dim myField As Range
            Dim nFileNum As Long
            Dim sOut As String
            
            nFileNum = FreeFile
            Open "C:\apps\Test.bat" For Output As #nFileNum
            For Each myRecord In Range("B34:B35")
                With myRecord
                    For Each myField In Range(.Cells(1), _
                            Cells(.Row, Columns.Count).End(xlToLeft))
                        sOut = sOut & DELIMITER & myField.Text
                    Next myField
                    Print #nFileNum, Mid(sOut, 2)
                    sOut = Empty
                End With
            Next myRecord
            Close #nFileNum
    Dim stAppName As String
    stAppName = "cmd.exe /k c:\apps\test.bat"
    Call Shell(stAppName, 1)
    End Sub
    Private Sub CommandButton2_Click()
    Const DELIMITER As String = "," 'or "|", vbTab, etc.
            Dim myRecord As Range
            Dim myField As Range
            Dim nFileNum As Long
            Dim sOut As String
            
            nFileNum = FreeFile
            Open "C:\apps\Test.bat" For Output As #nFileNum
            For Each myRecord In Range("B56:B57")
                With myRecord
                    For Each myField In Range(.Cells(1), _
                            Cells(.Row, Columns.Count).End(xlToLeft))
                        sOut = sOut & DELIMITER & myField.Text
                    Next myField
                    Print #nFileNum, Mid(sOut, 2)
                    sOut = Empty
                End With
            Next myRecord
            Close #nFileNum
    Dim stAppName As String
    stAppName = "cmd.exe /k c:\apps\test.bat"
    Call Shell(stAppName, 1)
    End Sub
    Private Sub run_Click()
           Const DELIMITER As String = "," 'or "|", vbTab, etc.
            Dim myRecord As Range
            Dim myField As Range
            Dim nFileNum As Long
            Dim sOut As String
            
            nFileNum = FreeFile
            Open "C:\apps\Test.bat" For Output As #nFileNum
            For Each myRecord In Range("B20:B24")
    
    
    
    
    
    
                With myRecord
                    For Each myField In Range(.Cells(1), _
                            Cells(.Row, Columns.Count).End(xlToLeft))
                        sOut = sOut & DELIMITER & myField.Text
                    Next myField
                    Print #nFileNum, Mid(sOut, 2)
                    sOut = Empty
                End With
            Next myRecord
            Close #nFileNum
    Dim stAppName As String
    stAppName = "cmd.exe /k c:\apps\test.bat"
    Call Shell(stAppName, 1)
    End Sub
    
    


    Private Sub CommandButton1_Click()
    Const DELIMITER As String = "," 'or "|", vbTab, etc.
            Dim myRecord As Range
            Dim myField As Range
            Dim nFileNum As Long
            Dim sOut As String
            
            nFileNum = FreeFile
            Open "C:\apps\Test.bat" For Output As #nFileNum
            For Each myRecord In Range("B34:B35")
                With myRecord
                    For Each myField In Range(.Cells(1), _
                            Cells(.Row, Columns.Count).End(xlToLeft))
                        sOut = sOut & DELIMITER & myField.Text
                    Next myField
                    Print #nFileNum, Mid(sOut, 2)
                    sOut = Empty
                End With
            Next myRecord
            Close #nFileNum
    Dim stAppName As String
    stAppName = "cmd.exe /k c:\apps\test.bat"
    Call Shell(stAppName, 1)
    End Sub
    Private Sub CommandButton2_Click()
    Const DELIMITER As String = "," 'or "|", vbTab, etc.
            Dim myRecord As Range
            Dim myField As Range
            Dim nFileNum As Long
            Dim sOut As String
            
            nFileNum = FreeFile
            Open "C:\apps\Test.bat" For Output As #nFileNum
            For Each myRecord In Range("B56:B57")
                With myRecord
                    For Each myField In Range(.Cells(1), _
                            Cells(.Row, Columns.Count).End(xlToLeft))
                        sOut = sOut & DELIMITER & myField.Text
                    Next myField
                    Print #nFileNum, Mid(sOut, 2)
                    sOut = Empty
                End With
            Next myRecord
            Close #nFileNum
    Dim stAppName As String
    stAppName = "cmd.exe /k c:\apps\test.bat"
    Call Shell(stAppName, 1)
    End Sub
    Private Sub run_Click()
           Const DELIMITER As String = "," 'or "|", vbTab, etc.
            Dim myRecord As Range
            Dim myField As Range
            Dim nFileNum As Long
            Dim sOut As String
            
            nFileNum = FreeFile
            Open "C:\apps\Test.bat" For Output As #nFileNum
            For Each myRecord In Range("B20:B24")
    
    
    
    
    
    
                With myRecord
                    For Each myField In Range(.Cells(1), _
                            Cells(.Row, Columns.Count).End(xlToLeft))
                        sOut = sOut & DELIMITER & myField.Text
                    Next myField
                    Print #nFileNum, Mid(sOut, 2)
                    sOut = Empty
                End With
            Next myRecord
            Close #nFileNum
    Dim stAppName As String
    stAppName = "cmd.exe /k c:\apps\test.bat"
    Call Shell(stAppName, 1)
    End Sub
    
    


    • Next myField
      Print #nFileNum, Mid(sOut, 2)
      sOut = Empty
      End With
      Next myRecord
      Close #nFileNum
      Dim stAppName As String
      stAppName = "cmd.exe /k c:\apps\test.bat"
      Call Shell(stAppName, 1)
      End Sub


    • End Sub

     


    Wednesday, July 18, 2018 4:19 AM

All replies

  • Hello RR95,

    Which line code cause the error? Does the bat file finally generate? If does, is the bat file able to run manually?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 19, 2018 1:45 AM