# How Do I Create My First Spreadsheet?

**Goals of this Exercise:**

- Create a new Spreadsheet
- Create a reference to cells A1 to A3 (A1:A3)
- Set the content of cell A1 of the first sheet to "Value A1"
- Set the content of cell A2 of the first sheet to "Value A2"
- Set the content of cell A3 of the first sheet to "Value A3"
- Save the Spreadsheet as a MS Excel file named "First_Excel_Exercise.xlsx"

**Required vocabulary:**

- XCEL_Application_Show
- XCEL_Worbook_Create
- XCEL_Range_Ref_Add
- XCEL_Range_Values_Set
- XCEL_WorkBook_SaveAsFile

## Showing the MS Excel Application

On Windows, the **XCEL_Application_Show **is required for showing** **MS Excel on screen.

On Mac, this command is forcing the MS Application to come on the top of other opened applications.

For testing the result of our work it could be useful to have the things appear on screen and see if all is happening as expected.

## Creating the new Spreadsheet

To create a new worksheet, we are using the **XCEL_Worbook_Create **command.

No parameter is required for this command.

If the workbook has been created with success "the result" is containing the name of the new workbook. This workbook name will be used as a reference to communicate with the workbook object.

**XCEL_Workbook_New****
put** the result into tNewWorkbook

## Preparing a reference to a range in the Spreadsheet

In the Excel Library a reference to a range in an Excel Spreadsheet, is an object we have to define in the Excel Library. This object once created, can be reused anytime we want in the project, until it is removed from memory by the commands **XCEL_Range_Ref_Remove **or** XCEL_Range_Ref_CleanAll**.

A range object is defined by:

- the
**name**of the reference we can use with other Excel Library command. - the
**workbook reference**: Can be the number or the name of any opened workbook. In our example the reference is contained by the tNewWorkbook variable. - the
**worksheet reference**: Can be the number or the name of any opened sheet. In this example we will communicate with the first worksheet, so the reference will be 1 - the
**range reference**: an Excel range reference in the A1 reference style. The reference can be a simple cell (A1) ; a range of cells (A1:A3) or a multiple area selection (A1:C3,D7:E3). We can also use a name defined in MS Excel and associated to a range.

As our goal is to work with cells A1 to A3, we are creating the following reference:

**put** XCEL_Range_Ref_Add("RangeA1_A3", tNewWorkbook, 1, "A1:A3") into tTheRange

## Creating Values in the Spreadsheet

Now we have created a reference to the range A1:A3 of the first worksheet of our new workbook tNewWorkbook, we can fill the 3 cells A1, A2 and A3 by using the **XCEL_Range_Values_Set **command.

The command** XCEL_Range_Values_Set **is requiring** **the following parameters**:**

- A reference to a range. This is the name of the object reference we have created by using the
**XCEL_Range_Ref_Add**function. In this Exercise the tTheRange variable contains the name of the reference - A list of

**put** "Value A1" & cr & "Value A2" & cr & "Value A3" into tCellsData
XCEL_Range_Values_Set tTheRange, tCellsData

## Saving the Spreadsheet as a MS Excel File

It is now time to save our work in a file. For accomplishing this, we are using the **XCEL_WorkBook_SaveAsFile **command.

This command requires the following parameters:

- the
**workbook reference**: Can be the number or the name of any opened workbook. In our example the tNewWorkbook variable. - the
**file path**. This can be a full path or a file name. In our example the name for the Spreadsheet file is "First_Excel_Exercise.xlsx" - the
**file format.**We are using the keyword "native" to save the Spreadsheet in the native MS Excel format (xlsx)

`XCEL_WorkBook_SaveAsFile tNewWorkbook, "First_Excel_Exercise.xlsx", "native"`

## Final Code

```
local tNewWorkbook, tTheRange, tCellsData
XCEL_Workbook_New
put the result into tNewWorkbook
XCEL_Application_Show
put XCEL_Range_Ref_Add("RangeA1_A3", tNewWorkbook, 1, "A1:A3") into tTheRange
put "Value A1" & cr & "Value A2" & cr & "Value A3" into tCellsData
XCEL_Range_Values_Set tTheRange, tCellsData
XCEL_WorkBook_SaveAsFile tNewWorkbook, "First_Excel_Exercise.xlsx", "native"
```

## Charles Szasz

I have collected data from LC fields and used tab a delimiter. How do I past the data into one row of an excel spreadsheet?

## Zryip - ASG Soft

Hi Charles,

We need 2 commands for accomplishing this:

- XCEL_Range_Ref_Add for defining a range inside the Excel Lib

- XCEL_Range_Values_Set for "pasting" the data into the spreadsheet row

Suppose you have collected data for five columns and you want to paste these informations into the third row.

In the Excel notation this range is "A3:D3", but how to use it with the Excel Lib?

In the Excel Lib, we have thought a range, as an alias you can reuse with different commands. The owner of this range is the workbook and inside the workbook, the worksheet. (In Excel several workbooks can be opened at a time, each containing at least one worksheet)

The XCEL_Range_Ref_Add function is allowing users to define a range inside the library. It accepts 4 parameters:

- first, is the name of the range object to use inside the Lib

- second, is the reference to a workbook opened inside Excel. This reference can be a name or number

- third, is the reference to a worksheet of the workbook. This reference can be a name or number

- fourth, is the range in the Excel Notation

The goal of this function, is you have not the need to repeat the workbook, the worksheet and the reference every time, but just the name you defined by using XCEL_Range_Ref_Add.

put XCEL_Range_Ref_Add("myRange","myWorkbookRef","myWorksheetRef","A3:D3") into tMyRange

Now we can add the data inside the worksheet:

XCEL_Range_Values_Set tMyRange, tTheData

Final example script:

on mouseUp

put "Data1 " & tab & "Data2" & tab & "Data3" & tab & "Data4" & tab & "Data5" into tTheData

put XCEL_Range_Ref_Add("myRange","myWorkbookRef","myWorksheetRef","A3:D3") into tMyRange

XCEL_Range_Values_Set tMyRange, tTheData

end mouseUp

Note by using other command available in the Excel Lib you can create something more dynamic, by extending the range from column A to another column (XCEL_uCellRef) and by looking for the next unfilled row (XCEL_Column_LastFilledCell_Get). Have a look to the documentation for more informations.

## Charles Ayton

If I used a spreadsheet as detailed above, would my distibutable project work on user's computer without Excel installed ?

## Mark Wieder

The spreadsheet isn't an application, so the user's computer would need something that can handle spreadsheets. Not necessarily Excel though... I use the free LibreOffice with no problems.