PPWIZARD is a free preprocessor for HTML, REXX, Visual Basic or any text files.
#import - EXCEL via VbScript |
This is an example of how Excel data can be automatically exported from a spreadsheet into a CSV and then read into ppwizard.
It is a bit crude but would provide an excellent starting point for anyone wishing to do similar.
The spread-sheet has 3 columns and ends when the "A" column contains an empty value, the code follows:
;---------------------------------------------------------------------------- ;--- Create VbScript -------------------------------------------------------- ;---------------------------------------------------------------------------- #Output "out\DOIT.VBS" ASIS '--- Some values --------------------------------------------------------- CsvName = "EXCEL.CSV" 'Short name (created in "TMP" dir) XlSheet = 1 'WorkSheet Number '--- Create Log File ----------------------------------------------------- set WshShell = WScript.CreateObject("WScript.Shell") set WshSysEnv = WshShell.Environment("USER") set fso = CreateObject("Scripting.FileSystemObject") CsvName = WshShell.ExpandEnvironmentStrings(WshSysEnv("TMP")) & "\" & CsvName set CsvFile = fso.CreateTextFile(CsvName, True) '--- Create Excel object ------------------------------------------------- set XlObj = Wscript.CreateObject("Excel.Application") '--- Open the EXCEL Spread sheet ----------------------------------------- call XlObj.Workbooks.Open("C:\TMP\EXCEL\SIMPLE.XLS",0) XlObj.Sheets(XlSheet).Activate '--- Loop through rows until we find a cell in "A" that is empty --------- Row = 1 do until XlObj.Range("A:A").Cells(Row).Text = "" 'Wscript.Echo ">>>>>>>>>>>>>>" & XlObj.Range("A:A").Cells(1).Text 'Wscript.Echo "@@@@@" & XlObj.Range("A1").value & "@@@@@@@@" '--- Get data from first 3 cells ------------------------------------- Cell1 = XlObj.Range("A:A").Cells(Row).Text Cell2 = XlObj.Range("B:B").Cells(Row).Text Cell3 = XlObj.Range("C:C").Cells(Row).Text '--- Display Data ---------------------------------------------------- Record = Quote(Cell1) & "," + Quote(Cell2) & "," + Quote(Cell3) CsvFile.WriteLine(Record) '--- Look at next row ------------------------------------------------ Row = Row + 1 loop Wscript.Echo "Wrote " & cstr(Row-1) & " rows to """ & CsvName & """" '--- Close Generated file ------------------------------------------------ CsvFile.close() '--- Close Excel object -------------------------------------------------- XlObj.Quit '--- That's All Folks ---------------------------------------------------- Wscript.Quit 0 '========================================================================= function Quote(What) ' ' If the cell contains a value that needs quoting then quote it, take care of ' any double quotes (get doubled up) '========================================================================= if instr(1, What, " ") <> 0 OR instr(1, What, ",") <> 0 OR instr(1, What, """") <> 0 then Quote = """" & replace(What, """", """""") & """" else Quote = What 'No need to quote end if end function #Output ;---------------------------------------------------------------------------- ;--- Executes the VbScript -------------------------------------------------- ;---------------------------------------------------------------------------- #DefineRexx '' VbRc = AddressCmd("cscript out\doit.vbs") if VbRc <> 0 then error("The VbScript to create CSV from Excel failed", "RC = " || VbRc); #DefineRexx ;---------------------------------------------------------------------------- ;--- Import the result ------------------------------------------------------ ;---------------------------------------------------------------------------- #import ^<?=getenv("TMP") || "\EXCEL.CSV">^ "CMA" "" \ "First Name" \ "Surname" \ "Age"