-
Re: Is there a way to link a cell from an excel file that is embedded into a solidworks drawing and link the cell to a custom property in the solidwworks model?
I don't think so. But you can link an Excel cell into a models Design Table, create a Custom Property, and have that linked in the drawing.
In general, the model drives the drawing. Although BOMs and Insert > Model Item dimensions being bi-directional are exceptions to the rule.
-
Re: Is there a way to link a cell from an excel file that is embedded into a solidworks drawing and link the cell to a custom property in the solidwworks model?
I would happy to entertain a Goto session to show you what I'm trying to accomplish. I think it can be done, but do not understand how to create an excel link from a solidworks Property. Perhaps it can be done via <xml>, but I'm not sure. Do you know how the link is created between a Custom Property and a design table? It appears in the form of $PRP@CustomProperty. I don't understand what the actual link is between SW and the Custom Property.-
Re: Is there a way to link a cell from an excel file that is embedded into a solidworks drawing and link the cell to a custom property in the solidwworks model?
Instead of embedding a standalone Tolerance Table into a drawing and then trying to reference a models custom property, create the Tol' Table below and to the right of a Design Table.
Reference the model type custom property to set the correct tolerances.
Manipulate the DT (hide rows and columns, set outline, etc) to suit how you want the Tolerance Table to appear in the drawing.
Insert the Design Table into the drawing.
-
-
-
Re: Is there a way to link a cell from an excel file that is embedded into a solidworks drawing and link the cell to a custom property in the solidwworks model?
Why not insert a SolidWorks table, then link a cell's contents to a custom property either in the drawing or in the model? Now that 2010 has more excel-like table editing, there are fewer reasons to use excel tables in SolidWorks. In 2010, you can also copy the excel table contents, then insert (paste) them into a SolidWorks table.-
Re: Is there a way to link a cell from an excel file that is embedded into a solidworks drawing and link the cell to a custom property in the solidwworks model?
Hello,
This is in response to a posting from way back in February regarding creating a tolerance table using a design table and inserting it into a drawing.
If I create a design table just for manipulating tolerances, what if I want to add configurations of different parts? Can you have more than 1 design table in a drawing?
Also, keep in mind, this is being created as a drawing template. It must come up every time a user creates a new part using our "standard drawing template". The user is going to enter the part type and units in drafting mode, not model mode. How do I drive the properties, "part type" and "units" which are model properties from a custom properties tab in drafting mode?
-
-
Re: Is there a way to link a cell from an embedded excel file
Not sure if this gona work but try this.
Set your excel sheet outside the SW. Now use this excel sheet for creating DT in part file where you can just link the custom property. Now use the same file as table in drawing.
-
Re: Is there a way to link a cell from an embedded excel file
Hi Depak,
I tried to do this, however, the design table will not be an integral part of the model. It is a drawing only table.
Either that or provide the abilty to use lookup tables and conditional statements for cells in a general table, which I find very little use for.
I can't even get the general table to control decimal places on numbers correctly in 2009.
-
-
Re: Is there a way to link a cell from an excel file that is embedded into a solidworks drawing and link the cell to a custom property in the solidwworks model?
I couldn't resist having a play at this. It should be possible but it will take some work.
Firstly I tested getting hold of the Excel object from SW, I managed to select the OLE object, cast it but then there was no way to retrieve the pointer from SW so that way was out of the question. I believe I had a way to access embedded objects before but I cannot remember what it was, so let's leave that for now.
Next was to think about setting SW from inside the Excel object. Simple. Double-click the OLE object to open it, then go to the Developer Tab that appears in the Excel ribbon at the top of SW once the workbook is being editted and create a new macro for the sheet. For now it doesn't matter where you put it.
Next in the macro get the active SW like you would any other way using the GetObject call, then set properties just like you would with the custom property manager. Use values from the Excel workbook using the ActiveSheet.Range or any other functions you wish. This got me to successfully setting SW custom properties based on Excel.
I reversed the situation so instead of setting custom properties, it pulled them in from SW and set its Range (cells) to those values.
All that was left now is to correctly fire these Excel events when SW changes its custom properties... Fun.
After playing with a lot of events and interesting ideas I came up with 2 solutions to the problem. The first is elegant and makes sure that whenever the file is loaded the embedded Excel sheet is updated so it will always be up-to-date when opened. You can easily add manual updating by adding event hooks on activating the sheets or something like that for example, but for me I think it was updated enough.
Attached is the file that keeps the embedded Excel sheets A1 cell always synced with the custom property called "Description" whenever the file is opened.
My second solution was quite funny I was laughing at the ridiculously complex it was, but it successfully kept both SW and the Excel file in sync the second a property was altered. It involved creating an ODBC dummy source link and fun with SolidWorks equations and Excel COM... but I won't post that one lol.
Luke
-
Re: Is there a way to link a cell from an excel file that is embedded into a solidworks drawing and link the cell to a custom property in the solidwworks model?
Hi Luke,
It's been awhile since I've taken a look at this. I'm not a real code writer, just a hack and copied your macro to see if I could get it to work in my embedded spreadsheet. Unfortunately it doesn't. Also when I tried to edit it, it would not allow me to while I had the embedded spreadsheet open after creating it.
Here is your code:
Option Explicit
Private Sub Workbook_Open()
UpdateCells
End Sub' We need to call this whenever SW updates a custom property
Sub UpdateCells()Dim swApp As SldWorks.SldWorks
Set swApp = New SldWorks.SldWorks
Dim swModel As ModelDoc2: Set swModel = swApp.ActiveDoc
' Gets the custom property description and puts it into the A1 cell
Workbooks(1).Worksheets(1).Range("A1").Value = swModel.CustomInfo2("", "Description")
End SubWhat is the actual code to use to get the cells to update?
You mention use the GetObject call to set Properties just like you would with the custom properties tab, however, I don't see a GetObject call in your macro.
also, your macro is a sub update cells(), but it looks like your main program is a Private sub workbook_open(). Where you make a call to UpdateCells
What does Option Explicit do? Is this needed?
One other point. when I changed the name of the description in your drawing, it did not update the table????
-
Re: Is there a way to link a cell from an excel file that is embedded into a solidworks drawing and link the cell to a custom property in the solidwworks model?
Hi again.
On my last statement, I stand corrected. Your file does update if you close the file and reopen it. This is not exactly what I was looking for. The properties need to update when the file is still open.
Reason is that this drawing with the embedded spreadsheet is a template. When the user opens the template, the default property is blank and the user can then enter the information into the custom properties tab found over in the task pane. I would like this information to propagate into the embedded excel spreadsheet while the file is still open by updating the drawing or just doing it automatically.
-
Re: Is there a way to link a cell from an excel file that is embedded into a solidworks drawing and link the cell to a custom property in the solidwworks model?
David,
Yes I think I looked into that and unfortunately because of how SW loaded the Excel file it suppressed all open events of Excel for many reasons so that was as far as I could get.
You could instead try having an equation in the template (he says... is that even possible lol) that plays with VBA to call a function inside the excel sheet (take a look at Fun with Equations on my site for info on getting raw VBA code to run from an equation).
Luke Malpass
AngelSix (SolidWorks Tutorials and Books)
-
Re: Is there a way to link a cell from an excel file that is embedded into a solidworks drawing and link the cell to a custom property in the solidwworks model?
Hi Luke,
It took awhile, but I finally got your macro to work in my SW Drawing. If I change the custom property, save, and close the file, then reopen, it works like a charm. This is a pretty good intermediate fix. Now all I need to do is get it to update while the drawing is still open. Is there any way to create a SW macro that will trigger the same kind of event as closing and opening the SW model when you execute the update command?
-
-
-
Re: Is there a way to link a cell from an excel file that is embedded into a solidworks drawing and link the cell to a custom property in the solidwworks model?
Hi,
It's been awhile since I visited this, but I'm back on it and I'd really like to get this function to work.
I added a few more custom properties to your macro and it seems to work only when I save and then reopen the file, however, I want the custom properties to update on the fly. In your e-mail you said, "You can easily add manual updating by adding event hooks on activating the sheets".
What do you mean by this?
-
Re: Is there a way to link a cell from an excel file that is embedded into a solidworks drawing and link the cell to a custom property in the solidwworks model?
Hi Luke,
I keep coming back to this issue and I've tried to respond to your previous posting regarding how to update the contents of an embedded excel table from Custom Properties of a Solidworks drawing.
I was able to perform this function successfully as you did, however, there are still several issues from making this functionality perfect and perhaps you can help me out:
1) I had to add some references in my drawing template in order to make it actually work. I have no idea how I figured that out, but I did.regardless. In any case, I have 30 templates I now need to update. My plan was to copy the excel file to all the templates. Will this macro work on every template? Also, when users creae a drawing using the template, will it work for them? Do all users need to manually go ino the embedded spreadsheet into the VBA code and add the references as I had to? (I hope not)
2) Just as you discovered, the only way to update the cells in excel after you've created the drawing is to change the custom properties in the Custom properties tab, save and close the drawing, then reopen it. It is a real pain to do this for users. Is there a way to get the excel table to update "on the fly" while you're in the drawing by running a macro that will somehow activate the embedded excel spreadsheet?
-
Re: Is there a way to link a cell from an excel file that is embedded into a solidworks drawing and link the cell to a custom property in the solidwworks model?
David,
Been a while for me too! I remember playing with this and I did personally take it much further but never posted the results, and yes you can get the embedded Excel to update on the fly but what I did was very very complex hence why I never bothered publishing it. It involved creating an ActiveX add-in that Excel creates an instance of on load (my own AX I made) that then made callbacks to VBA relying on an SQL database. I just did it to prove a point to myself but it is far from a viable solution for most.
Saying that I would be happy to give it another bash if you send me the latest template, and see if I can work some more Excel magic! I already have another set of ideas and almost certain I can get it to update periodically so long as SW doesn't stick its nose in and try to stop execution
Luke Malpass
AngelSix (SolidWorks Tutorials and Books)
-
-