Macro export list of CATparts to excel
Macro export list of CATparts to excel
(OP)
Dear all,
I would like to create a macro that will make the following steps
From a list of CATParts
1. Open a CATpart saved in a specific folder ( ex c:\Temp\input)
2. Retrieve info from catpart (part name)and export to excel
3. Go to step 1
3. Save excel sheet in a especific folder (ex c:\Temp\output)
The code i have just created finally is able to the excel sheet but is not able to export the properties of the catparts.
Any ideas how to fix this issue?
Many thanks and regards
Sub CATMain()
Dim documents1 As Documents
Set documents1 = CATIA.Documents
MsgBox “The number of documents is ” & documents1.Count
Dim doc1 As String
Set doc1 = documents1.Item(1)
‘MsgBox doc1.Name
‘MsgBox doc1.FullName
‘For Each doc1 in documents1
‘MsgBox doc1.Name
‘Next
Dim partDoc1 as PartDocument
Set partDoc1 = doc1
MsgBox partDoc1.Name
‘to excel
Dim Excel As Object
Dim workbooks As workbooks
Dim workbook As workbook
Dim worksheet As Excel.worksheet
On Error Resume Next
Set Excel = GetObject(, “EXCEL.Application”)
If Err.Number <> 0 Then
Err.Clear
Set Excel = CreateObject(“EXCEL.Application”)
Else
Err.Clear
MsgBox “Please note you have to close Excel”, vbCritical
Exit Sub
End If
Excel.Visible = True
‘load a sheet
Set workbooks = Excel.Application.workbooks
Set myworkbook = Excel.workbooks.Add
Set myworksheet = Excel.ActiveWorkbook.Add
Set myworksheet = Excel.Sheets.Add
‘download the properties
‘row one
Excel.Cells(1,1)=”Part Number”
‘row 2
Excel.Cells(2,1)= doc1
End Sub





RE: Macro export list of CATparts to excel
What kind of info from the part you want? I believe all steps can be found in different examples here in forum, just use search function.
Regards
Fernando
https://picasaweb.google.com/102257836106335725208
https://picasaweb.google.com/103462806772634246699...
RE: Macro export list of CATparts to excel
many thanks for your reply.
I want to get the Filename from a batch of CATPart and export all in excel sheet.
ex :
Filename Mass Volume PATH
150108_SCREW.CATPart C:\My_works\
Get also info like mass and volume should be nice , but it is noy necessary.
How is it possible to get these variables?
I am totally new programming in CATIA and I just want to ask some help because I don´t know
how to use this search funktion and the array
Thanks again and regards
RE: Macro export list of CATparts to excel
''''''''''''''''''''''''''''
CATIA.DisplayFileAlerts = False
set ShellApp=CreateObject("Shell.Application") 'generate a new "Shell Application" object
set FolBrowser=ShellApp.BrowseForFolder(0,"Choose the folder where your files are stored",16,17)
folderinput = "C:\Temp"
If not FolBrowser is Nothing then
folderinput=FolBrowser.Self.Path 'save the path & folder in the variable "folderinput"
End If
badfolder = instr(folderinput, "{")
If badfolder > 0 then
folderinput = "C:\Temp"
End if
Dim fs, fld
Set fs = CreateObject("Scripting.FileSystemObject")
Dim fso 'As FileSystem
Set fso = CATIA.FileSystem
sFolderPath = folderinput
Set folder = fso.GetFolder(sFolderPath)
Set Files = folder.Files
'~ Filter and open from choosen folder
For i=1 to Files.Count
Set file = Files.Item(i)
if file.Type = "CATIA Part" then
'' code to get info, see that post
Next
Regards
Fernando
https://picasaweb.google.com/102257836106335725208
https://picasaweb.google.com/103462806772634246699...
RE: Macro export list of CATparts to excel
thanks for your reply.
The link you forwarded me shows info retreieved from Part Bodies
I don´t know the variables I should use to retrieve the CATPart File.
Could you please help me?
Thanks again
RE: Macro export list of CATparts to excel
This is just an example, you need to continue and modify the script....
CODE --> CATScript
Sub CATMain() Set Excel = CreateObject("Excel.Application") Excel.visible = true Set workbooks = Excel.Application.workbooks Set myworkbook = Excel.workbooks.Add Set objsheet1 = Excel.Sheets.Add objsheet1.Cells(1, 1) = "Part Name" objsheet1.Cells(1, 2) = "CATIA Part Body mass" objsheet1.Cells(1, 3) = "CATIA Part Body mass SMD" objsheet1.Cells(1, 4) = "CATIA mass" objsheet1.Cells(1, 5) = "Density" objsheet1.Cells(1, 6) = "Real mass" objsheet1.Cells(1, 7) = "CATPart path" Set documents1 = CATIA.Documents CATIA.DisplayFileAlerts = False set ShellApp=CreateObject("Shell.Application") 'generate a new "Shell Application" object set FolBrowser=ShellApp.BrowseForFolder(0,"Choose the folder where your files are stored",16,17) folderinput = "C:\Temp" If not FolBrowser is Nothing then folderinput=FolBrowser.Self.Path 'save the path & folder in the variable "folderinput" End If badfolder = instr(folderinput, "{") If badfolder > 0 then folderinput = "C:\Temp" End if Dim fs, fld Set fs = CreateObject("Scripting.FileSystemObject") Dim fso 'As FileSystem Set fso = CATIA.FileSystem sFolderPath = folderinput Set folder = fso.GetFolder(sFolderPath) Set Files = folder.Files For i=1 to Files.Count Set file = Files.Item(i) if file.Type = "CATIA Part" then Set partDocument1 = documents1.Open(File.Path) '''''''''''''''''''''''''''''''''''''''''' Dim selection7 'As Selection Set selection7 = partDocument1.Selection selection7.Search "Name=PartBody,all" ''''''''''''''''''''''''''''''''''''measure Set objPart = CATIA.ActiveDocument.Part Set objRef = objPart.CreateReferenceFromObject(objPart.MainBody) Set objSPAWkb = CATIA.ActiveDocument.GetWorkBench("SPAWorkbench") Set objMeasurable = objSPAWkb.GetMeasurable(objRef) Dim weightL 'as Long weightL = CATIA.ActiveDocument.product.Analyze.Mass objsheet1.Cells(i+1, 1) = CATIA.ActiveDocument.Name objsheet1.Cells(i+1, 2) = weightL '''''''''''''''''''''''''''''''''''''''''''' partDocument1.Close End If Next End SubRegards
Fernando
https://picasaweb.google.com/102257836106335725208
https://picasaweb.google.com/103462806772634246699...
RE: Macro export list of CATparts to excel
thanks for the help
I tried to use this code but doesnt work on my CATIA V5 R24.
and also I added references on Microsoft Excel 15.0 Object Library in my CATIA
Any possible solution?
Regards
Sub ExampleSendInfoToExcel'EXCEL SETUP
Err.Clear
On Error Resume Next
Set objGEXCELapp = GetObject(, "EXCEL.Application")
If Err.Number <> 0 Then
Err.Clear
Set objGEXCELapp = CreateObject("EXCEL.Application")
End If
' to show excel uncomment next line
objGEXCELapp.Application.Visible = True
Err.Clear
If Err.Number <> 0 Then
Dim strMessage
strMessage = "Error Loading Selected File"
MsgBox strMessage, vbCritical, strSoftwareDeveloper
Err.Clear
End If
intActiveRow = 1
With objGEXCELapp.ActiveSheet
.Cells(intActiveRow, 1).Value = "filename"
.Cells(intActiveRow, 2).Value = "part number"
.Cells(intActiveRow, 3).Value = "description"
End With
End Sub
Sub ExampleGetPartAndProductDetails()
Set activeDoc = CATIA.ActiveDocument
If Right(activeDoc.Name, Len(".CATProduct")) = ".CATProduct" Then
Dim productDocument1 As ProductDocument
Set productDocument1 = CATIA.ActiveDocument
Debug.Print "FileName: " & productDocument1.Name
Debug.Print "PartNumber: " & productDocument1.Product.PartNumber
Debug.Print "Description: " & productDocument1.Product.DescriptionRef
Debug.Print "Revision: " & productDocument1.Product.Revision
Debug.Print "Name: " & productDocument1.Product.Name
Debug.Print "Definition: " & productDocument1.Product.Definition
Debug.Print "Nomenclature: " & productDocument1.Product.Nomenclature
ElseIf Right(activeDoc.Name, Len(".CATPart")) = ".CATPart" Then
Dim partDocument1 As PartDocument
Set partDocument1 = CATIA.ActiveDocument
Debug.Print "FileName: " & partDocument1.Name
Debug.Print "PartNumber: " & partDocument1.Product.PartNumber
Debug.Print "Description: " & partDocument1.Product.DescriptionRef
Debug.Print "Revision: " & partDocument1.Product.Revision
Debug.Print "Name: " & partDocument1.Product.Name
Debug.Print "Definition: " & partDocument1.Product.Definition
Debug.Print "Nomenclature: " & partDocument1.Product.Nomenclature
End If
End Sub
RE: Macro export list of CATparts to excel
Regards
Fernando
https://picasaweb.google.com/102257836106335725208
https://picasaweb.google.com/103462806772634246699...
RE: Macro export list of CATparts to excel
I am so sorry.I used your code already and the output in Excel sheet shows only the title of the variables you wrote.
I tried to modify and I am not able to find/understand the command to retrieve CATIA Partname.
Regards
Tony
RE: Macro export list of CATparts to excel
Of course, it will depends also on what you wish to use, catscript, catvba or excel macro.
Regards
Fernando
https://picasaweb.google.com/102257836106335725208
https://picasaweb.google.com/103462806772634246699...
RE: Macro export list of CATparts to excel
Hello Ferdo,
thanks for the tips. I checked some info in internet and the macro is improving a few =)
If you run the macro, at the moment the program exports the file name of the active open window in CATIA.
My question is now to know .. If I have several windows opened, how can be possible to export the filename to excel?
Thanks again and regards
Sub CATMain()
Dim documents1 As Documents
Set documents1 = CATIA.Documents
MsgBox "The number of documents is "& documents1.Count
Dim doc1 As String
Set doc1 = documents1.Item(1)
Dim partDoc1 as PartDocument
Dim part1 as Part
MsgBox doc1.Name
Set partDoc1 = doc1
For Each doc1 in documents1
MsgBox doc1.Name
Next
Dim doc2 As String
Set doc1 = documents1.Item(1)
Dim myParameters As Parameters
Dim getnom, RealPartNumber As String
Dim Excel As Object
Dim workbooks As workbooks
Dim workbook As workbook
Dim worksheet As Excel.worksheet
On Error Resume Next
Set Excel = GetObject(, "EXCEL.Application")
If Err.Number <> 0 Then
Err.Clear
Set Excel = CreateObject("EXCEL.Application")
Else
Err.Clear
MsgBox "Please note you have to close Excel", vbCritical
Exit Sub
End If
Excel.Visible = True
Set workbooks = Excel.Application.workbooks
Set myworkbook = Excel.workbooks.Add
Set myworksheet = Excel.ActiveWorkbook.Add
Set myworksheet = Excel.Sheets.Add
For Each doc1 in documents1
Excel.Cells(1, 2) = partDoc1.Name
Excel.Cells(2, 2) = partDoci.Name
Next
End Sub
RE: Macro export list of CATparts to excel
Bellow you have an example in CATScript, you need to adapt to your needs.
CODE --> CATScript
Language = "VBSCRIPT" Sub CATMain () Set windowsopen = CATIA.Windows for i = 1 to windowsopen.count Catia.windows.item(i).Activate Dim mainDocument Set mainDocument = CATIA.ActiveDocument Dim oProduct Set oProduct = mainDocument.Product msgbox "CATIA window name is: " & (Catia.windows.item(i).name) & Chr(10) & "CATIA PartNumber in this window is: " & oProduct.PartNumber NEXT End SubRegards
Fernando
https://picasaweb.google.com/102257836106335725208
https://picasaweb.google.com/103462806772634246699...
RE: Macro export list of CATparts to excel
Hi Ferdo,
thanks for the feedback.
My only problem in this code is the following
For Each doc1 in documents1
Excel.Cells(ColumnNum,1)= doc1.Name
ColumnNum = ColumnNum + 1
Next
Because the final output I get in excel is only the first CATPart I have opened in CATIA,
and I would like to display all catparts i have opened in my session.
Sub CATMain()
Dim documents1 As Documents
Set documents1 = CATIA.Documents
MsgBox "The number of documents is "& documents1.Count
Dim doc1 As String
Set doc1 = documents1.Item(1)
Dim partDoc1 as PartDocument
Dim part1 as Part
MsgBox doc1.Name
Set partDoc1 = doc1
For Each doc1 in documents1
MsgBox doc1.Name
Next
Dim doc2 As String
Set doc1 = documents1.Item(1)
Dim Excel As Object
Dim workbooks As workbooks
Dim workbook As workbook
Dim worksheet As Excel.worksheet
On Error Resume Next
Set Excel = GetObject(, "EXCEL.Application")
If Err.Number <> 0 Then
Err.Clear
Set Excel = CreateObject("EXCEL.Application")
Else
Err.Clear
MsgBox "Please note you have to close Excel", vbCritical
Exit Sub
End If
Excel.Visible = True
Set workbooks = Excel.Application.workbooks
Set myworkbook = Excel.workbooks.Add
Set myworksheet = Excel.ActiveWorkbook.Add
Set myworksheet = Excel.Sheets.Add
For Each doc1 in documents1
Excel.Cells(ColumnNum,1)= doc1.Name
ColumnNum = ColumnNum + 1
Next
End Sub
RE: Macro export list of CATparts to excel
Hello Ferdo,
I checked CAAV5 Help and I was able to make the code =) So my first program works.
How could be improved?
Thanks and regards
' Version: 1.0
' Code: CATIA CATScript
' Purpose: Returns the document's full file name, including its path and reports results in Excel
' Autor: Antonio
Sub CATMain()
Dim documents1 As Documents
Set documents1 = CATIA.Documents
Dim doc1 As String
Set doc1 = documents1.Item(1)
Dim partDoc1 as PartDocument
Dim part1 as Part
Set partDoc1 = doc1
For Each doc1 in documents1
Next
Dim SheetCollection As DrawingSheets
Set SheetCollection = CATIA.ActiveDocument.Sheets
Dim myParameters As Parameters
Dim getnom, RealPartNumber,DocPath As String
Dim Excel As Object
Dim workbooks As workbooks
Dim workbook As workbook
Dim worksheet As Excel.worksheet
On Error Resume Next
Set Excel = GetObject(, "EXCEL.Application")
If Err.Number <> 0 Then
Err.Clear
Set Excel = CreateObject("EXCEL.Application")
Else
Err.Clear
MsgBox "Please note you have to close Excel", vbCritical
Exit Sub
End If
Excel.Visible = True
Set workbooks = Excel.Application.workbooks
Set myworkbook = Excel.workbooks.Add
Set myworksheet = Excel.ActiveWorkbook.Add
Set myworksheet = Excel.Sheets.Add
For Each doc1 in documents1
Excel.Cells(ColumnNum,1)= doc1.FullName
ColumnNum = ColumnNum +1
Next
End Sub
RE: Macro export list of CATparts to excel
Did you solved your problem?
You have to define very clear what you want to do...in first post you said that you want to get properties from the catparts in a folder, then you want from all CATParts in the opened session...
You have to understand that this can lead a programmer to solve the task in different ways. You need also to specify your programming environment (you want to have the macro in CATIA or Excel or something else).
Generally speaking, if you give more details, you can get a better feedback. It maters also the operating system, CATIA version or Office version (that's why I prefer CATScript for simple tasks, is not depending so much of what I mentioned before).
Regards
Fernando
https://picasaweb.google.com/102257836106335725208
https://picasaweb.google.com/103462806772634246699...
RE: Macro export list of CATparts to excel
Thanks a lot for your reply.
As you can see this was my first macro and I made this with my limited knowledge in CATScript.
Hope next time will improve :)
Regards
Toni
RE: Macro export list of CATparts to excel
If you are working with CATParts, why do you use something which is used for CATDrawings?
Regards
Fernando
https://picasaweb.google.com/102257836106335725208
https://picasaweb.google.com/103462806772634246699...