Create a group from Excel list

[wacoal]

Hi,

I have a CATProduct open (500 parts), I have an Excel file that give me the name of 35 specific parts of this CATProduct. I would like a macro that could create a group with these 35 parts.

I have digged into the search options, I am thinking that a solution could be with personalized query, but I cannot find anything relevant.

I am using Catia V5, R20.

Thanks for your help,

Marc

 

[itsmyjob]

500 different parts in the assembly, just one instance ? this should not be too difficult... what is you code so far?

Eric N.
indocti discant et ament meminisse periti ​

 

[AlexLozoya]

just for start..

Sub Selecttt()

Dim sPath As String
CATIA.DisplayFileAlerts = False
Path = "C:\Products.xlsx"

Set xlApp = CreateObject("Excel.Application")
Set MyXL = GetObject(, "Excel.Application")
Set MyXL = GetObject(Path)
MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True


For i = 1 To 3
sPath = MyXL.Application.Sheets(1).Cells(i, 1).Value

 Set ActiveProductDocument = CATIA.ActiveDocument
 Set Product1 = ActiveProductDocument.Product
 Set Product3Products = Product1.Products
 Set Product2Dot1 = Product3Products.Item(sPath)
 
 Set ActiveProductDocument = CATIA.ActiveDocument

Set selection1 = ActiveProductDocument.Selection


selection1.Add Product2Dot1

 Next i
 
 Dim myGroups

Set myGroups = Product1.GetTechnologicalObject("Groups")

Dim myGroup As Group

Set myGroup = myGroups.AddFromSel
CATIA.DisplayFileAlerts = True
End Sub

 

[wacoal]

Thanks for your answers!

It looks like it's a bit more complicated. Indeed I use Catia on a UNIX station, so I would probably not be able to use this Excel sheet... Do you think I can instead use a txt file ?

I tried anyway to launch the macro, it stopped on the Line 29, column 6: "Next i", with the following error: "expected end of statement"

 

[wacoal]

Ok, I worked on this and thanks to your code and this website

http://v5vb.wordpress.com/2010/02/08/file-dialogs/#more-224

I found how to read a txt file. I tried mixing your code with the other one, but I still have an issue:

Sub CATMain()

Dim strFilePath As String
Dim objFile As File
Dim objTextStream As TextStream
Dim strLine As String

'Display file open dialog
strFilePath = CATIA.FileSelectionBox("Select Text File", "*.txt", 0)

'If user clicked cancel (empty string is returned), then exit the program
If strFilePath = "" Then Exit Sub

'Open the file, add the 10 first parts to the selection1
Set objFile = CATIA.FileSystem.GetFile(strFilePath)
Set objTextStream = objFile.OpenAsTextStream("ForReading")

For i = 1 to 10

strLine = objTextStream.ReadLine

Set ActiveProductDocument = CATIA.ActiveDocument
Set Product1 = ActiveProductDocument.Product
Set Product3Products = Product1.Products
Set Product2Dot1 = Product3Products.Item(strLine) '<<< Here is where it stop "The method Item failed" 

Set ActiveProductDocument = CATIA.ActiveDocument

Set selection1 = ActiveProductDocument.Selection

selection1.Add Product2Dot1

Next

objTextStream.Close

End Sub

Do you have any idea how to solve this ?

 

[AlexLozoya]

Try with the instance name of the parts or products in the txt file...

 

[wacoal]

I tried, still the same error.
I also tried with the PN, I tried with the name as displayed in graph, same thing...

 

[AlexLozoya]

I don't know what the problem is, but just in case.. look if you text file contains less that 10 lines.. or in other words, be sure that you are passing valid strings.. not empty strings.

one way to know.. change the number of iterations to 9.. and check if the error appears.

or maybe with message boxes..

 

[wacoal]

It looks like it's related to the name I am using. I tried a short sequence of the code above, and I can't make this line works: Set Product2Dot1 = Product3Products.Item("XXX")

Sub CATMain()

Set ActiveProductDocument = CATIA.ActiveDocument
Set Product1 = ActiveProductDocument.Product
Set Product3Products = Product1.Products
Set Product2Dot1 = Product3Products.Item("XXX")

Set ActiveProductDocument = CATIA.ActiveDocument
Set selection1 = ActiveProductDocument.Selection
selection1.Add Product2Dot1

End Sub

Any name I am using give me the same error "The method Item failed". I cannot figure out how to use this ".Item".

 

[itsmyjob]

try the online doc:

o Func Item( CATVariant iIndex) As Product

Returns a product from its index in the Products collection.

Parameters:

iIndex
The index of the product to retrieve in the collection of products. This index can either be the rank of the product in the collection or the name you assign to the product. As a numerics, this index is the rank of the product in the collection. The index of the first product in the collection is 1, and the index of the last product is Count. As a string, it is the name you assigned to the product using the

AnyObject.Name property
Returns:
The retrieved product
Example:
The following example returns in ThisProduct the third product, and in ThatProduct the product named Wheel in the CarParts product collection.

Dim ThisProduct As Product
Set ThisProduct = CarParts.Item(3)
Dim ThatProduct As Product
Set ThatProduct = CarParts.Item("Wheel")

Eric N.
indocti discant et ament meminisse periti ​

 

[ferdo]

Hi,

You may want to check also this, there is an example code there which is doing what you want...when you are using "name" is in fact Instance Name.

And agree with AlexLozoya, in CATScript use MsgBox to check your code.



Regards
Fernando

https://picasaweb.google.com/102257836106335725208

https://picasaweb.google.com/103462806772634246699/

 

[wacoal]

Thanks Fernando, I'm getting closer and closer

One last issue: I don't know how to add a selection to another selection. In the code you provided, the selection selPN is changing at each loop of the "For Each strLine in arrLines". But how can I store this information and add it to another selection ?

I thought of using a collection (like cnc07 in this Post) but the declaration "As New Collection" give me a error "Expected end of statement".

Thanks,

Marc

 

[wacoal]

Here I am again!

I finally managed to do what I wanted, it's working on my windows PC. But it's giving me the an error on my UNIX station, without any description of the error:

Sub CATMain()


Dim productDocument1 As Document
Set productDocument1 = CATIA.ActiveDocument

Dim selPN, selGL As Selection
Set selPN = productDocument1.Selection
Set selGL = productDocument1.Selection

Dim Producttemp As Product

Dim objFSO, strTextFile, strData, strLine, arrLines
CONST ForReading = 1

'name of the text file
strTextFile = "H:\test.txt"

'Create a File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject") '<<< HERE IS WHERE THE MACRO STOPS AND GIVE ME AN ERROR

'Open the text file - strData now contains the whole file
strData = objFSO.OpenTextFile(strTextFile,ForReading).ReadAll

'Split the text file into lines
arrLines = Split(strData,vbCrLf)

'~ 'Step through the lines
For Each strLine in arrLines
'~ ''''''''''''''''''''''''''''''''''''''''
selPN.Search "Name=" & "*" & strLine & "*" & " ,all"
SelPN.VisProperties.SetRealColor 0, 0, 255, 1

Next

SelGL.Search "Color=Blue,all"

'Cleanup objFSO
Set objFSO = Nothing

End Sub

Does anybody has an idea ?
Thanks

 

[ferdo]

Hi,

Check your file path (line strTextFile = "H:\test.txt") , in UNIX is different then in WIndows. This could be one reason why is not working.

Regards
Fernando

https://picasaweb.google.com/102257836106335725208

https://picasaweb.google.com/103462806772634246699/

 

[wacoal]

You're right, the file path is different, but I changed it while copying the macro on my Unix station. (/Myserver/myfolder/test.txt)

Actually the code is stopping at the next line:

'Create a File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")

 

[ferdo]

Unfortunately I don't have now access to a UNIX workstation (not even remote) and cannot check the code but you should use something like bellow (you need to forget Windows methods and put some MsgBox to check your code)

Instead of
'Create a File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject") '<<< HERE IS WHERE THE MACRO STOPS AND GIVE ME AN ERROR
....................
....................

Use

set fs = CATIA.FileSystem
dim strTextFile as File
set strTextFile = fs.GetFile("/Myserver/myfolder/test.txt")

MsgBox strTextFile

dim ts as TextStream
set ts = strTextFile.OpenAsTextStream("ForReading")

do while ts.atendofstream=false
''''''.code to read the lines
loop






Regards
Fernando

https://picasaweb.google.com/102257836106335725208

https://picasaweb.google.com/103462806772634246699/

 

[wacoal]

Thank you for your answer.
I am going to work on this, and come back here to get you a feedback.

 

[wacoal]

Yeah ! It's working, here is the code:

Sub CATMain()

On error resume next

Dim productDocument1 As Document
Set productDocument1 = CATIA.ActiveDocument

Dim selPN, selGL As Selection
Set selPN = productDocument1.Selection
Set selGL = productDocument1.Selection

Dim Producttemp As Product

Set fs = CATIA.FileSystem
Dim strTextFile as File
set strTextFile = fs.GetFile(CATIA.FileSelectionBox("Select Text File","*.txt",0))

Dim ts as TextStream
set ts = strTextFile.OpenAsTextStream("ForReading")

Do

Dim Lire as String
Lire = ts.ReadLine
MsgBox Lire
selPN.Search "Name=" & "*" & Lire & "*" & " ,all"
SelPN.VisProperties.SetRealColor 0, 0, 255, 1

Loop while ts.atendofstream=false

SelGL.Search "Color=Blue,all"
SelGL.VisProperties.SetRealColor 0, 0, 255, 0

'Cleanup objFSO
Set objFSO = Nothing

End Sub

Thanks for your help !