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.

Basics with Excel OLE

; Winbatch 2004G - Basics with Excel OLE
; Covers: adding a new workbook, eliminating unnecessary sheets
;         hiding gridlines
;         inserting data from text file with added validation column
;         auto-fitting and formatting columns
;         creating a named range
;         limiting the scrollable area
;
; Stan Littlefield, December 17, 2004
;/////////////////////////////////////////////////////


; What the script does
; You are given a csv file with 4 fields (in the header line) but only 3 
; fields have data. Yo want to place the csv in Excel, create a validation
; for the 4th field, create a named range for the size of the actual data,
; and limit the scrollable are of the final worksheet to the named range/


; set up error-handler
IntControl(73,1,0,0,0) 
gosub udfs

; check if Excel is installed
If ! IsInReg("Excel.Application")
   display(2,"Cannot Run Script","Requires Microsoft Excel To Be Installed")
   Exit
Endif

; create constant values
path = dirscript()
xlWait=2
xlDefault=-4143
xlValidateList = 3
xlValidAlertStop = 1
xlBetween = 1

; does csv exist?
cFile = StrCat(path,"cars.csv")

If ! FileExist(cFile)
   display(2,"Cannot Continue","%cFile% is Missing")
   Exit
Endif

; open Excel and process
; NOTE: this is row by row processsing, and while there are other
;       methods which may be faster, and you could turn off screen
;       updating, but that would probably require a message box to
;       tell the user what is going on
oXL = CreateObject("Excel.Application")
oXL.Visible          = @TRUE  ; change this to @FALSE to run hidden
oXL.ScreenUpdating   = @TRUE  ; change to @FALSE to speed up processing 
oXL.UserControl      = @TRUE
oXL.DisplayAlerts    = @FALSE
oXL.WorkBooks.add()
; we only need 1 worksheet
n = oXL.ActiveWorkbook.Worksheets.Count
If n>1
   While n>1
      oWS = oXL.ActiveWorkbook.Worksheets(2).Delete()
      n = oXL.ActiveWorkbook.Worksheets.Count
   Endwhile
Endif
oWS = oXL.ActiveWorkBook.Worksheets(1)
; name it and clear gridlines
cN = "Vehicles"
oWS.Name = cN
oXL.ActiveWindow.DisplayGridlines = @False

; open and process csv with WB file functions
h = FileOpen(cFile,"READ")
r=1
:start
x = Fileread(h)
If x == "*EOF*" Then goto end
rowins()
r=r+1
goto start

:end
FileClose(h)
oXL.ActiveWorkBook.Names.Add(::Name="NewCars",RefersTo="=%cN%!$a$1:$d$%r%")
oWS.Range("NewCars").Select()
oXL.Selection.Font.Name = 'Tahoma'
oXL.Selection.Font.Size = 9
oXL.Selection.Font.Bold = @True
oWS.Range("NewCars").Columns.Autofit()
oWS.Cells(1,1).Select()
oWS.Range("A1:D1").Interior.ColorIndex = 6
oWS.ScrollArea = "$a$1:$d$%r%"

:done
oWS=0
oXL=0
Message("Data Input Complete","Please Check then Save Spreadsheet")

Exit


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

Exit

;###################################################################
;Error Handler, Registry and Excel UDS
;###################################################################
:udfs
#DefineSubroutine rowins()
IntControl(73,1,0,0,0)

ClipPut("")
ClipPut(StrReplace(x,",",@TAB)) 
oWS.Paste(oWS.Cells(r,1))
oWS.Cells(r,4).Select()
oV = oXL.Selection.Validation
oV.Delete()
oV.Add(::Type=xlValidateList, AlertStyle=xlValidAlertStop, Operator=xlBetween, Formula1="DOMESTIC,IMPORT,TRUCK,UNK")
oV.IgnoreBlank = @True
oV.InCellDropdown = @True
oV.InputTitle = "Unit Type"
oV.ErrorTitle = ""
oV.InputMessage = "Please Select "
oV.ErrorMessage = ""
oV.ShowInput = @True
oV.ShowError = @True
oV=0
Return(1)

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

#EndSubroutine
  


#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


#DefineFunction isInReg(cProg)
Return( RegExistKey(@RegClasses,cProg) )
#EndFunction

Return

CARS.CSV 

Year,Make,Model,Unit_Type
2000,Acura,Integra
2000,Acura,NSX
2000,Acura,RL
2000,Acura,TL
2000,Audi,A4
2000,Audi,A6
2000,Audi,A8
2000,Audi,S4
2000,Audi,TT
2000,BMW,3 Series
2000,BMW,5 Series
2000,BMW,7 Series
2000,BMW,M
2000,BMW,M5
2000,BMW,X5
2000,BMW,Z3
2000,BMW,Z8
2000,Buick,Century
2000,Buick,LeSabre
2000,Buick,Park Avenue
2000,Buick,Regal
2000,Cadillac,Catera
2000,Cadillac,DeVille
2000,Cadillac,Eldorado
2000,Cadillac,Escalade
2000,Cadillac,Seville
2000,Chevrolet,Astro
2000,Chevrolet,Astro Cargo
2000,Chevrolet,Blazer
2000,Chevrolet,C/K 2500 Series
2000,Chevrolet,C/K 3500 Series
2000,Chevrolet,Camaro
2000,Chevrolet,Cavalier
2000,Chevrolet,Corvette
2000,Chevrolet,Express
2000,Chevrolet,Express Cargo
2000,Chevrolet,Impala
2000,Chevrolet,Lumina
2000,Chevrolet,Malibu
2000,Chevrolet,Metro
2000,Chevrolet,Monte Carlo
2000,Chevrolet,Prizm
2000,Chevrolet,S-10
2000,Chevrolet,Silverado 1500
2000,Chevrolet,Silverado 2500
2000,Chevrolet,Suburban
2000,Chevrolet,Tahoe
2000,Chevrolet,Tahoe Limited/Z71
2000,Chevrolet,Tracker
2000,Chevrolet,Venture
2000,Chrysler,300M
2000,Chrysler,Cirrus
2000,Chrysler,Concorde
2000,Chrysler,Grand Voyager
2000,Chrysler,LHS
2000,Chrysler,Sebring
2000,Chrysler,Town and Country
2000,Chrysler,Voyager
2000,Daewoo,Lanos
2000,Daewoo,Leganza
2000,Daewoo,Nubira
2000,Dodge,Avenger
2000,Dodge,Caravan
2000,Dodge,Dakota
2000,Dodge,Durango
2000,Dodge,Grand Caravan
2000,Dodge,Intrepid
2000,Dodge,Neon
2000,Dodge,Ram Pickup 1500
2000,Dodge,Ram Pickup 2500
2000,Dodge,Ram Pickup 3500
2000,Dodge,Ram Van
2000,Dodge,Ram Wagon
2000,Dodge,Stratus
2000,Dodge,Viper
2000,Ford,Contour
2000,Ford,Contour SVT
2000,Ford,Crown Victoria
2000,Ford,Econoline Cargo
2000,Ford,Econoline Wagon
2000,Ford,Escort
2000,Ford,Excursion
2000,Ford,Expedition
2000,Ford,Explorer
2000,Ford,F-150
2000,Ford,F-150 SVT Lightning
2000,Ford,F-250 Super Duty
2000,Ford,F-350 Super Duty
2000,Ford,Focus
2000,Ford,Mustang
2000,Ford,Mustang SVT Cobra
2000,Ford,Ranger
2000,Ford,Taurus
2000,Ford,Windstar
2000,Ford,Windstar Cargo
2000,GMC,C/K 2500 Series
2000,GMC,C/K 3500 Series
2000,GMC,Envoy
2000,GMC,Jimmy
2000,GMC,Safari
2000,GMC,Safari Cargo
2000,GMC,Savana
2000,GMC,Savana Cargo
2000,GMC,Sierra 1500
2000,GMC,Sierra 2500
2000,GMC,Sonoma
2000,GMC,Yukon
2000,GMC,Yukon Denali
2000,GMC,Yukon XL
2000,Honda,Accord
2000,Honda,Civic
2000,Honda,CR-V
2000,Honda,Insight
2000,Honda,Odyssey
2000,Honda,Passport
2000,Honda,Prelude
2000,Honda,S2000
2000,Hyundai,Accent
2000,Hyundai,Elantra
2000,Hyundai,Sonata
2000,Hyundai,Tiburon
2000,Infiniti,G20
2000,Infiniti,I30
2000,Infiniti,Q45
2000,Infiniti,QX4
2000,Isuzu,Amigo
2000,Isuzu,Hombre
2000,Isuzu,Rodeo
2000,Isuzu,Trooper
2000,Isuzu,VehiCROSS
2000,Jaguar,S-Type
2000,Jaguar,XJR
2000,Jaguar,XJ-Series
2000,Jaguar,XKR
2000,Jaguar,XK-Series
2000,Jeep,Cherokee
2000,Jeep,Grand Cherokee
2000,Jeep,Wrangler
2000,Kia,Sephia
2000,Kia,Spectra
2000,Kia,Sportage
2000,Land Rover,Discovery Series II
2000,Land Rover,Range Rover
2000,Lexus,ES 300
2000,Lexus,GS 300
2000,Lexus,GS 400
2000,Lexus,LS 400
2000,Lexus,LX 470
2000,Lexus,RX 300
2000,Lexus,SC 300
2000,Lexus,SC 400
2000,Lincoln,Continental
2000,Lincoln,LS
2000,Lincoln,Navigator
2000,Lincoln,Town Car
2000,Mazda,626
2000,Mazda,B-Series Pickup
2000,Mazda,Millenia
2000,Mazda,MPV
2000,Mazda,MX-5 Miata
2000,Mazda,Protege
2000,Mercedes-Benz,C43 AMG
2000,Mercedes-Benz,C-Class
2000,Mercedes-Benz,CL-Class
2000,Mercedes-Benz,CLK-Class
2000,Mercedes-Benz,E55 AMG
2000,Mercedes-Benz,E-Class
2000,Mercedes-Benz,M-Class
2000,Mercedes-Benz,ML55 AMG
2000,Mercedes-Benz,S-Class
2000,Mercedes-Benz,SL-Class
2000,Mercedes-Benz,SLK-Class
2000,Mercury,Cougar
2000,Mercury,Grand Marquis
2000,Mercury,Mountaineer
2000,Mercury,Mystique
2000,Mercury,Sable
2000,Mercury,Villager
2000,Mitsubishi,Diamante
2000,Mitsubishi,Eclipse
2000,Mitsubishi,Galant
2000,Mitsubishi,Mirage
2000,Mitsubishi,Montero
2000,Mitsubishi,Montero Sport
2000,Nissan,Altima
2000,Nissan,Frontier
2000,Nissan,Maxima
2000,Nissan,Pathfinder
2000,Nissan,Quest
2000,Nissan,Sentra
2000,Nissan,Xterra
2000,Oldsmobile,Alero
2000,Oldsmobile,Bravada
2000,Oldsmobile,Intrigue
2000,Oldsmobile,Silhouette
2000,Plymouth,Breeze
2000,Plymouth,Grand Voyager
2000,Plymouth,Neon
2000,Plymouth,Prowler
2000,Plymouth,Voyager
2000,Pontiac,Bonneville
2000,Pontiac,Firebird
2000,Pontiac,Grand Am
2000,Pontiac,Grand Prix
2000,Pontiac,Montana
2000,Pontiac,Sunfire
2000,Porsche,911
2000,Porsche,Boxster
2000,Saab,9 - 3
2000,Saab,9 - 5
2000,Saturn,L-Series
2000,Saturn,S-Series
2000,Subaru,Forester
2000,Subaru,Impreza
2000,Subaru,Legacy
2000,Subaru,Outback
2000,Suzuki,Esteem
2000,Suzuki,Grand Vitara
2000,Suzuki,Swift
2000,Suzuki,Vitara
2000,Toyota,4Runner
2000,Toyota,Avalon
2000,Toyota,Camry
2000,Toyota,Camry Solara
2000,Toyota,Celica
2000,Toyota,Corolla
2000,Toyota,ECHO
2000,Toyota,Land Cruiser
2000,Toyota,MR2 Spyder
2000,Toyota,RAV4
2000,Toyota,Sienna
2000,Toyota,Tacoma
2000,Toyota,Tundra
2000,Volkswagen,Cabrio
2000,Volkswagen,EuroVan
2000,Volkswagen,Golf
2000,Volkswagen,GTI
2000,Volkswagen,Jetta
2000,Volkswagen,New Beetle
2000,Volkswagen,Passat
2000,Volvo,C70
2000,Volvo,S40
2000,Volvo,S70
2000,Volvo,S80
2000,Volvo,V40
2000,Volvo,V70

Article ID:   W16604

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