Server Busy error during VBA automation

I have some Excel VBA code that I can call from a PC-DMIS script and it will pull data out of PC-DMIS, manipulate it, and then push it back in. If I generate any message boxes in Excel while this is happening I will get a Server Busy error message if the user doesn't respond to the message box within a few seconds. Does anyone know how to disable this message? I just want it to wait as long as necessary for Excel to finish what it is doing.

Parents

DaSalo over 9 years ago

I had a PM request to post the code for this solution. Here you go:

First call the .BAT file from PC-DMIS using the external command function. Pass the argument, separated by spaces, after the path to the file.

.BAT file:

@ echo Off
echo  Processing. This could take several minutes. Please wait...
@ echo off
cscript //Nologo "PATH_TO_YOUR_VBSCRIPT_HERE.vbs" %1 %2 %3 %4 %5 %6 %7 %8 %9

The .BAT file calls this script and passes the arguments. The first argument is the name of the macro and it branches and applies the other arguments as appropriate. If you pass more or fewer arguments than are required for that macro it will show an error message.

VBScript :

Option Explicit

Dim args,macro,num

Set args = WScript.Arguments
macro = args.Item(0)

Select Case macro

Case "Macro1"
num = args.Count
If num = 3 then
Macro1 args.Item(1), args.Item(2)
else 
MsgBox "Incorrect Number of Arguments Passed",0,"Error!"
End If

Case "Macro2"
num = args.Count
If num = 5 then
Macro2 args.Item(1), args.Item(2), args.Item(3), args.Item(4)
else 
MsgBox "Incorrect Number of Arguments Passed",0,"Error!"
End If

Case Else
MsgBox "Macro Name Not Recognized",0,"Error!"

End Select

Sub Macro1(arg1, arg2)
      Dim xl
      Dim xlBook     
      Dim sPath

      sPath = "C:\YourPath\"
      Set xl = CreateObject("Excel.application")
      Set xlBook = xl.Workbooks.Open(sPath & "Your_Excel_Wrkbk.xlsm", 0, True)      
       xl.Application.Visible = False
       xl.run "module1.Macro1", Cstr(Arg1), Cstr(Arg2)
       xl.Quit

      Set xlBook = Nothing
      Set xl = Nothing
End Sub

Sub Macro2(arg1, arg2, arg3, arg4)
      Dim xl
      Dim xlBook     
      Dim sPath

      sPath = "C:\YourPath\"
      Set xl = CreateObject("Excel.application")
      Set xlBook = xl.Workbooks.Open(sPath & "Your_Excel_Wrkbk.xlsm", 0, True)      
       xl.Application.Visible = False
       xl.run "module1.Macro2", Cstr(Arg1), Cstr(Arg2), CDbl(Arg3), CInt(Arg4)
       xl.Quit

      Set xlBook = Nothing
      Set xl = Nothing
End Sub

Reply

DaSalo over 9 years ago

@ echo Off
echo  Processing. This could take several minutes. Please wait...
@ echo off
cscript //Nologo "PATH_TO_YOUR_VBSCRIPT_HERE.vbs" %1 %2 %3 %4 %5 %6 %7 %8 %9

Option Explicit

Dim args,macro,num

Set args = WScript.Arguments
macro = args.Item(0)

Select Case macro

Case "Macro1"
num = args.Count
If num = 3 then
Macro1 args.Item(1), args.Item(2)
else 
MsgBox "Incorrect Number of Arguments Passed",0,"Error!"
End If

Case "Macro2"
num = args.Count
If num = 5 then
Macro2 args.Item(1), args.Item(2), args.Item(3), args.Item(4)
else 
MsgBox "Incorrect Number of Arguments Passed",0,"Error!"
End If

Case Else
MsgBox "Macro Name Not Recognized",0,"Error!"

End Select

Sub Macro1(arg1, arg2)
      Dim xl
      Dim xlBook     
      Dim sPath

      sPath = "C:\YourPath\"
      Set xl = CreateObject("Excel.application")
      Set xlBook = xl.Workbooks.Open(sPath & "Your_Excel_Wrkbk.xlsm", 0, True)      
       xl.Application.Visible = False
       xl.run "module1.Macro1", Cstr(Arg1), Cstr(Arg2)
       xl.Quit

      Set xlBook = Nothing
      Set xl = Nothing
End Sub

Sub Macro2(arg1, arg2, arg3, arg4)
      Dim xl
      Dim xlBook     
      Dim sPath

      sPath = "C:\YourPath\"
      Set xl = CreateObject("Excel.application")
      Set xlBook = xl.Workbooks.Open(sPath & "Your_Excel_Wrkbk.xlsm", 0, True)      
       xl.Application.Visible = False
       xl.run "module1.Macro2", Cstr(Arg1), Cstr(Arg2), CDbl(Arg3), CInt(Arg4)
       xl.Quit

      Set xlBook = Nothing
      Set xl = Nothing
End Sub

Children

No Data