Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.
Keywords: Winbatch ADO Script - For EXCEL
; /////////////////////////////////////////////////////// ; 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 OLE. ; ; 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: W14701
Filename: Winbatch ADO Script - For EXCEL.txt
File Created: 2000:07:06:07:54:36
Last Updated: 2000:07:06:07:54:36