Announcements
Attention for Customers without Multi-Factor Authentication or Single Sign-On - OTP Verification rolls out April 2025. Read all about it here.
WCrihfield
in reply to: hldr

Another fairly common and efficient way to write stuff to Excel is to gather all the data you want to write to Excel into a 2-dimensional array of Object first, then write that whole array of data to Excel at once.  I don't recall if this is possible using those iLogic only GoExcel snippets, but I definitely know it is possible when done the long way.  Here is an example I created some time ago for the task of writing a 2-dimensional array of data to Excel.

AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Sub Main
	'create a 2-dimensional array and fill-in its values
	'when creating a 2D array:
	'- If you are going to assign individual values for each cell you must specify the array's size
	'- If you are setting a block of data to the array all at one time, you don't need to specify its size
	'- first number in (,) is number of rows (minus one, because it starts at zero)
	'- second number in (,) is number of columns (minus one, because it starts at zero)
	Dim oVals(2, 1) As String
	'when setting individual values to a 2D array, specify row index first, then column index)
	oVals(0, 0) = "First"
	oVals(0, 1) = "One"
	oVals(1, 0) = "Second"
	oVals(1, 1) = "Two"
	oVals(2, 0) = "Third"
	oVals(2, 1) = "Three"

	'start a new instance of Excel
	Dim oExcel As New Excel.Application
	oExcel.DisplayAlerts = False
	oExcel.Visible = True
	'create a new Excel document
	Dim oWB As Workbook = oExcel.Workbooks.Add
	'create a new sheet/worksheet
	Dim oWS As Worksheet = oWB.Sheets.Add
	'specify the range of cells in the sheet to put the data
	'when using 'Cells', specify row index first, then column index (starting with one (1))
'	Dim oRange As Range = oWS.Range(oWS.Cells(1, 1), oWS.Cells(3, 2))
	'or
	Dim oRange As Range = oWS.Range("A1:B3")
	'set the cell values to the array values
	oRange.Value = oVals
	
'	save the new Excel document to the hard drive.
'	oWB.SaveAs("C:\Temp\Test.xlsx")

'	'close the workbook (Excel document)
'	oWB.Close

'	'close this instance of Excel
'	oExcel.Quit
End Sub

 

Wesley Crihfield

EESignature

(Not an Autodesk Employee)