Embeded worksheet

[dogarila]

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

 

[TheTick]

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.

 

[DetroitPickles]

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

 

[dogarila]

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

 

[macPT]

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

 

[StarrRider]

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 &quot;Some Message.&quot;, 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 = &quot;ASDF&quot; '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(, &quot;Excel.application&quot

------> 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(, &quot;Excel.application&quot
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.

 

[macPT]

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

 

[StarrRider]

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!

 

[macPT]

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 &quot;only&quot; 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 &quot;cosmetic&quot; 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

 

[dogarila]

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 &quot;Edit with worksheet&quot;.

Andrew

 

[rocheey]

' ---- 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 &quot;click&quot; 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 &quot;select&quot; this spot to 'select it' so we can get it thru the
' selection manager

success = Part.Extension.SelectByID(&quot;&quot;, &quot;OLEITEM&quot;, 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 &quot;ole object selected&quot;
Set MyOleObj = selmgr.GetSelectedObject3(1)
If MyOleObj Is Nothing Then MsgBox &quot;Unable to cooerce selection into a general object&quot;
End If


End Sub

 

[StarrRider]

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!

 

[macPT]

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

 

[dogarila]

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