.. index::
   single: XL Command Examples
   single: XL Commands
   single: Excel Commands

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.