02-03-2022
07:46 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
02-03-2022
07:46 AM
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
(Not an Autodesk Employee)