topic Re: iLogic code to read a specific excel sheet in a workbook in Inventor Forum

iLogic code to read a specific excel sheet in a workbook

Anonymous — Tue, 06 Dec 2016 20:23:31 GMT

Hello,

Currently I am having trouble trying to find a way for inventor to read the coordinates (x-y) from a specific excel sheet. When I use the sketch --> insert--> points. It only pulls it from the 1st sheet of the excel.. I need it to import a different sheet. So I was wondering there is any code for VBA or ilogic to read the points from excel in different sheets in excel

Re: iLogic code to read a specific excel sheet in a workbook

mcgyvr — Tue, 06 Dec 2016 20:29:22 GMT

The most basic is this..

If you provide more details better information can be given..

GoExcel.CellValue("filename.xls", "Sheet1", "A2")

and a link to the excel data functions reference for ilogic..

https://knowledge.autodesk.com/support/inventor-products/learn-explore/caas/CloudHelp/cloudhelp/2015/ENU/Inventor-Help/files/GUID-00F9D915-B53A-46BF-AAAC-3535F9FD9970-htm.html

Re: iLogic code to read a specific excel sheet in a workbook

Anonymous — Tue, 06 Dec 2016 20:32:22 GMT

Yes I tried doing that but since inside the sketch there isnt any parameters called out.. So Inventor doesn't know what to look for in Excel to import in those points. I just need a pathway or a code to get inventor to pull in excel and picking which sheet inside of excel to use those points and the points varies depending on the requirements when generating the x and y

Re: iLogic code to read a specific excel sheet in a workbook

mcgyvr — Tue, 06 Dec 2016 20:38:11 GMT

Ok.. I like more details.. 🙂

Now we are here..

Parameter("d2") = GoExcel.CellValue("filename.xlsx", "SheetName", "Cell")
iLogicVb.UpdateWhenDone = True

Re: iLogic code to read a specific excel sheet in a workbook

Curtis_W — Tue, 06 Dec 2016 20:54:36 GMT

@Anonymous wrote:

...I was wondering there is any code for VBA or ilogic to read the points from excel in different sheets in excel

Hi lle5956,

There is a *.zip file at this link that has a VBA project that creates and updates a 3D sketch from co-ordinates in an XLS file:

https://knowledge.autodesk.com/support/inventor-products/troubleshooting/caas/sfdcarticles/sfdcarticles/Import-points-associatively-from-Excel-into-Inventor-s.html

Also just as a tip, you can search and ask programming questions of this type on the Inventor Customization forum too:
http://forums.autodesk.com/t5/Autodesk-Inventor-Customization/bd-p/120

I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com

Re: iLogic code to read a specific excel sheet in a workbook

Anonymous — Tue, 06 Dec 2016 21:34:16 GMT

The current code i have is this

Sub Main()

' Get the CommandManager object.

Dim oCommandMgr As CommandManager

oCommandMgr = ThisApplication.CommandManager

' Get control definition for the line command.

Dim oControlDef As ControlDefinition

oControlDef = oCommandMgr.ControlDefinitions.Item( _

"SCxImportPointsCmd")

' Execute the command.

Call oControlDef.Execute

End Sub

which basically is just the command button to browse for the excel file and once it does. it imports whatever the x-y coordinate is but i need it to where it reads from that excel file just a different sheet/tab..

Re: iLogic code to read a specific excel sheet in a workbook

MechMachineMan — Tue, 06 Dec 2016 22:22:22 GMT

Hmm. Control Definitions.

http://modthemachine.typepad.com/my_weblog/2009/03/running-commands-using-the-api.html

As this blog says, that function might be limited and you might not actually be able to specify that for the command. It's worth looking into though!

You may have to think of some other way of organizing your excel files so that you can give in to what inventor wants.

Re: iLogic code to read a specific excel sheet in a workbook

Curtis_W — Tue, 06 Dec 2016 23:15:57 GMT

@Anonymous wrote:

The current code i have is this ....

...which basically is just the command button to browse for the excel file...

Hi lle5956,

Yeah, I don't see that being of much use for your goal here.

Here's a real quick iLogic example that reads in the specified Excel sheet, and works with the specified rows (oStartRow and oRowCount ).

This rule expects a 2D sketch to be active for edits.

I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com

owbook = "C:\TEMP\Book1.xlsx"
owSheet = "Sheet2"
GoExcel.Open(owbook,owSheet)

'start row to use
Dim oStartRow As Integer
oStartRow = 1

'number of rows to use
Dim oRowCount As Long
oRowCount = 10 

Dim oPartDoc As PartDocument
oPartDoc = ThisApplication.ActiveDocument

' Set a reference to the active sketch.
Dim oSketch As PlanarSketch

'catch error when sketch 
'is not in edit mode
Try
	oSketch = ThisApplication.ActiveEditObject
Catch
	MessageBox.Show("Can not continue." & vbLf & _
	"The active Object Is Not a Sketch.", "iLogic")
	Return
End Try

' Set a reference to the transient geometry collection.
Dim oTransGeom As TransientGeometry
oTransGeom = ThisApplication.TransientGeometry

Dim i As Integer
For i = oStartRow To oRowCount
	Dim strx As String, stry As String
	strx = GoExcel.CellValue(owbook,owSheet, "A" & i)
	stry = GoExcel.CellValue(owbook,owSheet, "B" & i)    
	
	oSketch.SketchPoints.Add(oTransGeom.CreatePoint2d(strx, stry))	

Next

Example XLS:

Example result: