iLogic create Table within Excel
Hi @checkcheck_master. The main difficulty in converting this to iLogic, is how to setup/call out the references/imports, so it knows what Excel and its objects are. This is done within the 'Header' area of an iLogic rule. I also often prefer to use a separate custom Function just to 'get' the Excel application object, depending on the situation. Here is an iLogic rule conversion of your VBA macro, that I have included the header content and the custom Function I like to use. (The first 3 lines will automatically transfer themselves into the header area.)
AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Sub Main
Dim oExcelApp As Microsoft.Office.Interop.Excel.Application = GetExcel
Dim oWB1 As Workbook = oExcelApp.ActiveWorkbook
CreateTable(oExcelApp, oWB1, "TableStyleLight10")
End Sub
Private Sub CreateTable(ByVal oExcelApp As Excel.Application, ByVal oWB As Workbook, ByVal TableStyle As String)
Dim oWs As WorkSheet = oWB.ActiveSheet
' Get Last Row and Last Column as numbers
Dim LR As Long = oWs.Cells(oWs.Rows.Count, 1).End(xlUp).row
Dim LC As Long = oWs.Cells(1, oWs.Columns.Count).End(xlToLeft).column
' Create Table
Dim rng As Range = oWs.Cells(1, 1).Resize(LR, LC)
oWs.ListObjects.Add(xlSrcRange, xllistobjecthasheaders:=xlYes, Destination:=rng)
oWs.ListObjects(1).Name = "Table"
oWs.ListObjects(1).TableStyle = TableStyle '"TableStyleLight21"
oWs = Nothing
oWB = Nothing
oExcelApp = Nothing
End Sub
Function GetExcel() As Microsoft.Office.Interop.Excel.Application
Dim oXL As Microsoft.Office.Interop.Excel.Application
Try
'try to find an already running instance of the Excel Application
oXL = GetObject(, "Excel.Application")
Catch
'it wasn't found open, so create an instance of it (start the application)
oXL = CreateObject("Excel.Application")
'oXL = New Microsoft.Office.Interop.Excel.Application
Catch
MsgBox("Failed to Get/Create an instance of the Excel Application. Exiting.", , "")
Exit Function
End Try
Return oXL
End Function
If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click (LIKE or KUDOS) 👍.
If you want and have time, I would appreciate your Vote(s) for My IDEAS 💡 or you can Explore My CONTRIBUTIONS
Wesley Crihfield
(Not an Autodesk Employee)
Hi @checkcheck_master , find these sample code i made sometimes back that creates an ipart and an excel table for it
Just thought i should put it here even for other readers
Public Sub CreateiPart()
Dim oPartDoc As PartDocument
oPartDoc = _InvApplication.Documents.Add(DocumentTypeEnum.kPartDocumentObject,
_InvApplication.FileManager.GetTemplateFile(DocumentTypeEnum.kPartDocumentObject),
True)
oPartDoc.SaveAs("C:\Temp\APIPart.ipt", False)
Dim oTG As TransientGeometry
oTG = _InvApplication.TransientGeometry
Dim oCompDef As PartComponentDefinition
oCompDef = oPartDoc.ComponentDefinition
Dim oPoints(3) As Point2d
oPoints(0) = oTG.CreatePoint2d(0, 0)
oPoints(1) = oTG.CreatePoint2d(5, 0)
oPoints(2) = oTG.CreatePoint2d(5, 5)
oPoints(3) = oTG.CreatePoint2d(0, 5)
Dim oSketch As PlanarSketch
oSketch = oCompDef.Sketches.Add(oCompDef.WorkPlanes(1))
Dim oLines(3) As SketchLine
oLines(0) = oSketch.SketchLines.AddByTwoPoints(oPoints(0), oPoints(1))
oLines(1) = oSketch.SketchLines.AddByTwoPoints(oLines(0).EndSketchPoint, oPoints(2))
oLines(2) = oSketch.SketchLines.AddByTwoPoints(oLines(1).EndSketchPoint, oPoints(3))
oLines(3) = oSketch.SketchLines.AddByTwoPoints(oLines(2).EndSketchPoint, oLines(0).StartSketchPoint)
Dim oProfile As Profile
oProfile = oSketch.Profiles.AddForSolid
Dim oExtrude As ExtrudeFeature
oExtrude = oCompDef.Features.ExtrudeFeatures.AddByDistanceExtent(oProfile, 15,
PartFeatureExtentDirectionEnum.kPositiveExtentDirection,
PartFeatureOperationEnum.kNewBodyOperation, 0)
oExtrude.FeatureDimensions(1).Parameter.Name = "Length"
oExtrude.FeatureDimensions(2).Parameter.Name = "TaperAngle"
Dim oFactory As iPartFactory
oFactory = oCompDef.CreateFactory
Dim oWS As Microsoft.Office.Interop.Excel.Worksheet
oWS = oFactory.ExcelWorkSheet
oWS.Cells(1, 1) = "Member<defaultRow>1</defaultRow><filename></filename>"
oWS.Cells(1, 2) = "Part Number [Project]"
oWS.Cells(1, 3) = "Length<free>150 mm</free>"
oWS.Cells(1, 4) = "TaperAngle"
oWS.Cells(2, 1) = "APIiPart-01"
oWS.Cells(2, 2) = "APIiPart-01"
oWS.Cells(2, 3) = "150 mm"
oWS.Cells(2, 4) = "0 deg"
oWS.Cells(3, 1) = "APIiPart-02"
oWS.Cells(3, 2) = "APIiPart-02"
oWS.Cells(3, 3) = "100 mm"
oWS.Cells(3, 4) = "5 deg"
oWS.Cells(4, 1) = "APIiPart-03"
oWS.Cells(4, 2) = "APIiPart-03"
oWS.Cells(4, 3) = "50 mm"
oWS.Cells(4, 4) = "10 deg"
Dim oWB As Microsoft.Office.Interop.Excel.Workbook
oWB = oWS.Parent
oWB.Save()
oWB.SaveAs("C:\Temp\Test.xls")
oWB.Close()
oPartDoc.Update()
oPartDoc.Save()
End Sub
Fidel Makatia
Developer Advocate
href=https://help.autodesk.com/view/INVNTOR/2022/ENU/?guid=GUID-0BD48573-7193-4285-87B7-6727555D053E rel= "noopener noreferrer">Inventor 2022 Documentation |
Just an additional link about the Worksheet.ListObjects property. It seems like, to use that resource we may need to include more References and/or Imports. The online documentation page for that in Microsoft's site only shows it being used by C#, and says that is used by Visual Studio Tools for Office. It shows the 'Namespace' and the 'Assembly' (.dll file) where it is sourced from. You might be able to use that info to include its recognition / functionality within the iLogic rule. I have never tried using that specific property or object type before, so I just left that part of the code alone for now.
Wesley Crihfield
(Not an Autodesk Employee)
Just from a first glance, without digging into it yet, I can already see two potential problems in the code you just posted.
Dim's As Longshould be like this:
Dim s As Longand in this next line:
Set rCells = oExcelApp.Cells(1, s)you need to get rid of the 'Set' keyword at the beginning of the line, because that is pretty much only used in VBA.
I also think it is a little odd to be using the 'Cells' property directly from the Excel.Application object. Usually that is called from a Worksheet object directly (avoids confusion of which Workbook and which Worksheet it should be focusing on), or from a Range object directly. Also, within the same line of code (setting the value of 'ILastColumn'), I see a variable being used 'xlToLeft', within the End() call, but I do not see where that variable is being passed to that Function, or where it is being defined before that point within that Function. So, I don't if that variable has any value/meaning there. I also see that that Function (FindColumn) is supposed to return a Long type value, but I don't see anywhere within that function where it is supposed to 'Return' any value. And all the 'Input' variables are ByVal, instead of ByRef, so I don't think a value could be returned that way either. Also, you generally don't need to use the keyword 'Call' in iLogic.
Wesley Crihfield
(Not an Autodesk Employee)
