When designing a new spreadsheet in Microsoft Excel that RPG2SQL will populate or read:

  1. Decide what type of data you need to capture:
    • Detailed data records - Detailed information is required. Multiple data rows will be added to the spreadsheet. 
    • Fixed or changeable column headings - Can be hard-coded in the spreadsheet template and formatted with Excel prior to using the template and then be updated with the RPG2SQL code.

      For example: Setting dates or month names at the top of each column of data and shifting the values depending on which month is current.

    • Individual numeric or character data values - Field values to fill in when generating data into the spreadsheet.

      For example: Column headings, a part number, customer name, rep name, and so on.

  2. Create the new spreadsheet file and add:

    Note: Always leave the first row of the spreadsheet template blank, unless you want to use row one for column names. Start placing data in row 2 instead.

    • As many sheets as required.

      For example: You may want to create a new spreadsheet template with worksheets for each month. 

    • Any static fields you need.
    • Any formulas that you would like to use to summarize data later when RPG2SQL adds data to the spreadsheet.
  3. For detailed record information, in Excel, go to Insert > Name > Define and create a named range. Call the named range something like: JulyDetail or DetailInfo.

    RPG2SQL will later user this named range to add or clear detail records within the spreadsheet. Think of a named range as a pre-defined detail area or single cell within a spreadsheet. A spreadsheet template can have multiple named ranges across multiple worksheets. 


Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: December 10, 2016