Extract Mass Properties to Excel

[Evripos]

Hi to all.
I'm trying to modify a Catia macro (CAASpoInertia.CATScript located in the CAAScdDmuUseCases module) to export Excel instead to a Msgbox. Thus far, I cannot make it run. I keep getting a Compile error (User-defined type not defined).
Can someone please help me?
Thank you.

Here is the code I'm using,
Option Explicit

Sub CATMain()

' Retrieve the selected component
Dim oSelection As Selection
Set oSelection = CATIA.ActiveDocument.Selection
Dim oProduct As AnyObject
On Error Resume Next
Set oProduct = oSelection.FindObject("CATIAProduct")
If (Err.Number <> 0) Then
MsgBox "No selected product"
Else
On Error GoTo 0

' Compute the inertia
Dim oInertia As AnyObject
Set oInertia = oProduct.GetTechnologicalObject("Inertia")

' Read the inertia data
Dim dMass As Double
dMass = oInertia.Mass
Dim dCoordinates(2)
oInertia.GetCOGPosition dCoordinates

' Display the results
'MsgBox oProduct.Name & ": Mass = " & CStr(dMass) & ", Center of gravity : X = " & _
'CStr(dCoordinates(0)) & ", Y = " + CStr(dCoordinates(1)) & ", Z = " + CStr(dCoordinates(2))

Dim Excel As Object
Dim workbooks As workbooks
Dim workbook As Excel.workbook
Dim worksheet As Excel.worksheet

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) = oProduct.Name
objsheet1.Cells(1, 2) = CStr(dMass)

End If

End Sub

 

[dougsnell]

Your problem is in the following:

Dim Excel As Object
Dim workbooks As workbooks
Dim workbook As Excel.workbook
Dim worksheet As Excel.worksheet

You likely do not have the Excel Type Libraries Referenced in your VBA Application so it does not recognize the Object Declarations of Workbooks, Excel.Workbook and Excel.Worksheet.

Select Tools > References and find the Excel Type Library to add to your project. If you are planning on keeping this Excel Version agnostic, declare all of those variable as type Object. Keep in mind you will not be provided with any intellisense/Auto-Complete listings for these objects unless the type library reference is added to your project.

--Doug

 

[SkipVought]

Hi,

Option Explicit

Sub CATMain()

'....


'Now for Excel

With CreateObject("Excel.Application")
'This level is the Excel Application

   .Visible = True

   With .workbooks.Add
'This level is the Workbook

       With .WorkSheets(1)
'This level is the first Worksheet

            .Cells(1, 1) = oProduct.Name
            .Cells(1, 2) = CStr(dMass)

       End With

       .SaveAs "YourWorkbookName"

       .Close

   End With
   .Quit
End With
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Evripos]

Doug,
you were absolutely correct. I never thought to check on that.


Skip,
your Excel code worked like a charm.

Thank you so much both.

Yanni

 

[dougsnell]

That has happened to me enough that I recognized the scenario from the error message

--Doug

 

[SkipVought]

Yes, it helps if you have an appropriate object library. Then you can use familiar application objects like Excel, Workbook and Worksheet variables.

However, if you understand some basic things about an application's object model, in this case, Excel, an object library is not necessary, just more convenient.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]