Import CSV File Using Office Web Components Spreadsheet

If you are not at all interested in Office Web Components, read no further!

Not the greatest script - I finally tracked down the OWC help file, which has often been reported as missing from the Office 2000/2002 install CD [it was on mine] - but assuming you have a csv file and would like to format, display in a browser window and perhaps allow the user to print, then this is a start.

The script creates a spreadsheet object, imports a csv file (provided) formats and sends to a browser window with menus enabled for printing.

Comments are welcome. I plan to build on this; generate graphs and PivotTables then embed as 'reports' in a master XML file. And, if anyone knows how to make Excel the ContentHandler for the htm file the script produces, please post here.

stan

; Winbatch - Import CSV file using OWC Spreadsheet
;            Output to HTML then display in Browser
;
; Run on Win98 with Office 2002/Web Components Installed
;
; NOTE: in this example the csv file is pre-created, it
;       could be as a result of a database query, or a
;       LAFF database. The result set is displayed as
;       read-only, but I believe there is a way to make
;       Excel the ContentType for the HTML in which case
;       you could edit the cell contents.
;
;            Stan Littlefield, November 9, 2003
;/////////////////////////////////////////////////////

; standard IE Explorer Window UDF
#definesubroutine startMSIE(cURL)
   BoxOpen("Please Wait...","Loading %cURL%...")
   Browser = objectopen("InternetExplorer.Application")
   Browser.TheaterMode = @false
   ; but make it resizable
   Browser.Resizable = @true
   Browser.addressbar = @false
   Browser.statusbar = @false
   Browser.menubar = @true     ; to allow printing
   Browser.toolbar = @false
   browser.visible = @false
   browser.navigate(cURL)
   WaitForPageLoad()
   BoxShut()
   browser.visible = @true
   browserDoc = Browser.Document
   all = browserdoc.all
   return(browser)
#endsubroutine

#DefineSubroutine WaitForPageLoad()  ; assume Browser
   While browser.busy || browser.readystate == 1
      TimeDelay(0.5)
   EndWhile
   While browser.Document.ReadyState != "complete"
      TimeDelay(0.5)
   EndWhile
   return
#EndSubroutine




;//// start of script
; is the Web Component Installed?
If ! RegExistKey(@REGCLASSES,"OWC.Spreadsheet") Then Message("Cannot Continue",StrCat("Office Web Components",@CRLF,"Are Not Installed.") )
intcontrol(73, 1, 0, 0, 0)

; files
cCSV = StrCat(dirget(),"Products.csv")
If ! FileExist(cCSV) Then Exit
cURL = StrCat(dirget(),"PRODUCTS.HTM")
If FileExist(cURL) Then FileDelete(cURL)

; create spreadsheet from csv file
BoxOpen("Creating %cURL%","Please Wait...")
oS            = ObjectOpen("OWC.Spreadsheet")
oS.DataType   = "CSVURL"
oS.CSVURL     = cCSV
oSheet        = oS.ActiveSheet
oRange        = oSheet.UsedRange
oRange.Select
oRange.AutoFitColumns
oI            = oRange.Interior
oI.Color      = "CornSilk"
ObjectClose(oI)
oFont         = oRange.Font
oFont.Bold    = @TRUE
ObjectClose(oFont)
ObjectClose(oRange)
oSheet.Export(cURL, 0)
ObjectClose(oSheet)
ObjectClose(oS)
;///// end of spreadsheet creation

BoxShut()
; display in Browser
br = startMSIE(cURL)
ObjectClose(br)


Exit

:WBERRORHANDLER
intcontrol(73, 1, 0, 0, 0)
return
exit
;//////////////////////////////////////////////////////

Article ID:   W16163

File Created: 2004:08:02:10:29:40

Last Updated: 2004:08:02:10:29:40

Database Search

TechHome

Import CSV File Using Office Web Components Spreadsheet