Okay maybe we can finally put this thread to rest. I did some research into Excels object model and here is some sample code I came up with:
;**********************************************************************
; Writing A Two Dimensional WIL Array To The Worksheet
;
; If you have a 2 dimensional array, you need to use Resize to resize the destination range to the proper size.
; The first dimension is the number of rows and the second dimension is the number of columns.
;
; The code below illustrates writing an array out to the worksheet starting at cell C3.
;
; startingcell = "C3"
; oDest = oWS.Range(startingcell).Resize(numrows,numcols);
;
; You can transpose the array when writing to the worksheet:
;
; oDest.Value = oXL.WorkSheetFunction.Transpose(arrData)
;
; Reference: http://www.cpearson.com/excel/ArraysAndRanges.aspx
;
;
; Deana Falk 2014.03.06
;**********************************************************************
; Define WB array
arrData = ArrDimension(3,5)
arrData[0,0] = "Name"
arrData[0,1] = "Address"
arrData[0,2] = "City"
arrData[0,3] = "State"
arrData[0,4] = "Zip"
arrData[1,0] = "Fred Flinstoe"
arrData[1,1] = "55 Stone St"
arrData[1,2] = "BedRock"
arrData[1,3] = "BC"
arrData[1,4] = "01959"
arrData[2,0] = "Barney Rubble"
arrData[2,1] = "123 Agate Ave."
arrData[2,2] = "BedRock"
arrData[2,3] = "BC"
arrData[2,4] = "01959"
; Get number of rows and columns
numrows = ArrInfo(arrData, 1 )
numcols = ArrInfo(arrData, 2 )
; Create Empty workbook ( or optionally open an existing workbook )
oXL = CreateObject("Excel.Application")
oXL.Visible = 1
oXL.ScreenUpdating = 1
oXL.UserControl = 1
oXL.DisplayAlerts = 0
oXL.WorkBooks.Add(-4167)
oWS = oXL.ActiveWorkBook.Worksheets(1)
oWS.Activate()
; IMPORTANT YOU MUST RESIZE THE RANGE FOR THE DESTINATION
startingcell = "C3" ; Starting cell for inserting the data
oDest = oWS.Range(startingcell).Resize(numrows,numcols)
;Pause(oDest.Rows.count,oDest.Columns.count) ; For debugging
; IMPORTANT YOU MUST TRANSPOSE THE ARRAY DATA BEFORE PASSING TO THE EXCEL RANGE
oDest.Value = oXL.WorkSheetFunction.Transpose(arrData)
; Clean up
oDest = 0
oWS=0
oXL=0
Exit
