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
Solved! Go to Solution.
Solved by MechMachineMan. Go to Solution.
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
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")
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?
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?
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??
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
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
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.
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.
Can't find what you're looking for? Ask the community or share your knowledge.