Another GetObject() rouindup

[stanl]

This topic has been well discussed on this forum, but usually from using GetObject(,"Excel Application"). A major issue, as Tony pointed out, is an Excel application can be closed, but still in ROT.  I have a situation for a script that looks for an active Excel instance, displays worksheets, allows selection of one which is then saved as ^ delimited text. If the excel file is then closed and the script (on a timer) tries again, Getobject() will not function correctly.  I wanted to play around with this in the CLR, so for practice came up with this simple PS try...catch:  ran several tests and seems to work, but converting to WB CLR would involve a little more code. Question: would it be worth it, or is there an easier way with WB not going the CLR route:

Code: [Select]


try {
$xl=[System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application")


$wk = $xl.ActiveWorkbook.FullName
$wk
$ind = $xl.Worksheets.Count
$ind
if ($wk -eq $null) {
   Write-Host "Excel Instance Not Active"
}
else {
foreach ($sh in $xl.ActiveWorkbook.Worksheets) {
   $n = $sh.Name
   $ix = $sh.Index
   $v = "Worksheet # $ix $n"
   $v}
   }
}
catch {
   Write-Host "Excel Instance Not Found"
}


[td]

I am likely missing something but why bother with dotNet at all? COM Automation and WinBatch error handling should be able to do what your managed script is doing with only a few lines of code.

[stanl]

I am likely missing something but why bother with dotNet at all? COM Automation and WinBatch error handling should be able to do what your managed script is doing with only a few lines of code.

Below is some original code I wrote.

• Ran the script with Excel not open - error worked
• Opened an .xlsx file
• Re-ran the script, still got error [attached]
• Then ran my PS code - worked as expected.
• Closed Excel file - PS code reported "Not Active"

Not sure what the secret sauce is for the dotnet code, probably why I posted in first place.

Code: Winbatch

gosub udfs
IntControl(73,1,0,0,0) ;will trap the error
oXL=0
oXL = GetObject(,"Excel.Application")


If oXL<>0
   cnt = oXL.Workbooks.Count
        Message(oXL,"ActiveWorkbooks: ",cnt)
Else
        Message(oXL,"No Active Excel Instances")
Endif


Exit


:WBERRORHANDLER
IntControl(73,1,0,0,0)
ErrorProcessing(1,1,0,0)
oXL=0
Exit


:udfs
#DefineSubroutine ErrorProcessing(deleteIni,showerr,logfile,Err_Array)
If Vartype(Err_Array) ==256
   WbError = Err_Array[0]
   wberrorhandlerline = Err_Array[1]  
   wberrorhandleroffset = Err_Array[2]  
   wberrorhandlerassignment = Err_Array[3]  
   wberrorhandlerfile = Err_Array[4]
   wberrortextstring = Err_Array[5]  
   wberroradditionalinfo = Err_Array[6]  
   wberrorinsegment = Err_Array[7]  
Else
   WbError = LastError()
Endif
WbTextcode = WbError
If WbError==1668||WbError==2669||WbError==3670
   ; 1668 ; "Minor user-defined error"
   ; 2669 ; "Moderate user-defined error"
   ; 3670 ; "Severe user-defined error"
   WbError = ItemExtract(1,IntControl(34,-1,0,0,0),":")
   WbTextcode = -1
EndIf
WbErrorString = IntControl(34,WbTextcode,0,0,0)
WbErrorDateTime = TimeYmdHms()
WbErrorFile = StrCat(DirWindows(0),"WWWBATCH.INI")
If deleteIni
   FileDelete(WbErrorFile)
   IniWritePvt(WbErrorDateTime,"CurrentScript",WbErrorHandlerFile      ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ErrorValue"   ,WbError                 ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ErrorString"  ,WbErrorString           ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ScriptLine"   ,WbErrorHandlerLine      ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ScriptOffset" ,WbErrorHandlerOffset    ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"VarAssignment",WbErrorHandlerAssignment,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"VarInSegment" ,WbErrorInSegment,WbErrorFile)
   IniWritePvt("","","",WbErrorFile)
Endif  
WbErrorMsgText = StrCat(WbErrorDateTime,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Current Script: ",WbErrorHandlerFile,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Error# [",WbError,"]",@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Error Text: ",wberrortextstring,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"[Extended Information] ",wberroradditionalinfo,@CRLF,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"On Line:",@CRLF,WbErrorHandlerLine,@CRLF)
;WbErrorMsgText = StrCat(WbErrorMsgText,"Offset: ",WbErrorHandlerOffset,@CRLF)
If (WbErrorHandlerAssignment>"") Then %WbErrorHandlerAssignment% = "UNKNOWN"
WbErrorMsgText = StrCat(WbErrorMsgText,"Assignment/Variable: ",WbErrorHandlerAssignment,@CRLF)
If (WbErrorInSegment>"") Then WbErrorMsgText = StrCat(WbErrorMsgText,"In UDF/UDS: ",WbErrorInSegment,@CRLF)
If logfile
   cSep = StrCat(StrFill("=",50),@CRLF)
   cLogFile = StrCat(dirscript(),"log.err")
   If ! FileExist(cLogFile) Then FilePut(cLogFile,StrCat("Error Log",@CRLF,cSep))
   FilePut(cLogFile,StrCat(FileGet(cLogFile),WbErrorMsgText,cSep))
   display(2,"An Error Occured",StrCat("written to ",cLogFile))
Else
   If showerr
      WbErrorMsgText = StrCat(WbErrorMsgText,"[THIS ERROR NOT WRITTEN TO LOG FILE]",@CRLF)
      Message("An Error Was Encountered",WbErrorMsgText)
   Endif
Endif
Return(1)
#EndSubroutine


Return
 

[td]

Still not seeing the point. PS is just a thin cover over COM Automation interfaces in this case. You know when and what the errors and WinBatch allows you to trap that error and do whatever processing you want after it happens.

<edit> If you execute your script at the WinBatch default integrity level, you will not be able to detect ROT entries like Excel because Excel runs at low integrity. PS has no secrete sauce, it is just running at a reduced integrity level. If you want the same result from WinBatch simply execute the script at
the reduced level- "yourscript.wbt_if".

[stanl]

the infamous _if...   Thanks for pointing that out.