WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WinBatch
OLE COM ADO CDO ADSI LDAP
OLE with Excel
plus
Samples from Users

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Reference a Specific Excel Workbook Instance

; Winbatch - Excel Object Reference Test
;
; Given that an Excel workbook might be loaded as a 2nd or third instance 
; of the Application Object, using CreateObject("Excel Application") will not
; allow you to manipulate that workbook from within Winbatch. A workaround would
; be to use GetObject() with the fully-qualified pathname. However, there is one
; caveat and two possible error traps.
;
; Caveat: Using GetObject(XLFile) returns a Workbook Object, not an Application Object
; Error Traps: The workbook is not loaded as an instance, but another instance of 
;              Excel is loaded.
;              No instancs of Excel are loaded
;
;
; NOTE: this script looks for and utilizes tn.wsc which it will create if it
;       does not exist.
;////////////////////////////////////////////////////////////////////////////////
gosub udfs

IntControl(73,1,0,0,0)

oXL=0
; indicate the file you will be looking for  
cBook = "C:\Projects\Automation\RegTest\TestData\Cat320.xls"  ; substitute your own name 
                           ; test by (1) loading it as a second instance of Excel
                           ; (2) load as the only instance
                           ; (3) do not load, load another workbook instead
                           ; (4) have no Excel instanes loaded
                           ; (5) load Excel, open cBook, then open a second workbook
                           ; (6) use the WB GetObject(), not the wsc GetApp()
                           ; (7) load excel, do not open any workbooks

; extract file name and check for file on disk
cFile = StrUpper( FileRoot(cBook) )
If ! FileExist(cBook)
   display(1,cBook,"Does Not Exist")
   goto end
Endif

; initiate (and optionally create) a wsc with needed functions
;cWsc = StrCat(dirscript(),"tn.wsc")
;If ! FileExist(cWsc)
;   CrTn(cWsc)
;Endif
;oObj = GetObject(StrCat("script:",cWsc)) 
;                 
; declare an Excel Object variable and attemt to retrieve instance with a call
; to a function in the wsc - because the wsc function can retrieve an Object at the
; Application level
;oXL = oObj.GetApp(cBook)

; as an alternative, use WB, but oXL will be returned as a WorkBook
; comment the GetApp line above and uncomment next line to test 

; td - get the workbook
oWorkBook = GetObject(cBook)

; td - Use the Workbook's applicatioin method to get the application object.
oXL = oWorkBook.Application()

;Free the Workbook object
oWorkBook = 0

; td - basically I am lazy 
cType = "APPLICATION"

If !( VarType(oXL) &1536)==1536  Then goto end ; function call failed
;cType = StrUpper( oObj.GetTn(oXL) ) ; Determine Objects 'typename' - something Winbatch
                                    ; cannot do	  td - And in this case does not need to.


; now process alternatives to see how to proceed
display(1,"Initiated...",cType)
If cType == "WORKBOOK"  ; you used the WB's GetObject()
   cName =  StrUpper( oXL.Name )
   If StrIndex(cName,cFile,0,@FWDSCAN) Then display(2,cBook,"Has been successfully attached")
   ; now do whatever processing you have to but remember
   ; use oXL.Close() NOT oXL.Quit() since it is a Workbook Object
   goto end
Endif

If cType == "APPLICATION"  
   ; first, check if it's a bogus Application Object
   If oXL.WorkBooks.Count < 1
      ; maybe it's loaded but no workbooks loaded
      If oXL.Visible
         display(2,"Excel is Active","No WorkBooks are Open")
      Else
         display(1,"Initiate Failed","Excel is Not Loaded")
      Endif
      goto end
   Endif
   ; now check if the workbook you are after is loaded
   ; TD - this will fail with Excel 10, if Excel is not running when you start the script.
	;      Apparently Excel does not consider new empty workbook to be active even though
	;      it retuns a count of 1 from the call to  "oXL.WorkBooks.Count".  This can be
	;      addressed in several ways, including just trapping the error or checking the 
	;      return value something like following:
	;     if oXL.ActiveWorkbook == 0 then message("Excel Workbook", "No active workbook found.")
	cName =  StrUpper( oXL.ActiveWorkbook.Name )


   If StrIndex(cName,cFile,0,@FWDSCAN) Then display(2,cBook,"Loaded as Active Instance")
   If ! StrIndex(cName,cFile,0,@FWDSCAN) Then display(2,cBook,"Not loaded, But %cName% is")
   ; ok, so maybe it's loaded as an inactive workbook
   isFound = 0
   If oXL.WorkBooks.Count >1  
      ForEach w in oXL.WorkBooks        
         If StrIndex(StrUpper(w.Name),cFile,0,@FWDSCAN) Then isFound = 1
      Next
   Endif
   If isfound Then display(2,cBook,"Is loaded but Not The Active Workbook")
   ; and you would have to make it active

   goto end
Endif

display(2,"Unable To Continue","Excel is Not Active")

:end
oXL = 0

Exit


:WBERRORHANDLER
IntControl(73,1,0,0,0)
If LastError() == 1129
   Display(2,"GetObject Failed","Excel Cannot be Initialzed")
   Exit
Endif
ErrorProcessing(1,1,1)
oXL=0
Exit

:udfs

#DefineFunction CrTn(cWsc)
If FileExist(cWsc) Then Return(0)
var = StrCat('',@CRLF,"",@CRLF,'',@CRLF)
var = StrCat(var,'',@CRLF,'',@CRLF)
var = StrCat(var,'',@CRLF,'	',@CRLF,'		',@CRLF,'	',@CRLF)
var = StrCat(var,'  ',@CRLF,'		',@CRLF,' 	',@CRLF,'',@CRLF)
var = StrCat(var,'',@CRLF)
var = StrCat(var,'',@CRLF,'',@CRLF)
FilePut(cWsc,var)
#EndFunction


#DefineSubroutine ErrorProcessing(deleteIni,showerr,logfile)  
WbError = LastError()
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))
Endif
If showerr Then Message("wbErrorHandler",WbErrorMsgText)
Return(1)
#EndSubroutine

Return
;////////////////////////////////////////////////////////////
Article ID:   W16624

File Created: 2005:02:18:12:21:40
Last Updated: 2005:02:18:12:21:40