OLE and Excel - The Basics

Keywords: 	 OLE Excel basics

The following script covers the basics.

It is a good way to collect information about a spreadsheet prior to selecting or modifying data.

; ///////////////////////////////////////////////////////
; Winbatch ADO Script - For EXCEL
;
; Prompts user for Excel Spreadsheet. It then creates an INI
; file to Record information about the Spreadsheet. This is
; probably not the most efficient method to obtain the information
; but it works and exposes newer users to the 'how to' when
; using Winbatch with the OLE Extender.
;
; here is example output from a spreadsheet with two worksheets
; [WorkBook]
; File=E:\wbdemo\test.xls
; WorkSheets=2
; Sheet1=TQUERY
; Sheet2=RAO
; [Ranges]
; Number=3
; Range1=CARS
; Ref1=TQUERY!$A$1:$E$16
; Range2=Database
; Ref2=RAO!$A$1:$J$11
; Range3=RAO
; Ref3=RAO!$A$1:$J$11
;
; This makes future navigation of the Workbook a more 'data-driven'
; operation
;
; Stan Littlefield 07/04/2000
; ///////////////////////////////////////////////////////

; obtain the desired spreadsheet
cXLS  = AskFilename( "Select Excel Spreadsheet", DirGet(), "Excel 97|*.XLS","*.XLS",1)
cXLS  = FileNameShort(cXLS)
nXLS  = StrIndexNc(cXLS,".",1,@FWDSCAN)
cXLS  = StrSub(cXLS,1,nXLS-1)

; determine INI file name and delete if it already exists
cINI  = strcat(cXLS,".INI")
IF FileExist(cINI) == @TRUE
  FileDelete(cINI)
Endif

;Create the WorkBook Entry
IniWritePvt("WorkBook","File",strcat(cXLS,".xls"),cINI)

BoxOpen("Opening %cXLS%","Recording in %cINI%")

DB   = ObjectOpen("Excel.Application")
oAPP = DB.Workbooks

oAPP.Open(cXLS)
oWKS = DB.Worksheets
nCnt = oWKS.Count()


;enter number of worksheets and their names
IniWritePvt("WorkBook","WorkSheets",nCnt,cINI)
BoxText("Examining WorkSheet(s)")

For i=1 To nCnt
   oWS = DB.Worksheets(i)
   oWS.Activate
   cname=oWS.Name
   IniWritePvt("WorkBook",strcat("Sheet",i),cname,cINI)
Next

;enter all Ranges, their reference points, with parent WorkSheet
oNames = DB.Names
oCnt   = oNames.Count()
IniWritePvt("Ranges","Number",oCnt,cINI)
BoxText("Examining Range(s)")

For i=1 To oCnt
   rname = DB.Names(i)
   cref  = rname.RefersTo
   cref  = strsub(cref,2,-1) ; this eliminates == in INI File
   rname = rname.Name

   IniWritePvt("Ranges",strcat("Range",i),rname,cINI)
   IniWritePvt("Ranges",strcat("Ref",i),cRef,cINI)
Next

ObjectClose(oWS)
oAPP.Close()
ObjectClose(oAPP)
ObjectClose(DB)
BoxShut()
Run("Notepad",cINI)
exit

Article ID:   W14698

Filename:   OLE and Excel - The Basics.txt

File Created: 2000:07:17:11:36:10

Last Updated: 2000:07:17:11:36:10

Database Search

TechHome

OLE and Excel - The Basics