Author Topic: Embedded Spreadsheet in Drawing  (Read 5220 times)

0 Members and 1 Guest are viewing this topic.

JohnF

  • Guest
Embedded Spreadsheet in Drawing
« on: October 29, 2005, 08:29:42 PM »
How can I select and work with an embedded worksheet in a drawing?

It would have something to do with OLE and I'm not sure if VBA has the grunt to do this.

Regards

Murphy

  • Guest
Re: Embedded Spreadsheet in Drawing
« Reply #1 on: October 30, 2005, 09:11:51 AM »
With VBA you could open the file in Excel and make your changes. I personaly have not done this but, since the OLE object retains the address of the file you can access that information, open it in Excel, search the data for what needs changing, save the file and come back to Autocad to refresh the OLE object.

JohnF

  • Guest
Re: Embedded Spreadsheet in Drawing
« Reply #2 on: October 30, 2005, 01:15:23 PM »
Murphy,

Thanks for the reply....More Questions.

How to obtain the OLE address?

Wouldn't "altering the original file" only work on linked objects and not embedded? 

Regards

Jim Yadon

  • Guest
Re: Embedded Spreadsheet in Drawing
« Reply #3 on: November 01, 2005, 12:41:29 PM »
Have you tried right clicking and selecting from the menu similar to the one below? (this was done with 2k2)

JohnF

  • Guest
Re: Embedded Spreadsheet in Drawing
« Reply #4 on: November 02, 2005, 02:52:06 AM »
Jim,

Thanks for the reply,

I want to control the spreadsheet using programs from VBA and would prefer the sheet embedded to be able to send/receive a drawing and not have to add a spreadsheet.

OLE is the answer, I just need to investigate further on how to access the object using OLE.

I should stop being lazy and asking instead of using "OLE for Dummies" or something like that! :lmao:

When I find out.........and I will; I will post an answer.

jjs

  • Guest
Re: Embedded Spreadsheet in Drawing
« Reply #5 on: November 02, 2005, 09:39:11 AM »
If you are not forced to use OLE for spreadsheets, I would recommend against their use. Let me know if you want the reasons.

JohnF

  • Guest
Re: Embedded Spreadsheet in Drawing
« Reply #6 on: November 02, 2005, 02:23:48 PM »
jjs,

I certainly would like to know.

Thankyou.

jjs

  • Guest
Re: Embedded Spreadsheet in Drawing
« Reply #7 on: November 02, 2005, 02:33:35 PM »
In my past experience with testing OLE out, I believe with ACAD2K, I found the following.

1. The background color of the users computer is the background color of the OLE object. For example, I use a light gray instead of the windows Standard White for the background where you type text in Word and Excel. I also use a black font. So the OLE object will print a little muddy when I am the one printing it as compared to someone with a white background. I am not sure if the font color would also change, I doubt it in excel since you color the text per cell. YOu can test.

2. I believe you can only do one page in Word or Excel, the rest gets left off in autocad.

3. If you rotate the plot in autocad, the OLE object does not rotate with it. So if your drawing is set up as portrait and you rotate the plot to landscape, the OLE stays oriented the same it was in portrait. Which will make it off by 90 degrees in your landscape plot.

4. Line weights are harder to control.

5. We need more people who know how to program to use Autosxedule so somebody besides murphy and I will be updating it.  :-D

I believe there were other reasons, but it has been so long since i tried it.


Jim Yadon

  • Guest
Re: Embedded Spreadsheet in Drawing
« Reply #8 on: November 03, 2005, 12:44:06 PM »
In my past experience with testing OLE out, I believe with ACAD2K, I found the following.

1. The background color of the users computer is the background color of the OLE object. For example, I use a light gray instead of the windows Standard White for the background where you type text in Word and Excel. I also use a black font. So the OLE object will print a little muddy when I am the one printing it as compared to someone with a white background. I am not sure if the font color would also change, I doubt it in excel since you color the text per cell. YOu can test.

2. I believe you can only do one page in Word or Excel, the rest gets left off in autocad.

3. If you rotate the plot in autocad, the OLE object does not rotate with it. So if your drawing is set up as portrait and you rotate the plot to landscape, the OLE stays oriented the same it was in portrait. Which will make it off by 90 degrees in your landscape plot.

4. Line weights are harder to control.

5. We need more people who know how to program to use Autosxedule so somebody besides murphy and I will be updating it.  :-D

I believe there were other reasons, but it has been so long since i tried it.



To expound on one of J's issues and add some of my own -
-There is a limit on the size of the Work Sheet that can be placed in an AutoCAD Drawing (i.e. a door schedule with 300 doors will not make it in a single OLE object)
-The scaling of the Worksheet object is relative to the display area, not the plot area.
-Multiple Worksheet objects accross multiple Layouts generates some odd objects that while selectable, cannot be deleted nor moved and occssionally, obscure or block drawing objects when plotted. (I've not had time to experiment with this and duplicate the issues, just noted them when they happened)

JohnF

  • Guest
Re: Embedded Spreadsheet in Drawing
« Reply #9 on: November 03, 2005, 03:06:27 PM »
Well you certainly know how to dampen one's enthusiasm!

Thank you for explaining the problems.

I will need to review my project and use ACAD for drawing and Excel for calcs and keep them separate.

Regards

John

jjs

  • Guest
Re: Embedded Spreadsheet in Drawing
« Reply #10 on: November 03, 2005, 04:17:03 PM »
there are a number of ways to get your excel sheet into autocad.

Autosxedule is one of them. Ask Murphy for the link.

spanner is another.

There are a number more of them.

Autocad Building systems has a built in schedule routine now.

JohnF

  • Guest
Re: Embedded Spreadsheet in Drawing
« Reply #11 on: November 06, 2005, 01:34:57 AM »
jjs,

I have written code to transfer cell values in Excel into a table in CAD about 3 years ago.

I should enhance the code to update a table in CAD when the spreadsheet changes.

This should surfice in lieu of embedding a spreadsheet.

Thanks again all.

Jim Yadon

  • Guest
Re: Embedded Spreadsheet in Drawing
« Reply #12 on: November 07, 2005, 09:26:56 AM »
The table objects feature that came out with 2k5 is pretty versitile. I have on my wish list of things to do, to write some functions for porting data from Excel into them. There is already functionality for database connectivity.