$title  GAMS Code with WSF Conversion Script for XLS Touchup

*       When gdxxrw is used to write a set to an
*       XLS file, the elements are coded in the Excel
*       file as strings.  In some setting, this 
*       creates problems, particularly when the set is
*       actually a set of integers, such as years, and
*       you wish to use these as keys for a Pivot Report.
*       (It is quite annoying when years in the pivot
*       table do not sort automatically.)

*       It is therefore helpful at times to be able to
*       convert the resulting cells to numeric format.
*       This illustrates yet one imperfect strategy to
*       this end.

set     yr      Set to be written to Excel /2000*2050/;

*       Dump out the set using GDXXRW:

execute_unload 'yr.gdx', yr;
execute 'gdxxrw i=yr.gdx o=yr.xls set=yr rng=pivotdata!a2 rdim=1 cdim=0';

*       Create a backup copy:

execute 'copy yr.xls yr.bak.xls';

*       Convert the first column in the pivotdata worksheet to
*       numeric format:

execute 'cscript numeric_column.wsf yr.xls pivotdata 1';


$onecho >numeric_column.wsf
<?xml version="1.0" ?>
<job>
<!--comment
Script: numbers.wsf Converts a Worksheet Column to Numeric.
-->
 <script language="VBScript">
 <![CDATA[

option explicit

dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")

'check we've atleast v5.6 of WSH
if CDbl(wScript.Version) < CDbl("5.6") then
   wScript.Echo " ***************** "
   wScript.Echo " This script requires atleast v5.6 of Windows Script Host."
   wScript.Echo " Your current version is " & wScript.Version
   wScript.Echo " http://msdn.microsoft.com/downloads/default.asp"
   wScript.Echo "***************** "
   wScript.Quit
end if

if InStr(LCase(wScript.FullName), "wscript.exe") then
   wScript.Echo "You have run this script from the GUI (wscript)" & vbCRLF & _
                "Please rerun from a command prompt as" & vbCRLF & _
                " 'cscript xlsn.wsf File'" & vbCRLF & _
                "Reads file.xls and writes file.gms."
   wScript.Quit
End if

dim xlsfile,worksheet,column

if Wscript.Arguments.Unnamed.Count=3 then
   xlsfile = Wscript.Arguments.Unnamed(0)
   worksheet =  Wscript.Arguments.Unnamed(1)
   column =  cint(Wscript.Arguments.Unnamed(2))
else
   wScript.Echo "Command syntax: xlsnz File"
   wScript.Quit
end if

if not objFSO.FileExists(xlsfile) then
   xlsfile = xlsfile & ".xls"
   if not objFSO.FileExists(xlsfile) then
      wScript.Echo "Did not find XLS file: "& xlsfile
      wScript.Quit
   end if
End if

xlsfile = objFSO.GetAbsolutePathName(xlsfile)
wscript.echo "xlsfile = " & xlsfile

public objExcel         ' Excel instance

private ExcelRunning

ExcelRunning = IsExcelRunning()
If ExcelRunning Then
   Set objExcel = GetObject(,"Excel.Application")
Else
   Set objExcel = CreateObject("Excel.Application")
   objExcel.visible = False
End If
objExcel.Workbooks.Open(xlsfile)
Wscript.Echo "Open xls file: " & xlsfile

private r,i
set r = objExcel.Worksheets(worksheet).UsedRange
for i=0 to r.rows.count
   r.cells(i+1,column).value = cint(r.cells(i+1,column).value)
next

objExcel.DisplayAlerts = False
objExcel.Save 
objExcel.Workbooks.close
objExcel.Application.quit
set objExcel = Nothing

Function IsExcelRunning() 
    Dim xlApp
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    IsExcelRunning = (Err.Number = 0)
    Set xlApp = Nothing
    Err.Clear
    on Error goto 0
End Function

 ]]>
 </script>
</job>

$offecho