Questions similar to this appear in the Be Communities MicroStation Programming Forum.

Text Export Tool

You might want to create a bill of materials in your DGN model. A good way to do that in MicroStation CONNECT is to use Item Types and the built-in reporting tool. If you are using MicroStation V8i then you should consider using tag elements.

However, if legacy working practises have been less rigorous, you may have inherited a loosely-assembled collection of text elements in tabular format …

A characteristic of a tabular layout of text is that it has no structure. To the human eye, of course, it has rows and columns that make sense. Unfortunately, since one text element has no connection with another text element, you can't create a meaningful report. Furthermore, depending on the order in which the text elements were created, their order of appearance once exported may vary. That's also true if you edit parts of the table: elements may be rewritten to the DGN file in a new order.

For example, neither you nor I can make much sense of this, even though it contains all the text you see above …

SIZEITEMDESCRIPTIONQUANTITY 5"PIPE, STAINLESS STEEL 316L, SCH 10SCC15126.0m 3"CC153
FITTING, LRBW 45^ELBOWS S.S 316L SCH10S2 3"CC152 FITTING, LRBW 90^ELBOWS S.S 316L SCH10S3 4"
CC154 FITTING, BW EQ TEE S.S 316L SCH10S5 14"CC151 PIPE STAINLESS STEEL 316L, SCH1.2m 6"x4"
CC156 FITTING, CONC RED S.S 316L SCH10S6

If you attempt to export such a table you end up with a jumble of text. What you want is a neat set of rows, organised into columns. Once that's done you can hope that an external application, such as Microsoft^™ Excel^®, can make some sense of your data.

Spatial Analysis

In order to impart a sense of order to a text table, we need to analyse it spatially. That is, where is the first element? Visually, we know where the first element is — it's in the top-left corner of the table. But where is it in the DGN model, and how do we find it?

Where is the second element? Just to the right of the first element, or just below it? That is, do we analyse the table in a row-major or column-major fashion?

Text Table Exporter

The Text Table Exporter VBA project implements spatial algorithms that sort text for us. You press the Export Text Table button, place a fence around the text table, and the VBA code does the hard work.

What it does is to sort the text elements within the fence into a set of rows and columns. Each row is sorted by the text element's X coordinate, and each column is sorted by each element's Y coordinate. The result is the natural ordering that you might expect.

Finally, the grid of rows and columns is written as a CSV file. The result from the example above is below …

SIZE,ITEM,DESCRIPTION,QUANTITY
14",CC151 PIPE,"STAINLESS STEEL 316L, SCH",1.2m
5",CC151,"PIPE, STAINLESS STEEL 316L, SCH 10S",26.0m
3",CC153,"FITTING, LRBW 45^ELBOWS S.S 316L SCH10S",2
3",CC152,"FITTING, LRBW 90^ELBOWS S.S 316L SCH10S", 3
4",CC154,"FITTING, BW EQ TEE S.S 316L SCH10S",5
6"x4",CC156,"FITTING, CONC RED S.S 316L SCH10S",6

The observant reader will note that text elements that contain a comma are wrapped in double quote marks. That prevents the consuming application (e.g. Excel) from becoming confused if it finds too many commas in a column.

---

Download the Text Table Exporter Tool

You can download the Text Table Exporter MVBA project. The project includes the MVBA project TextTableExporter.mvba and an example DGN file with an example text table used to test the project.

• Unpack the ZIP archive to a suitable location, such as ..\Organisation\Standards\vba
• Start the tool with the MicroStation keyin vba run [TextTableExporter]modMain.Main
• The Text Table Exporter dialog opens

Questions

Post questions about MicroStation programming to the MicroStation Programming Forum.