Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Register Log in

Macro export list of CATparts to excel 1

Status
Not open for further replies.
ToniPerez

ToniPerez

Mechanical
Mar 10, 2012
25
0
0
DE

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
 
Replies continue below

Recommended for you

Sort by date Sort by votes
Hello Ferdo,

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
 
Upvote 0 Downvote
Take a look at this post. You can modify the code to loop thru all CATParts in your folder, something like bellow (this is part of a CATScript code, you need to modify maybe).

''''''''''''''''''''''''''''

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

 
Upvote 0 Downvote
Hello Ferdo,
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
 
Upvote 0 Downvote
Hi

This is just an example, you need to continue and modify the script....

Code: 
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 Sub

Regards
Fernando

 
Upvote 0 Downvote
Ho Fernando,
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
 
Upvote 0 Downvote
Hello Ferdo,

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
 
Upvote 0 Downvote


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
 
Upvote 0 Downvote
Hi

Bellow you have an example in CATScript, you need to adapt to your needs.

Code: 
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 Sub

Regards
Fernando

 
Upvote 0 Downvote

Hi Ferdo,

thanks for the feedback.

My only problem in this code is the following

For Each doc1 in documents1
[highlight #CC0000]Excel.Cells(ColumnNum,1)= doc1.Name[/highlight]
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
 
Upvote 0 Downvote

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
 
Upvote 0 Downvote
Hi Antonio

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

 
Upvote 0 Downvote
Hello Ferdo,

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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

howdyitssam
  • Question
Catia V5 Macro - SmartTeam info to Excel
Replies
3
Views
22
Little Cthulhu
Little Cthulhu
Med Che
  • Locked
  • Helpful tip
Macro to Count the Number of Leaders Associated with a 2D Component
Replies
5
Views
7
Little Cthulhu
Little Cthulhu
vangilme
  • Question
CATIA V5 Macro - changing orientation of axis system
Replies
4
Views
36
Boyinlove82
Boyinlove82
pieswt
  • Locked
  • Question
Runtime error CATIA BOM Export using Macro
Replies
1
Views
13
jmarkus
jmarkus
dctene2
  • Question
Macro To Extract A Surface In Space (&amp; Understanding Selection Object)
Replies
3
Views
34
dctene2
dctene2
Back
Top