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.
I think I might have a solution. Those of you who know about such things please comment on the benefits/pitfalls of the following:
- Using a PC-DMIS script to call an external script causes problems with the COM server if there is too much time delay for the external script to finish. I have been unable to find a way to extend the COM server timeout. If someone knows how to do this it seems like the best solution.
- In the absence of a way to prolong the COM timeout I can use the external command function in PC-DMIS with "Wait" specified.
- To do this I call a .bat file from the PC-DMIS command and pass the arguments to the .bat file.
- The .bat file then calls a VBScript file that looks almost exactly like the script that I was running through the PC-DMIS scripting engine originally. The vbscript received the arguments from the .bat file and the uses Select Case to branch off the first argument to call the correct subroutine. This subroutine then calls the correct excel macro.
It seems like a long way around but it also seems to work. Anybody have a better solution?
Update:
It is confirmed that using the external command with "wait" argument solves the issue with the COM timeout. It seems like it might run a bit faster as well...
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
I believe you will not have 'Server Busy' related problems if the VBA code was not inside Excel (not executed from within Excel).
Recently I was trying to solve a problem where a workbook sheet, when duplicated by external methods (an external VB script for example), caused some text to be truncated to a maximum of 255 characters. Once particular cell in the sheet contained about 20 or 30 characters more than this upper limit. I had no idea why the text was truncated and spent a lot of time looking at everything that might cause this. At some point I tried to manually duplicate the workbook sheet and this was the first time I saw the Excel error message box stating that some cells would be truncated to 255 characters. When I Googled the message I understood the problem, the reasons, and worked out a solution (my solution was to copy the text and write it into the same cell of the duplicated sheet. The text is still truncated when the sheet is duplicated but I don't care since I update the text using the copy from the original). No error messasge popup's appear when controlled externally, I only saw them when interacting directly with Excel.
Server busy is coming from the Pcdmis side of things, not from Excel. It happens when you launch a script from PcDmis and that script launches some other process and waits for that process to return before continuing. When that second process then looses focus for a period of time, often due to some modal window (message box or similar) popping up, the script that launched it gets impatient and issues this message telling you that that it is trying to continue but can't because the external process isn't returning. There is a default timeout value for COM servers that I think is around 5 seconds. Some applications, from what I can gather, allow you access to this parameter through the API and you can set it to something larger. I could not find any way to change the value in PCDMIS.
The method posted above seems to work better than calling through the PCDMIS script. Execution seems to be faster and the timeout issue is completely resolved. Everything is still running in Excel. The only difference is in how Excel is called.
I understand PC-DMIS is the source of the 'Server Busy' error but the cause is from Excel showing a message box or something like that.
If the contents of the macro was outside of Excel you won't get message boxes in Excel and therefore no 'Server Busy' errors from PC-DMIS.
It is just an observation I noticed recently regarding error messages from Excel when manipulated externally. It is nice to try and keep this kind of stuff simple and clean if possible. Anything too complex or with too many steps involved is more likely to break.
