×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Embeded worksheet

Embeded worksheet

Embeded worksheet

(OP)
In my drawing templates I embeded an excel file that handles
revisions. Our drawings are released at revision A. I would like to create a macro that updates a certain cell (in this case 'D3') with today's date.

I know how and what to do for most part of it. What I don't know and require help is:

- How to open the embeded worksheet.
- How to attach to it.

Thanks a lot,

Andrew

RE: Embeded worksheet

There may be some information in this thread that could help you.
Thread559-53929

On justice and on friendship, there is no price, but there are established credit limits.

RE: Embeded worksheet

I use this to Insert a BOM from VB, so you may learn something here. You can also use BOMTable.Attach3 and other BOM Table commands to do what you want. Use the API help in SW and search for "BomTable"...

ViewName = "Drawing View1"
TemplateName = "C:\Automation\Parts_List.xls"

'Select the view, to insert BOM
If (Part.Extension.SelectByID(ViewName, "DRAWINGVIEW", 0, 0, 0, False, 0, Nothing)) Then
    Set SelMgr = Part.SelectionManager
    Set View = SelMgr.GetSelectedObject3(1)
    View.InsertBomTable TemplateName, 0, 0, lngErr
End If

'Set focus back to the sheet
boolstatus = Part.Extension.SelectByID("Cust1", "SHEET", 0, 0, 0, False, 0, Nothing)


Mr. Pickles

RE: Embeded worksheet

(OP)
My question is:

After I select the worksheet object (OLEITEM), how do I get inside to change the values of some cells?

Can I "attach" to it as I attach to a BOM or Design Table?
I tried but it didn't work.

Mr. Pickles:

Your suggestion of setting focus back to sheet works if I double click on my excel object and start the macro from there. What I want is to eliminate the double click.

Andrew

RE: Embeded worksheet

netshop21

In my case, revision number, revision date and revision subject are custom properties (I have 8 levels reserved) and these custom properties, that belongs to the part or assembly, are linked to the drawing sheet. I have an excel macro I can easely create or update the revisions (as some other properties) in the parts/assemblies and the drawings are updated accordingly.

It seams to me that this method it's easier than edit an embebed worksheet. Another advantage is that all the information is saved in the part/assembly and not in the drawing.

What is your opinion?



Regards

RE: Embeded worksheet

macPT

     I worked for several years with a system identical to what netshop21 is describing. Yes, it was a pain in several ways. We did not have a PDM system so at times a revision would be made in a model but not in a drawing. We did have a secretary who kept us honest by checked for obvious mistakes like drawing revision not matching the revision block.
     The only problem that I have with a system like what you describe is that for it to work, there has to be a LOT of CPs to store all of that revision information. We had 5 items to fill out in our revision block - 8 levels - that is 40 CPs to handle. Sure - it's possible - but why do it that way? The only good reason that I can see would be if you have a few people normally do only model changes and a few who only do drawing updates.
     That was what we did for a year or two. When the company had a layoff, the drawing people were cut and suddenly the model people realized how much unnecessary work they had been accidentally creating.

netshop21

Here are a couple of snippets that should help you.
1 - You need to make sure that you are working in a drawing

  Set Part = swApp.ActiveDoc ' Initialization
  If (Part.GetType <> swDocDRAWING) Then  ' Make sure the file is a Drawing
    swApp.SendMsgToUser2 "Some Message.", swMbWarning, swMbOk
    Exit Sub  ' This is not a Drawing so exit
  End If

2 - I assume that your Rev block is only on the first sheet of a drawing, so you need to be sure that you are on that sheet

  Set DwgDoc = swApp.ActiveDoc ' Initialization
' Find the first sheet in the drawing
  Set Sheet = DwgDoc.GetCurrentSheet    'Get the current sheet
  SheetName = Sheet.GetName             'Get the current sheets name
  PreSheetName = "ASDF"                 'Provide a default for the while
  
  While SheetName <> PreSheetName
    SheetName = Sheet.GetName           'Get the current sheets name
    retval = DwgDoc.SheetPrevious       'Get the previous sheet
    Set Sheet = DwgDoc.GetCurrentSheet  'Get the current sheet
    PreSheetName = Sheet.GetName        'Get the current sheets name
  Wend

3 - Beyond this - I don't know - I've never tried it. This was taken directly from the 2001+ API Help file.

To access design tables from a drawing document you need to get the ModelDoc object associated with a particular drawing view and then call this function from that ModelDoc object. To determine if a drawing view has a design table associated with it, refer to View::HasDesignTable.

4 - This will get you the first view in the sheet.

  Set AView = DwgDoc.GetFirstView       'Get the First View
  Set DTable = AView.GetObject(, "Excel.application")

------> I think your code should go here

5 - I believe that the first view in a sheet is actually the template, but don't take my word for this. If this doesn't work, the following is a while loop that you can use to examine each view in the sheet.

  While Not AView Is Nothing
    Set DTable = AView.GetObject(, "Excel.application")
    If Not DTable Is Nothing Then
    End If
    Set AView = AView.GetNextView        'Get the next view
  Wend

6 - I am also a little leery of the GetObject function. I snatched it from the Design Table Example but it was not in the VBA help file. I have seen other cases where an example program is not updated when the API changes. There are several GetOLEObject functions if this doesn't work.

7 - I didn't include any declarations - everything that is Set needs to be declared. The same thing applies to cleanup.

When you get this working the way you want it to - Please reply to this message and let us know. I would like to see the results as well.

Lee

The best leaders inspire by example. When that is not an option, brute force and intimidation works pretty well, too.

RE: Embeded worksheet

StarrRider

In fact we are a small team and there's not a lot of revisions (otherwyse it would mean that we where doing a poor job). We have a maximum of 8 revisions. If we reach this number it means: redesign the product and issue fresh drawings.

About having 40 CP's to handle, I think the only problem is the time for the macro to read an write these properties. Having all in the part and assembly files they are easy to manage, no matter how many you have (in a post somewhere, someone sayd that theres was an automotive company using more than 300 CP's!).

Regards

RE: Embeded worksheet

macPT

     That’s right - You did say you were using a macro to edit them. Somehow, I was thinking about doing it in the Properties dialog. I do use a macro, but it only creates the CPs with a space in them and then opens the Properties dialog.
     Does you macro ignore previous revisions or use 2 forms – 1 for the revision and then load the right CP properties into a second form?
     I did see one that - I think - worked that way. That was a couple years ago and I was fairly new to the VBA at the time and couldn’t follow everything that was happening to make it fit our needs.
 
Lee

Who is the most popular guy at a nude beach?
The guy who can carry 7 donuts and 2 cups of hot coffee!
Who is the most popular girl at a nude beach?
The girl who can eat all 7 donuts!

RE: Embeded worksheet

StarrRider

In order to maintain historical data we don't ignore previous revisions. We have 3 CP's for each revision: rev1, date1, subject1, rev2, date2, subject2,..... util rev8, date8, subject8.

In the drawing format we have a table with 8 lines and 3 columns in which all these CP's are linked.

So either by reading the drawing or running the macro in the part (or going to File/Properties) we know the actual revision state, when it was issued and what was modified. We also know all the previous revisions, when they where issued and what was modified at that time.

Just to clear things, the macro manage more than "only" revisions. It manages the part name, part number, weight, surface treatment, general tolerances, costs,.... These are all informations saved with the part/assembly. The drawings are mainly "cosmetic" documents for manufacturing. We make a great effort to keep all the information in the parts/assemblies so we can even erase drawings and not loose any information.

Regards

RE: Embeded worksheet

(OP)
Guys,

your posts about revisions are very interesting and people can learn a lot from them. I suggest to start a new thread on this subject so more people could jump in with their experience and remarks. In my case, we are doing custom design for automation so very rarely a part (or drawing) cary more than one revision (usually revisions are fixes of screwups in the design). We do not track revisions because we do not need to, there isn't any value in keeping the history of a part that has design errors.

Coming back to the subject of this thread:

Lee, thank you for your snippets. I still don't know how to take control of the existing embeded Excel worksheet from SolidWorks. SoliWorks API has some info related to OLE objects (Sheet.GetOLEObjectData etc.) but I don't think they are what I am looking for. Again that is either double clicking on the worksheet or RMB on the worksheet and select "Edit with worksheet".

Andrew

RE: Embeded worksheet

' ---- cheese.bas ------
' wanna see how far I got before *I* failed?
' ran into a few undocumented commands, a few sw bugs,
' and a few of my own before I gave up. I looked at this
' from the OLEOBJECT perspective ...



Sub main()

    Dim swApp As SldWorks.SldWorks
    Dim Part As ModelDoc2
    Dim selmgr As SldWorks.SelectionMgr
    Dim success As Boolean
    Dim Sheet As SldWorks.Sheet
    Dim Dwg As SldWorks.DrawingDoc
    Dim MyOleObj As Object
    Dim ObjSettings As Variant
    Dim BinaryData As String

    Set swApp = Application.SldWorks
    Set Part = swApp.ActiveDoc
    Set Dwg = Part

    ' get the active sheet
    Set Sheet = Dwg.GetCurrentSheet
    
    ' search for OLE objects
    NumObjects& = Sheet.GetOLEObjectCount   ' 1-based
    If NumObjects& = 0 Then Exit Sub
    
    ' ok, we found at least one OLE object on the current sheet
    Set selmgr = Part.SelectionManager  ' get the selection manager

    ' Now get its location so we can "click" on it ...
    ObjSettings = Sheet.GetOLEObjectSettings(0, bytecount&, aspect&)
    

    ' Bug here:
    ' The Object Browser defines the next call as having 1 parameter..
    ' but the help file shows 3... 1 param works..  Help file also shows it returning
    ' a boolean, but it is returning binary... possibly the COleClientItem class, itself...
    BinaryData = Sheet.GetOLEObjectData(0)
    Debug.Print BinaryData   ' copy into notepad and set font to TERMINAL to view better
    
    ' Back to selecting the OLE object..
    ' ignore bytecount and aspect, we're just interested in finding the middle
    ' so we can select it using the selection managwer
    XTop# = ObjSettings(0): YTop# = ObjSettings(1)
    ' Now "select" this spot to 'select it' so we can get it thru the
    ' selection manager

    success = Part.Extension.SelectByID("", "OLEITEM", XTop#, YTop#, 0, False, 0, Nothing)

    ' see what we selected ....
    numselects& = selmgr.GetSelectedObjectCount     ' get number of OLE objects selected
    SelectType = selmgr.GetSelectedObjectType2(1)   ' get type of object selected
    If SelectType = 7 Then ' OLEITEM
            MsgBox "ole object selected"
            Set MyOleObj = selmgr.GetSelectedObject3(1)
            If MyOleObj Is Nothing Then MsgBox "Unable to cooerce selection into a general object"
    End If
 

End Sub

RE: Embeded worksheet

rocheey

     Are you using 2001+ or 2003? The latest API for SW2003 can be downloaded from http://www.solidworks.com/api/index.cfm?template=Downloads
It is about the 10 item in the list and is dated 12-14-03

Lee

Who is the most popular guy at a nude beach?
The guy who can carry 7 donuts and 2 cups of hot coffee!
Who is the most popular girl at a nude beach?
The girl who can eat all 7 donuts!

RE: Embeded worksheet

netshop21

Sorry! But, as we say in my coutry, these discussions are like cherrys: when we pull only one out of the basket, there are several others that come attach to the first. So when someone posts a problem, the discussion easely derives to other subjects and we tend to forget why it's started.

Regards

RE: Embeded worksheet

(OP)
macPT,

no need to be sorry. I just found your inputs very interesting and I thought more people could find them useful, that's the only reason I suggested a new thread with a more significant subject.

Andrew

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources