PPWIZARD is a free preprocessor for HTML, REXX, Visual Basic or any text files.
[Bottom][Contents][Search][Prev]: Importing Fields, Each Record To It's Own File[Next]: Automatically Create Chart Images

#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"
    


[Top][Contents][Search][Prev]: Importing Fields, Each Record To It's Own File[Next]: Automatically Create Chart Images

PPWIZARD Manual
My whole website and this manual itself was developed using PPWIZARD (free preprocessor written by Dennis Bareis)
Friday June 01 2001 at 5:58pm