CEU14 - Why doesn't Paste from Clipboard work the same in CE as it does in SS4?

Hello Stuart,

This is embedded in the Place Table feature so you have more control over your Excel content:

For more details see help topic Home > Drawing Composition > Scheduling and Reporting > Tables > Place Table

Marc. Thanks for coming back to me but that doesn't work as it wants to import the whole sheet. I just want a small selection from the spreadsheet. 

I did try what you suggested with tables but it spent 2 mins 'generating table' which I cancelled out of in the end. 

How do I import (paste?) a small selection from a spreadsheet that keeps all the formatting of the original?

More to the point why has this changed from the way SS4 does it?

stuartw said:

Drawing with shapes for buildings and a spreadsheet for areas

You're manually measuring areas of shapes and adding those to your spreadsheet?

Ian Lapper is suggesting that you tag each shape with your Item Type.  The Item Type can include the area ID (Unit no. in your screenshot) and calculated parking count.  An area report will extract DGN element metrics, such as area, which you can show using different units (square metres and square feet, per your screenshot).

Using the Report definition you can (a) create a DGN table automatically and (b) export to Excel.

Jon Summers said:

You're manually measuring areas of shapes and adding those to your spreadsheet?

Ian Lapper is suggesting that you tag each shape with your Item Type.  The Item Type can include the area ID (Unit no. in your screenshot) and calculated parking count.  An area report will extract DGN element metrics, such as area, which you can show using different units (square metres and square feet, per your screenshot).

Using the Report definition you can (a) create a DGN table automatically and (b) export to Excel.

Yes, because I don't want the accuracy at this stage of the design that measuring a shape will give me. So a building has a lettable area of 2,500sq.ft/232sq.m on the area table but the shape in the drawing may be 233.025sq.m. That is fine! 

Marc Thomas said:

'Load key in mdl load olecntr; olecntr pastemethod'

That doesn't work for me? Why has it taken 14 releases for this to be found and more to the point why doesn't it work as v8i?

stuartw said:

That doesn't work for me?

Do you see a confirming message OLECNTR loaded?

What doesn't work?  Can you paste your spreadsheet into a DGN model?

stuartw said:

Why has it taken 14 releases for this to be found?

It hasn't taken 14 releases.  It used to work but was broken in Update 12 or thereabouts.  Search this Forum for more detail.

HI Stuart

Understand, I think you are missing something.... once the ITEM TYPE is setup and defined in a DGNLIB at Organisation level, it si so so so easy to apply it to the element.  Once applied it is a click on the reports icon and run the area report..

as an example, not only can you get the areas, but you could assign the Tenure, Nit Type, Block Type, Target Area, Actual Area, aspect orientation (NE) and a lot more.  once in the file, this can be managed and reported and it is one source of truth, thus if someone changes the CAD data the report updates.

Its worth reviewing

HI Stuart

Understand, I think you are missing something.... once the ITEM TYPE is setup and defined in a DGNLIB at Organisation level, it si so so so easy to apply it to the element.  Once applied it is a click on the reports icon and run the area report..

as an example, not only can you get the areas, but you could assign the Tenure, Nit Type, Block Type, Target Area, Actual Area, aspect orientation (NE) and a lot more.  once in the file, this can be managed and reported and it is one source of truth, thus if someone changes the CAD data the report updates.

Its worth reviewing

Ian Lapper said:

Understand, I think you are missing something.... once the ITEM TYPE is setup and defined in a DGNLIB at Organisation level, it si so so so easy to apply it to the element.  Once applied it is a click on the reports icon and run the area report..

You may well be right but what we use works (in SS4 anyway) and is simple. At this point we don't need anything else.

I have tried to use item types but found them way too complicated so gave up. If I can't make it work in 30 mins then it's not that user friendly. I like to keep things simple. 

If you've got 30 minutes, try this: Item Types - Populating Items using Lookup Tables

It includes sample files to make life easy.

Marc Thomas said:

If you've got 30 minutes, try this: Item Types - Populating Items using Lookup Tables

It includes sample files to make life easy

I had a look at this but it lost me about a 3rd down the blog.

What's a lookup table & why would I need one of these for building areas?

What's a pick list & why would I need one of these for building areas?

Would I need both of the above for each site that we look at?

Agreed. It took me a while to realise that the approach in the Blog was a bit inane and skipped a few "follow the bouncing ball" steps that would make it much easier to understand.
Basically though, you have an Excel spreadsheet set up with Arrays of information that relate to a Picklist item.

E.g. (I hope this is clearer)

I select a Light Type - ZA280L-GG from LIGHTNAME Picklist, and the Lookup expressions in each field finds all the other data that is associated with that particular model light and automatically populates the fields, saving heaps of repetitive time.

NB. Don't get confused by the "Create the PickList" section as this is a it of a red herring. PickLists in the WorkBook are a One Off deal, and once loaded into the Item Type LIbrary, have no further use, other than maybe a backup. I recommend skipping straight to "Create the Item Type" section.

Each piece of extra data is columnated with a NAME that is specific to that key field (LIGHTNAME)
e.g.

In the ITEMS.xlsx Workbook, I have a Worksheet called VENDORCODE, with the following lines of data.
LIGHTNAME          MANUF           DIRECTION     COLOUR1   COLOUR2   TYPE  (NB. No spaces in name!)
ZA280-GG             ATG Airports    STRAIGHT      GREEN        GREEN        INSET
ZA280-GY             ATG Airports    STRAIGHT      GREEN        YELLOW      INSET ...etc.

The Expression to select the extra data when the key data is chosen is simple, if you reverse engineer what each part is doing, when compared with the example.

Their E.g. LookUp.GetEntry("UKNativeTrees","CommonName",this.CommonName).LatinName

correlates to (for one entry) as (ignore {}. This just groups the fields for easier reading):

LookUp.GetEntry("{Key Sorting Field}","{name of the WorkSheet the Array is listed in}",this.{Key Sorting Field}).{Worksheet Column Name}

Or as the Blog describes it...
"The expression specifies the name of the Excel worksheet containing the lookup table (the Excel file can contain multiple lookup tables on separate worksheets), the controlling value and finally for each property, its name, to link it to the controlling value."

My E.g.

LookUp.GetEntry("LIGHTNAME","VENDORCODE",this.LIGHTNAME).MANUF

So when I choose ZA280-GG, or ZA280-GY, the MANUF is returned as "ATG Airports" populated into the field described in the expression.

Also, the key to finding the WorkBook is to add the Excel Spreadsheet into a path that is defined by ITEMTYPE_LOOKUP
This variable is pointing to a file (in my example) named "ITEMS.xlsx" which has a Worksheet called "VENDORCODE" with the above data over multiple lines of entry, one for each LIGHTNAME.

(I note I have the old name defined as wasn't aware of the change from U13 - good timing)

Sorry to say the same thing over and over, but I hope this helps. The key is understanding how the Expression gets the data from out of the WorkBook, WorkSheets.

Hi Greg,

Creating picklists in Excel is described as it is easier to paste in lists from other sources by this route. You are correct that once the picklists have been imported the Excel file is simply a backup, although obviously with the potential to be updated and used as a source once again.

I will review the blog and see what I can do to make it clearer.