Examples For Using the XL Commands

Example 1

vamcr vam.cfg hist
vam hist b
dvam b

# MAKE SAMPLE DATA
fdate 1975 2010
f t = 1974 + @cum(t, 1.0, 0.0)
f y = 1975 / (t - 1975)
f y{2010} = -0.0000001              # Set missing value in 2010

xl open xltest.xls                  # Start Excel  server, open
                                    # the xltest.xls workbook.
xl open worksheet 1                 # Open worksheet 1.
xl write  A 1 "Writing text to file:"
                                    # Write a string to A1.
xl open worksheet 2                 # Open worksheet 2.
xl write A 1 "Record data columns"
xl write A 3 "Year"                 # Record label for the date
xl write A 4 down t 1976 2010       # Record the date
xl write B 3 "y"                    # Record series name
xl write B 4 down y 1976 2010       # Write series 'y' from the
                                    # workspace to the worksheet,
                                    # starting in cell B4 and
                                    # moving down the sheet
xl close                            # Close the workbook.


## READ EXCEL FILE
xl open xltest.xls                  # Open the workbook.
xl replace 0.0                      # Set replacement value for
                                    # missing values codes to 0
XL missing "N/A"                    # Specify the code for
                                    # missing values in the
                                    # Excel file.
xl open worksheet 2                 # Open worksheet 3.
xl read B 4 down b.y1 1976 2010     # Read data into vam bank b.
xl read F 4 right  y2 1976 2010     # Read data into workspace.
xl exit                             # Close workbook, close
                                    # connection to Excel server.

Example 2

XL open BEA\section2all_xls.xls     # Start or attach to Excel
                                    # server, open workbook.
XL missing "....."                  # Replace missing values
do{
   XL open worksheet 2              # Open worksheet 2.
   XL read D %2 right a.inv_cst%1 1987 2004
                                    # Read data.
                                    # Store data in vam bank
   XL open worksheet 3              # Switch to worksheet 3.
   XL read D %2 right a.inv_qst%1 1987 2004
   }(1-66)(10 12 14 16-46 48 50 52 54-74 76 78-84 ) m
xl exit

Example 3 A demonstration of the ability of G7 to read a matrix from an Excel file.

xl open C0319e.xls
xl invisible
xl open worksheet 1
xl replace 0.0
ic Read a full matrix from Excel, store in Vam matrix.
xl matread c(2-18) r(14-17, 19-20, 22-24,26-29,31-32, 34-35) c.AM c(1-17) r(1-17) 2000
xl exit

Do Loops in xl Commands The xl commands can be used in combination with the do loop command. The do command permits us to use loops, which saves time when writing code. These do loops, as in other programming languages, can be nested. Here is an example of reading data into G7 using this technique:

Code without the do loop:

xl read C 3 right va1 1998 2007
xl read C 4 right va2 1998 2007
xl read C 5 right va3 1998 2007

Using the do loop:

do{
  xl read C %1 right va%2 1998 2007
  }(3-5)(1-3)m

In this case, G7 starts to read the spreadsheet in cell C 3 and maps it into a series of workspace variables with root names “va”. It does this sequentially, matching the spreadsheet row number (%1) with the index number (%2). This is a powerful and versatile tool that can be used with many spreadsheet and other commands. For more information on this function, check the Do Loop section of this help file.