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.

Extract Data from specific Range in Closed Excel WorkBook

;// Winbatch - Extract Data from specific Range in Closed Excel WorkBook
;              Using ADO
;   Rather than use the Excel Application Object and physically open
;   a workbook then loop through cells to get data from a range
;   perform an ADO query. In this example, we use GetRows() to 
;   process a 2-dimensional array and WB's ArrayFilePutCSV() to save 
;   the data to a file
;
; Stan Littlefield, June 7, 2004 [please retain this header]
;/////////////////////////////////////////////////////////////////////////////

;/// The Big Question: Why Not Just Use Excel? ///////////////////////////////
;
; First, this sample is not a replacement or touted as better than, it is
; more an alternative.  If you just need to get a small range of data out of
; a closed worksheet, this works well. If you use the Excel.Application object,
; have visible=@FALSE, and something goes wrong, you can end up with an orphan,
; have to re-boot, or answer questions as to why further attempts to open the
; worksheet say "Read-Only".
;
; Performing ADO queries with Excel has a few syntax quirks, and the sample
; has given several options which you can mix-and-match to view the results
;
;//////////////////////////////////////////////////////////////////////////////


;// Set Up Error Handler and Check For Files //
IntControl(73,1,0,0,0)
cXLS = StrCat( FilePath(IntControl(1004,0,0,0,0)),"ClosedXLS.XLS")
If ! FileExist(cXLS) Then exit
cOut = StrReplace( StrUpper(cXLS),"XLS","TXT")
If FileExist(cOut) Then FileDelete(cOut)
BoxOpen("Please Wait","Extracting Excel Range From Closed WorkBook")

;// The Connection String - test either by commenting out the other ///
; create Coonection String with Excel Driver
Conn = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=%cXLS%;"
; or the OLEDB Provider [check if you have Excel 8.0 or 9.0 installed]
;Conn= "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%cXLS%; Extended Properties=Excel 8.0;"
;////////// Connection String Selected //////////////////////////////////////////////////////////

;// Create Connection Object and define data ranges //
DB = ObjectCreate("ADODB.Connection")
DB.Open(Conn)
sheet = "Sales"       ;valid WorkSheet Name
range = "CarSales"    ;valid Range Name                           
range1 = "A4:F10"     ;actual range (in this case for CarSales)
createHeader = 1

;// Choose a method to get obtain the data - syntax is important
;// and the [ ] brackets should be used as shown
;////////////////////////////////////////////////////////////////////// 
; to obtain just the named range
RS = DB.Execute("Select * FROM %range%;")
; to obtain from a range within a sheet
;RS = DB.Execute("Select * FROM [%sheet%$%range1%];")
; to obtain the whole sheet
;RS = DB.Execute("Select * FROM [%sheet%$];")
;///// Data Has Been Selected From Range or Sheet //////////////////////

;// since you are saving as CSV, this will create Headers
;// Note: in this example, if you select the entire sheet
;//       the headers may look confusing
;///////////////////////////////////////////////////////////////////////
h = ""
If createHeader
   ForEach f in RS.Fields
      h = StrCat(h,f.Name,",")
   Next
   h = StrSub(h,1,Strlen(h)-1)
Endif
;//////////////////////////////////////////////////////////////////////

; send data to Winbatch Array and display it
ArrayFilePutCSV(cOut , RS.GetRows() , ",", @FALSE,2)
FilePut(cOut,StrCat(h,@CRLF,FileGet(cOut)) )
Message("Data For Range %range%",FileGet(cOut))
;/////////////////////////////////////////////////////////////////////

;// Close Object and Clear
RS.Close()
RS = 0
BoxShut()
DB.Close()
DB = 0
Exit

:WBERRORHANDLER          
IntControl(73,1,0,0,0)
If RS<>0 Then RS=0
IF DB<>0 Then DB=0
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 = StrCat(TimeYmdHms(),"|",StrFixLeft(GetTickCount()," ",10))

WbErrorFile = StrCat(DirWindows(0),"WWWBATCH.INI")
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("","","",WbErrorFile)

WbErrorMsgText = StrCat(WbErrorDateTime,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Current Script: ",WbErrorHandlerFile,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"LastError #:",WbError,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Error Text: ",wberrortextstring,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"[Additional] ",wberroradditionalinfo,@CRLF,@CRLF)
; Line in script that caused Error.
WbErrorMsgText = StrCat(WbErrorMsgText,"WbErrorHandlerLine:",@CRLF,WbErrorHandlerLine,@CRLF)
; Offset into script of error line, in bytes.
WbErrorMsgText = StrCat(WbErrorMsgText,"WbErrorHandlerOffset: ",WbErrorHandlerOffset,@CRLF)
; Variable being assigned on error line, or "" if none.
WbErrorMsgText = StrCat(WbErrorMsgText,"WbErrorHandlerAssignment: ",WbErrorHandlerAssignment,@CRLF)
If (WbErrorHandlerAssignment>"") Then %WbErrorHandlerAssignment% = "UNKNOWN"
ClipPut(WbErrorMsgText)
WbErrorMsgText = StrCat(WbErrorMsgText,"[Error Copied To Clipboard]")
Message("wbErrorHandler",WbErrorMsgText)
Exit
;///////////////////////////////////////////////////////////////////////

Article ID:   W16616

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