Finding Values in an array

[Anonymous]

Greetings.  The following code works great inside the Excel VBA envirnoment.

I would like to transfer this over to Inventor so that when I run I can go and grab the excel info and use it.

The end result in Inventor would be for the parameters to be equal to the cell values found.

of course a second option could be if I could figure out how to store the values then only pull those values from Excel into Inventor.

Question1 - Is this even possible?

Comment1 - if it is, please help!

Currently the only real issue I am having is to figure out the equivilant find and find next method in Inventor.  I assume there is a way to search through an arrray and keep only the values that match.

Thanks

Option Explicit

Sub FindEvap()

Dim SearchRange As Range
Dim ADTevap As Range
Dim FirstADTevapCell As String
Dim ADTQTY As Integer
Set SearchRange = Range("A1:A10")
Dim ADTdesign(7) As String

Set ADTevap = SearchRange.Find("adt")



    If ADTevap Is Nothing Then
        ADTQTY = 0
        
    
    Else
        
        FirstADTevapCell = ADTevap.Address
        
        Do
            ADTevap.Select
            
            Set ADTevap = SearchRange.FindNext(ADTevap)
            ADTQTY = ADTQTY + 1
            ADTdesign(ADTQTY - 1) = ADTevap.Value
        Loop While ADTevap.Address <> FirstADTevapCell
        
     End If
    'numberofevap = ADTQTY
    'ADTModel1 = ADTdesign(0)
    'ADTModel2 = ADTdesign(1)
    'ADTModel3 = ADTdesign(2)
    'ADTModel4 = ADTdesign(3)
    'ADTModel5 = ADTdesign(4)
    'ADTModel6 = ADTdesign(5)
    'ADTModel7 = ADTdesign(6)
    'ADTModel8 = ADTdesign(7)


End Sub

[Jef_E]

I don't fully understand what you are trying to accomplish here.

Help me help you 😉

• You have a Inventor model.
• You have a Excel spreadsheet.
• You want to drive the Inventor model, with values from the Excel spreadsheet.

Thats the larger picture?

---

Currently the only real issue I am having is to figure out the equivilant find and find next method in Inventor.  I assume there is a way to search through an arrray and keep only the values that match.

---

This is sentence is confusing to me? Do you want to find the equivilant what? Parameter value, Parameter name, ...

You could just set the parameter value  with the Excel cell value?

oDef.Parameters.item("parameter_name").value = Excel cell value?

Edit:

For questions like "how to search best in an array", it's easier to get response in a general website than here like stackoverflow. If you have a real Inventor related question, it's more likely you get an answer here.

I'm not sure what kind of question this is.

[Anonymous]

Ok, Sure let me try to improve my explanation.

I will attach the excel spreadsheet that I am using just to help clarify.

There are several columns in this excel spreadsheet that I need to be able to search from, for example, W6 throughW59.  As you can see there are gaps, and there could or could not be gaps or skipped rows in the file.

So, I need to be able to search all the column W and find only the relevant information.  In Excel, there is a FInd function ( I attempted to reference the Excel Object collection, but does not appear to be working the way I think).   The code shown will find the coils that start with ADT, then place them into a second array.  

After it has done that with one value it moves to the findNext function and cycles through the entire column until it reaches the starting coil.  Then stops.

So, by the end of this example, it would have collected 7 adt models.  I then would want to figure out how to delete repeats, but lets just do one thing at a time.

While it is grabbing the appropriate information, it is counting how many ADT coils that are found.  

Unfortunately, the way we design, there is not a standard format for using this worksheet, so I need a way to grab the relevant information, then plug it into the parameters.  I wish I could just make one cell value drive one parameter, that would make life super easy!

The examples of those parameters is as follows:

rownumbers = ADTQTY(counter value)

Evap1 = adtdesign(0)  (these are values in the array that are gathered from the column)

Evap2 = adtdesign(1)

Etc.

I would like these values to drive a drawing right now, but once I figure our how to make this work, I could then use a similar method to drive noth the model and drawing.

So, to answer part of your question, yes it is to drive our model and drawing generation.

So, Using Inventor, I figured I can dim an array(ormultivaluelist) of the all the column values.  Then I need to be able to count my ADT coils and LET coils, then I need to supply the quantity of the ADT, LET to two different parameters that drive two different part files.

Then I need to use the infomation from the individual ADT and LET to supply values into a table.  

I hope that makes sense.

I showed the code because it works and it does what I want.  However, when I move it into Inventor the functions, or methods  of Find and FindNext do not work.  And of course i have to take out the Set and the Sub and make slight translations.

So is there a similar function In Inventor that will accomplish these tasks?  

Or there might be a better way to perform this task and due to my lack of knowledge, I have not figured it out yet.

[wayne.brill]

Hi,

I am not sure why the Find and FindNext do not work in the Inventor function. If you provide the code you are using I can try to get it working with your xls file. 

Thanks,

Wayne

[Anonymous]

Wayne,

I think we need to step through this one question at a time.

The main issue I am having is once I get the information out of Excel and into Inventor as a MultiValue.List...

Do I then need to turn it into an array?

If not, is the information in a MultiValue.List able to be identified like you are able in an array?  So as Index(0), Index(1), index(2) etc.

And another confusion on my part, is the multivalue list different than an array?

The code I am attempting to recreate in Inventor is what is in the initial post.   The following code is where I am currently.  Not far I admit, but the previous questions has been stumping me for awhile. 

SyntaxEditor Code Snippet

MultiValue.List("DesignModel") = GoExcel.CellValues("C:\Users\jbaum\Desktop\Design Worksheet example.xls", "Conventional", "W6", "W50")
MyStringValues = New String(DesignModel)
Dim ADTevap As String

 ADTevap = Find("adt")

I think the Find does not work because it needs to reference the MultiValue.List, but not sure how to make that happen.

Thanks,

JBaum

[wayne.brill]

Hi JBaum,

The call to MultiValue.List is setting the available values for an Inventor Parameter named DesignModel. The default iLogic snippets for Excel are for interacting with Inventor Parameters and Excel.  I am not sure if you are trying to use iLogic snippets for general Excel access. I changed the cells that were being retrieved so there were less available values for the DesignModel parameter. (W6,W17 instead of W6,W50)  Screenshot below to help explain.

The Multivalue.List() does returns an ArrayList. Also maybe MultiValue.FindValue() will help. Here is the iLogic rule I tested with.

'MultiValue.List("DesignModel") = GoExcel.CellValues("C:\Users\jbaum\Desktop\Design Worksheet example.xls", "Conventional", "W6", "W50")
MultiValue.List("DesignModel") = GoExcel.CellValues("D:\Inventor Cases\Design Worksheet example.xls", "Conventional", "W6", "W17")

values = MultiValue.List("DesignModel")
Dim obj As Object
For Each obj In values
	MessageBox.Show("avalilable value for DesginModel paramater " & obj.ToString(), "Title")
Next

'or get one value
foundVal = MultiValue.FindValue(MultiValue.List("DesignModel"), "=", "ADT-090")
MessageBox.Show("foundVal = " & foundVal, "Title")

Thanks,

Wayne

[Anonymous]

Wayne,

"I am not sure if you are trying to use iLogic snippets for general Excel access"  Is there another way to access Excel?

You are correct I am attempting to use the snippets because I am new to this idea of customization and programming.  

Now that the data is available I need to see if I can sort the data the way I need it so that I can place the data into the respective parameters.

Attached is another excel file and txt file with the macro on it.  

At the end of the ADT sort you will see a list of parameters that need the corresponding values.  They are commented out because they do not talk to Inventor at this moment.  As you step through the code you can see the different arrays being generated with the accurate number of evaps, and no repeats.

Then you will see a sort for LET, WK, and WKE.  They all will have their own similar parameters.

My thoughts, perform the sort in Excel first, place the found components in a specific cell on the sheet, then pull those cells specifically using the snippets, and finally place the data into the appropriate parameter. 

However, the more I can do in Inventor the better only because of how deeply nested these Excel files will be on our Network.  Slows down the software significantly.

So in the following I want to only search for the ADT part of the string.  However, the foundVal is blank. Why?

SyntaxEditor Code Snippet

foundVal = MultiValue.FindValue(MultiValue.List("DesignModel"), "=", Left("ADT-090",3))
MessageBox.Show("foundVal = " & foundVal, "Title")

Thank you for your help.  

[Wind_Talker]

Hi JBaum,

To help you search a value in an array you should take a look at the following post by me. My end goal is although different to you but I am creating arrays, adding values to them and then doing a search in them for specific values based on my required criteria all in ilogic. You may want to try something similar for your search problem.

http://forums.autodesk.com/t5/inventor-customization/comp-replace-by-next-or-previous-file-in-a-netw...

Regards

[Wind_Talker]

Hi JBaum,

Reading through your post I saw that you are also looking for ilogic code to delete repititve values in an array. I think something like below should work for you. I did it with integers but offcourse you can do the same with strings. I hope that helps.

'Create an array of integer. Working with List rather than array is always preferred.
Dim Array1 as New List(Of Integer) 

'Add values to the array. Dim 2 counting integers to get repititive value.
Dim i As Integer
Dim j As Integer 

For j = 1 To 5
    For i = 1 To 5
        Array1.add(i)
    Next
Next

'Display the values in the list in a msg box. Just a check to see if the array has values as expected.
'You can delete the msgbox code below if not needed.
Dim tText As String
Dim oItem As Integer
For Each oItem In Array1
    tText = oItem & vbLf & tText
Next
MsgBox(tText)

'Dim another array to add non repititive values.
Dim Array2 as New List(Of Integer)
'Dim item counter for 1st array list.
Dim Item1 as Integer
'Create a loop to cycle through the items of 1st array and add them to 2nd array list if they are already not there.
For Each item1 in Array1
    If Not Array2.Contains(item1) Then
        Array2.Add(item1)
    End If
Next

'Display the items in new array list which are non repititive.
Dim bText As String
Dim bItem As Integer
For Each bItem In Array2
    bText = bItem & vbLf & bText
Next
MsgBox(bText)

[Jef_E]

@Wind_Talker Is this really required?

Such a long code for such an simple task. I took the first few lines where you create the List(of T) and edited below. Edits are marked in red.

This method requires only 1 list.

Create an array of integer. Working with List rather than array is always preferred.
Dim Array1 as New List(Of Integer) 

'Add values to the array. Dim 2 counting integers to get repititive value.
Dim i As Integer
Dim j As Integer 

For j = 1 To 5
    For i = 1 To 5
        Array1.add(i)
    Next
Next

' To remove duplicates
Array1= Array1.Distinct().ToList

[Anonymous]

Thanks Wind_Talker,
This gives me a lot to digest. Looks promising.

[Wind_Talker]

Hi Jeff_E,

I know there will always be a better way to do a task. This is what I came up with on the fly.
Anyway I tried your code but it does not work. Throws an error saying
"'Distinct' is not a member of 'System.Collections.Generic.List(Of Integer)'.
"
Are we missing something here or do something need to be added to the top.
Regards

[wayne.brill]

Hi JBaum,

Have you considered using your Excel VBA code from Inventor VBA? It should work as is after you reference the Excel library and get the Sheet. One advantage VBA has over iLogic is that is is easier to debug and see values of variables in the watch window.  I usually protoype in VBA and then move the code to iLogic or VB.NET after it is working.  Although in some cases using VBA is fine to put into production.

In the Inventor VBA Editor go to Tools>References and select the Excel object library:

You would just need to use GetObject or CreateObject. I tested this using GetObject. To use GetObject Excel needs to be already opened and to use the ActiveSheet have your xls file opened. CreateObject could be used to fire up a new session of Excel. Here is the top of your VBA code I tested with:

Sub excelFindEvap()

    Dim oExcel As Excel.Application
    Set oExcel = GetObject(, "Excel.Application")
    
    Dim oWkBk As Excel.Workbook
    Set oWkBk = oExcel.ActiveWorkbook
    
    Dim oExcelSheet As Excel.WorkSheet
    Set oExcelSheet = oWkBk.ActiveSheet
    
    Dim SearchRange As Range
    Dim ADTevap As Range
    Dim FirstADTevapCell As String
    Dim ADTQTY As Integer
    
    'Set SearchRange = Range("A1:A10")
    Set SearchRange = oExcelSheet.Range("A1:A10")
    
    Dim ADTdesign(7) As String

'...

You could also use the Excel COM API in iLogic. Here is an iLogic rule with your VBA code.

AddReference "Microsoft.Office.Interop.Excel"
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Core

a1Value = GoExcel.CellValue("D:\Inventor Cases\findevaps.xlsx", "Sheet1", "A1")
excelApp = GoExcel.Application

'Start from VBA
Dim SearchRange As Range
Dim ADTevap As Range
Dim FirstADTevapCell As String
Dim ADTQTY As Integer

'WB added
Dim excelSheet As Object
excelSheet = excelApp.ActiveSheet 
'SearchRange = Range("A1:A10")
SearchRange = excelApp.ActiveSheet.Range("A1:A10")

Dim ADTdesign(7) As String

ADTevap = SearchRange.Find("adt")
	MessageBox.Show("ADTevap.Value = " & ADTevap.Value.ToString(), "Title")
    If ADTevap Is Nothing Then
        ADTQTY = 0

    Else
        
        FirstADTevapCell = ADTevap.Address
        
        Do
            ADTevap.Select
            
            ADTevap = SearchRange.FindNext(ADTevap)
            ADTQTY = ADTQTY + 1
            ADTdesign(ADTQTY - 1) = ADTevap.Value
        Loop While ADTevap.Address <> FirstADTevapCell
     End If

'	'numberofevap = ADTQTY
'    'ADTModel1 = ADTdesign(0)
'    'ADTModel2 = ADTdesign(1)
'    'ADTModel3 = ADTdesign(2)
'    'ADTModel4 = ADTdesign(3)
'    'ADTModel5 = ADTdesign(4)
'    'ADTModel6 = ADTdesign(5)
'    'ADTModel7 = ADTdesign(6)
'    'ADTModel8 = ADTdesign(7)
'

Dim LETevap As Range
Dim FirstLETevapCell As String
Dim LETQTY As Integer
Dim LETdesign(7) As String

LETevap = SearchRange.Find("let")
    MessageBox.Show("LETevap.Value = " & LETevap.Value.ToString(), "Title")
    If LETevap Is Nothing Then
        LETQTY = 0
   
    Else
        
        FirstLETevapCell = LETevap.Address
        
        Do
            LETevap.Select
            
            LETevap = SearchRange.FindNext(LETevap)
            LETQTY = LETQTY + 1
            LETdesign(LETQTY - 1) = LETevap.Value
        
        Loop While LETevap.Address <> FirstLETevapCell
        
      End If
 
 
Dim WKEevap As Range
Dim FirstWKEevapCell As String
Dim WKEQTY As Integer
Dim WKEdesign(7) As String

WKEevap = SearchRange.Find("wke")
    MessageBox.Show("WKEevap.Value = " & WKEevap.Value.ToString(), "Title")
    If WKEevap Is Nothing Then
        WKEQTY = 0

    Else
        
        FirstWKEevapCell = WKEevap.Address
       
        Do
            WKEevap.Select

            WKEevap = SearchRange.FindNext(WKEevap)
            WKEQTY = WKEQTY + 1

            WKEdesign(WKEQTY - 1) = WKEevap.Value
    
        Loop While WKEevap.Address <> FirstWKEevapCell
		
     End If
     
    
Dim WKevap As Range
Dim FirstWKevapCell As String
Dim WKQTY As Integer
Dim WKdesign(7) As String

'WKevap = SearchRange.Find("wk",LookAt:=xlPart) 'WB commented  
WKevap = SearchRange.Find("wk",,,XlLookAt.xlPart) 'WB added  
    MessageBox.Show("WKevap.Value = " & WKevap.Value.ToString(), "Title")
    If WKevap Is Nothing Then
        WKQTY = 0
   
    Else
        
        FirstWKevapCell = WKevap.Address
        
        Do
            WKevap.Select
            
            WKevap = SearchRange.FindNext(WKevap)
            WKQTY = WKQTY + 1
            WKdesign(WKQTY - 1) = WKevap.Value
			
			'MessageBox.Show(WKEevap.Address.ToString(), "Title")
			'MessageBox.Show(FirstWKEevapCell.ToString(), "Title")
        
        Loop While WKevap.Address <> FirstWKevapCell
        
     End If
'END from VBA

Thanks,

Wayne

[Jef_E]

I honestly don't know this page says it's a member of List(T)..

https://msdn.microsoft.com/en-us/library/d9hw1as6%28v=vs.90%29.aspx

I used it once or twice in a project, it works there.. I didn't import anything special there, here is my sample code from another project ( vb.net ).

    Private Function CountCylinders(ByVal oDef As AssemblyComponentDefinition) As Integer

        ' Lets list all cylinder segments found in the assembly
        ' we will need the document name to do this.
        ' the initial value is nothing, if, after counting
        ' this is still the case, there are no cylinders.
        Dim oList As New List(Of String)

        ' Loop through all of the occurences found in the assembly
        For Each oOccurrence As ComponentOccurrence In oDef.Occurrences

            ' Get the occurence document
            Dim oOccurenceDocument As Document
            oOccurenceDocument = oOccurrence.Definition.Document

            ' Check if the occurence document name contains cylinder
            If oOccurenceDocument.DisplayName.Contains("Cylinder") Then

                ' Get the cylinder filename
                Dim oCylinder As String
                oCylinder = oOccurenceDocument.FullFileName

                ' Get the filename w/o extension
                oCylinder = IO.Path.GetFileNameWithoutExtension(oCylinder)

                ' Remove the segment mark.
                oCylinder = oCylinder.Remove(oCylinder.LastIndexOf("_"), oCylinder.Length - oCylinder.LastIndexOf("_"))

                oList.Add(oCylinder)
                Debug.Print("add : " & oCylinder)
            End If
        Next

        ' Delete the duplicates in the list
        oList = oList.Distinct().ToList

        ' TODO: can be removed.
        Debug.Print("Total number of cylinders = " & oList.Count)

        ' Return the number of cylinders
        CountCylinders = oList.Count

    End Function

[Wind_Talker]

Hi Jeff,

It has to be declared as IEnumerable of Tsource not just List of Tsource. See following link for details.

https://msdn.microsoft.com/en-us/library/bb348436(v=vs.90).aspx

Regards

[Jef_E]

---

@Wind_Talker wrote:
Hi Jeff,

It has to be declared as IEnumerable of Tsource not just List of Tsource. See following link for details.

https://msdn.microsoft.com/en-us/library/bb348436(v=vs.90).aspx

Regards

---

Well, I did not do that 😕 And works? Maybe something with framework version?

[forbillian]

Hi Jef,

Thankyou for your post - was very helpful.

How can I adapt your code so that I can combine some  string text as well as the integers to show in the array list like this e.g.? I have tried & can only get the text on one line

"STRING - "  5

"STRING - "  4

"STRING - "  3

"STRING - "  2

"STRING - "  1

[forbillian]

I worked it out.

'Create an array Of Integer. Working With List rather than array Is always preferred.

Dim Array1 As New List(Of Integer) 
'Dim Array2 As String'

'I HAVE ADDED THESE  2  LINES'......................
Dim Sx1 As String
Sx1 = "sPromptStrings("

Dim Sx2 As String
Sx2 = ")"
'......................



Dim j As Integer 

For j = 0 To 1
Array1.Add(j)
Next

'Display the values in the list in a msg box. Just a check to see if the array has values as expected.

Dim tText As String
Dim oItem As Integer
For Each oItem In Array1
tText = Sx1 & oItem & Sx2 & vbLf & tText



Next
MsgBox(tText)

What I am trying to do is use this array to loop through the following strings without having to list each one. This is the section of code I am trying to adapt using the array list above.   

Dim sPromptStrings(0 To 1) As String
sPromptStrings(j) = ""

 The full code is to pr-empt no of prompt values in a border before adding it to a sheet. I have included the full code below.

Sub Main InsertCustomBorderOnSheet()
    ' Set a reference to the drawing document.
    ' This assumes a drawing document is active.
    Dim oDrawDoc As DrawingDocument
    oDrawDoc = ThisApplication.ActiveDocument
    
    ' Obtain a reference to the desired border definition.
    Dim oBorderDef As BorderDefinition
     oBorderDef = oDrawDoc.BorderDefinitions.Item("TRIAL")
     
     'gather the current sheet name
    Dim oCurrentSheet
    oCurrentSheet = oDrawDoc.ActiveSheet.Name
    
    Dim oSheet As Sheet
    oSheet = oDrawDoc.ActiveSheet
    
    'step through each sheet
i = 0
For Each oSheet In oDrawDoc.Sheets
  i = i+1
  'activate the sheet
  ThisApplication.ActiveDocument.Sheets.Item(i).Activate
    
    ' Check to see if the sheet already has a border and delete it if it does.
    If Not oSheet.Border Is Nothing Then
        oSheet.Border.Delete
    End If
    
    
' Check to see if the sheet already has a border and delete it if it does.'If Not oSheet.Border Is Nothing Then'oSheet.Border.Delete'End If

' This border definition contains one prompted string input. An array' This line must show the exact number of Prompted Strings in the Border


 'INSTEAD OF USING THIS & LISTING EACH sPromptString, 

'ORIGINAL ***************
Dim sPromptStrings(0 To 1) As String
sPromptStrings(0) = ""
sPromptStrings(1) = ""
'*********************

'I WANT TO REFER TO THE ARRAY LIST IN SOMEWAY LIKE THIS

'NEW ***************
Dim sPromptStrings(0 To j) As String 
sPromptStrings(j) = "" 
***************

' Add an instance of the border definition to the sheet.
Dim oBorder As Border
'LINE 28 IS WHERE i AM HAVING TROUBLE
oBorder = oSheet.AddBorder(oBorderDef, sPromptStrings)  'THIS LINE LINE WORKS FOR INSERTING BORDER
MsgBox("WORKS UPTO HERE BUT I WANT TO CHANGE THE sPromptStrings WITHOUT deleting & adding the Border again")

Next
ThisApplication.ActiveDocument.Sheets.Item(oCurrentSheet).Activate
End Sub

Any assistance would be great