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

email me  any feedback, additional information or corrections.
See this page online (look for updates)

PPWIZARD Manual

Saturday May 28 2022 at 2:55pm