- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Hi,
I'm currently working on a rule which exports the bom to an excel. I need to add an additional column which adds the name of the main assembly to each row, as shown in the attached image.
I currently have something like this:
'Add additional data to macro excel GoExcel.CellValue("C:\User\Desktop\" & test & ".xlsm", "Parts List", "K1") = "Mainassy" GoExcel.CellValue("C:\User\Desktop\" & test & ".xlsm", "Parts List", "L1") = "assembly1" GoExcel.CellValue("C:\User\Desktop\" & test & ".xlsm", "Parts List", "M1") = "Amount" GoExcel.CellValue("C:\User\Desktop\" & test & ".xlsm", "Parts List", "N1") = "1"
But i need something that will add the text to all cells in a column instead of just one.
Does anyone know if there is a possibility to do this?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Hi @hldr. I think you may be talking about this.
Dim oVals As IList(Of String) = {"assembly1", "assembly1", "assembly1", "assembly1", "assembly1", "assembly1" }
GoExcel.CellValues("H2", "H7") = oVals
That will write that set of values to all the cells in a single column, which you specify the starting cell and end cell for. I just used data already seen in your Excel image, as an example.
Wesley Crihfield
(Not an Autodesk Employee)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
hi @WCrihfield , thank you for your reply.
I was afraid that this would indeed be the only possible solution within iLogic. The example here was quite a short parts list, but unfortunately most parts lists will be way longer. This would mean I have to make a list with so many values. I think i'll just let ilogic write one value to the excel, and let excel run a macro to drag this value to all following rows.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
The key to populating cells like your requesting in excel is really where do you want to start and stop your input of data.
Because the range can be any size you need to try and find out what the size may be. You want to know where the last blank cell is in a specific column in many cases.
Here is a go excel method to achieve this.
GoExcel.Open(""C:\User\Desktop\" & test & ".xlsm"", "Parts List") 'Column Title GoExcel.CellValue("K1") = "Mainassy" GoExcel.CellValue("L1") = "assembly1" GoExcel.CellValue("M1") = "Amount" GoExcel.CellValue("N1") = "1" For i = 2 To 1000 'Loop to a known row number past the populated data in the excel sheet Cellvalue = GoExcel.CellValue("A" & i+1) 'Check a known column with cells that are consistently populated like "Item" from a BOM List + 1 then to end on right row 'Cell content GoExcel.CellValue("K"&i) = "Mainassy" GoExcel.CellValue("L"&i) = "assembly1" GoExcel.CellValue("M"&i) = "Amount" GoExcel.CellValue("N"&i) = "1" If Cellvalue = "" Then Exit For Next GoExcel.Save GoExcel.Close
Here is another method using current region to find the table size, then find the last row then use autofill to populate data. You could also use a for loop like above to accomplish the same.
https://docs.microsoft.com/en-us/office/vba/api/excel.range.autofill
https://docs.microsoft.com/en-us/office/vba/api/excel.range.currentregion
'AddReference "Microsoft.Office.Interop.Excel.dll" 'Imports Microsoft.Office.Interop 'Imports Microsoft.Office.Interop.Excel Sub Main() Dim xlApp As New Excel.Application 'start a new instance of Excel xlApp.DisplayAlerts = False xlApp.Visible = True Dim xlWorkbook As Workbook xlWorkbook = xlApp.Workbooks.Open("C:\User\Desktop\" & test & ".xlsm") Dim xlWorksheet As Worksheet xlWorksheet = xlWorkbook.Worksheets.Item("Parts List") 'Column Title xlWorksheet.Range("L1").Value = "assembly1" xlWorksheet.Range("M1").Value = "Amount" xlWorksheet.Range("N1").Value = "1" Dim LastCol,LastRow As Long LastCol = xlWorksheet.Range("A1").CurrentRegion.Columns.Count LastRow = xlWorksheet.Range("A1").CurrentRegion.Rows.Count 'Find last row use any row that will have consistent content 'Cell content xlWorksheet.Range("L2").Value = "assembly1" xlWorksheet.Range("M2").Value = "Amount" xlWorksheet.Range("N2").Value = "1" 'Cell Autofill xlWorksheet.Range("L2").AutoFill(xlWorksheet.Range("L2:L" & LastRow)) xlWorksheet.Range("M2").AutoFill(xlWorksheet.Range("M2:M" & LastRow)) xlWorksheet.Range("N2").AutoFill(xlWorksheet.Range("N2:N" & LastRow)) xlWorkbook.Save 'xlWorkbook.Close (False) 'xlApp.Quit End Sub
I see that you are writing to a macro enabled work book so likely you can do the same within Excel VBA. I have a lot of Macros in my Macro workbook for processing BOM's and these are triggered by a population of a cell then the Excel VBA Macro takes over and finish the rest. It can be easier than converting the VBA macro to VB.NET.
Or if this helped you, please, click (like)
Regards
Alan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Thank you guys! I already gave up, but reading your comments gave me new hope and it's working now! it's something like this now:
wb2 = excelApp.Workbooks.Open(MacroExcel) xlws2 = wb2.WorkSheets(1) 'get partnumber of main asssembly For Each dwgSheet As Sheet In ThisDrawDoc.Sheets If dwgSheet.DrawingViews.Count > 0 Then 'modelFile = dwgSheet.DrawingViews(1).ReferencedDocumentDescriptor.FullDocumentName modelDoc = dwgSheet.DrawingViews(1).ReferencedDocumentDescriptor.ReferencedDocument prtNumber = modelDoc.PropertySets("Design Tracking Properties").Item("Part Number").Value End If Next
oLastRow = xlws.UsedRange.Rows.Count
'Column Title xlws2.Range("K1").Value = "Maakartikel:" xlws2.Range("L1").Value = "Batchgrootte:" 'Add assembly name and production amount to parts Dim MaakRange2 As Range = xlws2.Range("K2:K" & oLastRow) MaakRange2.Value = prtNumber Dim BatchRange2 As Range = xlws2.Range("L2:L" & oLastRow) BatchRange2.Value = Production_Amount 'Save Document As Dim FileName As String FileName = "C:\User\Desktop\" & sFileName & ".xlsm" wb2.SaveAs(FileName) wb2.Close 'excelApp.Quit 'excelApp = Nothing excelApp.Application.Quit