OK - I'm impressed that you got through a UDF as complex as xlLib in 5 days! Now you know I'm extra tough on my best students!

Here's code that's almost working on my system. I'm getting a COM error on xlRangeWrite, but this is the first time I've tried it on Excel 2007, so I'm not sure if that's related. I'll try it when I get home - I still have Office 2k3 on my laptop. If it works there, I'll look into what's needed for 2K7 support. Try it on 2K3 if you have it.

Couple of things I found:

  • You were right - it's Col/Row, not Row/Col.
  • Function is xlBookCreate, not xlNewBook as shown in the example. I'll fix that in the source.
  • You need to use xlQuit before the script ends, or you'll have many hidden copies of Excel running. If your script dies, you'll need to use Task Manager to kill Excel.
  • The process "'' + $Col + $Row" didn't work (the initial '' forced evaluation as a string). Changing to $Col + CStr($Row) provided a proper "A1" style specification.

Glenn 


;; KixGenerated: 2008/07/30 14:49:36 
Break On
 
Global $DEBUG				; debug flag  
Global $MSG_LOG_			; Message Log file for MSG/DBG UDFs (not used at this time) 
 
Dim $					; generic var 
Dim $Row, $Col				; row and column references  
Dim $I					; index pointer  
Dim $IFile, $OFile			; input/output file names 
Dim $aryData				; data array from CSV 
Dim $oXL, $oWB				; excel pointer vars 
Dim $Range				; range string (A1) 
 
; Load UDFs 
call @ScriptDir + "\xlLib.udf"
call @ScriptDir + "\csv.UDF"
call @ScriptDir + "\msg.UDF"
 
$DEBUG = 1				; enable debugging  
 
$OFile = 'C:\temp\test.xls'
$IFile = 'C:\Temp\test.csv'
$Row = 3
$Col = 'A'
 
 
$oXL = xlInit()				; instantiate Excel 
Dbg('Init: ' + @SERROR)
 
$wBO = xlBookCreate($oXL)		; create a new workbook 
Dbg('NewBook: ' + @SERROR)
 
$ = xlSheetAdd($oXL, 1, 'Sheet1')	; add a worksheed 
Dbg('SheetAdd: ' + @SERROR)
 
$ = xlSheetSelect($oXL, "Sheet1")	; make it active 
Dbg('SheetSelect: ' + @SERROR)
 
If Open(2,$IFile) = 0			; open a source CSV file 
  $aryData = CSV(ReadLine(2))		; read a record and return an array 
  While Not @ERROR
 
    ; for debugging only 
    Dbg('' + (UBound($aryData) + 1) + ' fields were returned by CSV()')
    If $DEBUG
      For $I = 0 to UBound($aryData)
        Dbg('' + $I + ': ' + $aryData[$I])
      Next
    EndIf
 
 
    $ = xlRangeValue($oXL, $Col + CStr($Row), $aryData, 'Sheet1')
    Dbg('RangeValue: ' + @SERROR)
    $Row = $Row + 1		; increment row pointer  
 
    $aryData = CSV(ReadLine(2))	; read next row from input file, if any  
 
  Loop
  $ = Close(2)
Else
  MessageBox(@SERROR, "ERROR", 64)
EndIf
 
$ = xlFile($oXL, 2, $OFile)
Dbg('Save: ' + @SERROR)
$ = xlQuit($oXL)
Dbg('quit: ' + @SERROR)
_________________________
Actually I am a Rocket Scientist! \:D