iLogic and Excel Worksheets

[dleesuk]

Hi All,

I can link to an external Excel spreadsheet, choose the correct tab and collect the data from one specific column, i.e. A1 to A100.

However, I'm having great difficulty in collecting data from subsequent columns, i.e. B1 to B100, C1 to C100, etc.

Does anyone know how I can select the next, and subsequent, columns using iLogic??

This is in relation to a previous post of mine - ilogic and excel integration to draw splines

I've made some progress with the previous problem, but I'm stumped by this column issue.

I've looked through some of the Excel help files, though I'm having difficulty converting to iLogic.

Any help is greatly appreciated

Thank you

[dleesuk]

Hi All,

I was wondering why my posts get ESCALATED??

Whenever this happens I get no answers to anything, ever!!!

I find this quite frustrating

[MechMachineMan]

Something along these lines should work:

xlApp = GoExcel.Application

oCell = xlApp.WorkBooks("C:\ExcelWB.xlsx").Worksheets("Sheet1").Cells(1,1)       'Cell A1.

oNextCell = oCell.Offset(1,0) 'Offset(row, column) +ve direction is down + right

MsgBox(oCell.Value & vblf & MsgBox(oNextcell.Value")

[Anonymous]

I don’t know if this will help but it might be a different way of looking at it. You can use iLogic to grab cell information from excel to fill user parameters.  You can use the user parameters to drive model data. Or take the model data to fill out the excel cell spread sheet.

‘Column A

PointA1=GoExcel.CellValue("3rd Party:Embedding 1", "Sheet1", "A1")

PointA2=GoExcel.CellValue("3rd Party:Embedding 1", "Sheet1", "A2")

PointA3=GoExcel.CellValue("3rd Party:Embedding 1", "Sheet1", "A3")

PointA4=GoExcel.CellValue("3rd Party:Embedding 1", "Sheet1", "A4")

PointA5=GoExcel.CellValue("3rd Party:Embedding 1", "Sheet1", "A5")

PointA6=GoExcel.CellValue("3rd Party:Embedding 1", "Sheet1", "A6")

‘Column B

PointB1=GoExcel.CellValue("3rd Party:Embedding 1", "Sheet1", "B1")

PointB2=GoExcel.CellValue("3rd Party:Embedding 1", "Sheet1", "B2")

PointB3=GoExcel.CellValue("3rd Party:Embedding 1", "Sheet1", "B3")

PointB4=GoExcel.CellValue("3rd Party:Embedding 1", "Sheet1", "B4")

PointB5=GoExcel.CellValue("3rd Party:Embedding 1", "Sheet1", "B5")

PointB6=GoExcel.CellValue("3rd Party:Embedding 1", "Sheet1", "B6")

‘Column C

PointC1=GoExcel.CellValue("3rd Party:Embedding 1", "Sheet1", "C1")

PointC2=GoExcel.CellValue("3rd Party:Embedding 1", "Sheet1", "C2")

PointC3=GoExcel.CellValue("3rd Party:Embedding 1", "Sheet1", "C3")

PointC4=GoExcel.CellValue("3rd Party:Embedding 1", "Sheet1", "C4")

PointC5=GoExcel.CellValue("3rd Party:Embedding 1", "Sheet1", "C5")

PointC6=GoExcel.CellValue("3rd Party:Embedding 1", "Sheet1", "C6")

Now that I reread your question I guess that’s not really what you were asking. Does it make any difference if you try an embedded spreadsheet instead of external?

[dleesuk]

Thank you, jjdickson,

The possible solution you offer would probably work if I had a small dataset.

However, the dataset I have is 55 rows x 281 columns = 15455 actual data points.

I'm using iLogic as my language of choice.

In you opinion, might it be easier to achieve with VBA?

[dleesuk]

Thank you MechMachineMan,

I have tried your solution within iLogic, which is my language of choice, to no avail.

(I did a tiny bit of re-jigging of the code to suit).

In your opinion, might VBA be a better option in achieving my goal??

[MechMachineMan · Accepted solution]

iLogic itself isn't really a language. It's coded in vb.net, but really
just acts as shortcut ways of acceasing certain features if the API based
on how many assumptions it makes. You can definitely combine aspects as
long as you always run the rules from within inventor.

The

xlApp = GoExcel.Application

is one such shortcut as usually you would have to write about 10 - 20 lines
involving error checking and things such as Marshall.GetActiveObject
("Excel.Application) and CreateObject ("Excel.Application") and maybe even
GetTypeFromProgId (...) and then add import lines to the top. So in the end
it can definitely simplify thing, but isn't always easiest.

Accessing excel directly (easiest via the GoExcel.Application call) is
always a good idea, IMO, because of the features that are available within
the excel API that have been specifically tailored to high level usage,
whereas the iLogic snippets in Inventor are not comprehensive and only
include the calls the Autodesk *thinks* their clients might need to use
frequently.

[dleesuk]

Thanks again MechMachineMan.

I'll take that on board and consider rewriting my routine in VBA.

It seems there's more help out there for that.

I'll accept this as a solution as my other thread has came up trumps with the spline creation part.

Your help is greatly appreciated

[MechMachineMan]

I managed to find a sample that will connect to inventor without using any of the iLogic snippets. This is written in vb.net and can be directly copy/pasted into a rule. Only thing you should neerd to change are the file links and sheet name.

AddReference "Microsoft.Office.Interop.Excel" 'To use excel

Imports Microsoft.Office.Interop.Excel        'To use excel
Imports System.Runtime.InteropServices        'To use marshal
Imports System.Activator                      'To use CreateInstance
Imports System.Type                           'To use GetTypeFromProgID

Sub Main()    
    Dim oXLWorkBook As String = "C:\Users\Owner\Desktop\TEST.xlsx"
    Dim oXLWorkSheet As String = "Sheet4"

        Dim oXLClass As New Cl_xlApp
        Dim oExcelApp As Microsoft.Office.Interop.Excel.Application  = oXLClass.ExcelInstance
        Dim xlWb As Workbook
        
        Try
            xlWb = oExcelApp.Workbooks.Open(oXLWorkBook)
        Catch
            MsgBox("excel file not found")
            oExcelApp = Nothing
            Exit Sub
        End Try
        
        Dim xlWs As WorkSheet = xlWb.Worksheets(oXLWorkSheet)
        oExcelApp.Visible = True
    
    Dim oLastRow As Integer = xlWs.Cells(xlWs.Rows.Count,1).End(XlDirection.xlUp).Row

    oCell = xlWs.Cells(1,1)
    oCell.Value = "This is first cell"
    oCell.Offset(2,1).Value = "This is offset cell"

    If oXLClass._started = True
        MsgBox("A new excel application was initiated this run!")
    Else
        MsgBox("A current instance of excel was connected to!")
    End If
                
    xlWs.Activate
    oExcelApp = Nothing
    
End Sub


Class Cl_xlApp

    Dim Public ExcelInstance As Microsoft.Office.Interop.Excel.Application
    Dim Public ActiveBook As WorkBook
    Dim Public _started As Boolean

    Public Sub New()

        Try
            ExcelInstance =  Marshal.GetActiveObject("Excel.Application")
            ActiveBook = ExcelInstance.ActiveWorkbook
        Catch ex As Exception
            Try     
                Dim xlAppType As Type = GetTypeFromProgID("Excel.Application")     
                ExcelInstance = CreateInstance(xlAppType)     
                ExcelInstance.Visible = True
                ActiveBook = ExcelInstance.ActiveWorkbook
                _started = True
            Catch ex2 As Exception     
                MsgBox(ex2.ToString())     
                MsgBox("Unable to get or start Excel")
                Exit Sub
            End Try
        End Try

    End Sub
End Class

[william]

Hello @MechMachineMan 

I have been reading through your code above, I am trying to do something similar in ilogic. 

I have created a excel sheet from a previous rule, that is open and unsaved. How would I connect to it again to write further data into the cells. Make it active again, so to speak. 

I have attached the file itself with the rules in it, so you can see what I am trying to do. 

I change the size parameters, the "BOM" rule fires, and then I want to run the "BOM to Excel" rule. I want to continue adding to the workbook with the BOM items from different sizes, therefore I need to activate the workbook again to write the data. 

I currently have this section of the code commented out since it doesnt work yet. 

[MechMachineMan]

I'm not sure, off the top of my head. Seeing as this is kind of general to
excel, googling it should give you some results. "Get active unsaved
document + excel + vba" or something of the sort. Guessing the answer will
either be in excel forums or on stackexchange!

Good luck!

[william]

fair enough, I did some more research and found the answer. It was simpler than what I thought.

To create the excel and write to it, the code was

	excelApp = CreateObject("Excel.Application")
	excelApp.Visible = True
	excelApp.DisplayAlerts = False
	excelWorkbook = excelApp.Workbooks.Add

	With excelApp
		.Range("A1").Select
		.ActiveCell.FormulaR1C1 = ""
	End With 

To activate the open, unsaved, excel workbook this created the code is

	excelApp = GetObject(,"Excel.Application")
	excelApp.Visible = True
	excelApp.DisplayAlerts = False
	excelWorkbook = excelApp.ActiveWorkBook
	
	With excelApp
		.Range("A1").Select
		.ActiveCell.FormulaR1C1 = ""
	End With 

Hopefully this is helpful to someone else later on. Please like if it is.