Schedule Revit for QTO

Go to End

-> Top

Note

2 Mar 2022: Expanded to include previous presentation materials titled "Minimalist Modelling and Coding of Revit for Quantities" presented in February 2014 and January 2015 both in Kuala Lumpur, and on 6 April 2016 in Hong Kong and 2 March 2022 at HKIS.

12 Mar 2015: Created. Titled "Export Revit schedules to Excel.

-> Top

Intro

One can model BIM to the last bolt and nut and can use third party software to take-off quantities from the BIM model, but this would mean extra work and more license fees.

This article would show how a Revit model and its families of building blocks can be built, coded and scheduled in a minimalist and self-explanatory manner appropriate and sufficient enough for producing auditable dimensions and quantities for costing.

-> Top

Overview

-> Top

Capabilities of Revit

Can Revit produce Bills of Quantities from Revit model with the press of a button?

• No.
• It is a modelling software, not a BQ production software.

Can Revit model provide quantities?

• Yes.
• Revit being a building information modelling (“BIM”) software defines its building blocks (“elements”) with parameters which contain a lot of information including quantities of work and materials.
• That’s why BIM is called “parametric modelling”.

Are the quantities in compliance with the standard method of measurement?

• No.
• The SMMs of different countries vary.

Are the quantities provided by a Revit model sufficient and ready enough to be converted to quantities according to the standard method of measurement?

• No.
• Some parameters are not schedulable.
• The lengths, areas and volumes of elements provided by a Revit model may not be the desired lengths, areas and volumes for SMM purposes.
• Concrete shoulders at junctions of different mixes are not available.
• Formwork areas are not available.
• Wall and ceiling finish areas are not readily available.

Then, how can we use Revit model to produce quantities for estimates or BQ?

• There are third party quantity take-off software which can help extract quantities from a Revit model and provide them for estimates, bills of quantities and other uses.
• However, one would still need to write the descriptions and do some linking between the descriptions and the modelling elements.
• The linking resembles on-screen taking-off.
• Depending on the suitability of the model, the linking process may be quick or tedious.
• For some software, extensive formulae would need to be set-up.
• This is difficult to check individually and is therefore prone to errors.
• The beauty of such QTO software is that once the descriptions and linking are done, any changes to the models can be monitored.
• The downside is one has to invest in the license and training costs.

When descriptions are required to be written and quantities required to be classified according to the SMM, Quantity Surveyors are still required.

Can we still get something useful from Revit models without third party QTO software?

• Yes, it is the purpose of this article to explain.

-> Top

Should QS build models?

Are Revit models readily available from Architects and Engineers?

• Probably not yet for most of the cases.
• While QTO software can handle 2D CAD drawings, the on-screen taking-off process would be more tedious for 2D CAD drawings.

Without a BIM model, should QS build up models?

• QS has evolved from dimension sheet, cut-and-shuffle, schedule, scale rule, curvimeter, planimeter, Lotus 1-2-3 spreadsheet, Excel worksheet, digitizer, on-screen taking off, etc.
• There is no reason why the QTO must be textural and not graphical.
• Having learned the basic tools, modelling the basic features with Revit is easier than with 2D CAD software.
• It should be within the capability of the average QS to learn, only if he or she has time.
• For the bulk items like structure, fabric and finishes, the time to model and get quantities would be shorter than the time to do manual taking off or on-screen taking off from 2D CAD drawings.
• Even if models are provided by Architects and Engineers, they may not have been modelled in such a way suitable for QTO.
• QS should be able to inspect and understand the models in order to use the models.
• Furthermore, in order not to disturb the integrity of the models provided by the Architects and Engineers, QS would probably need his own set of “QS parameters” and adjust the parameters for his own purposes.
• Therefore, the answer should be "yes".
• QS should build up models in the absence of models or should be able to modify models to suit.

Should QS model every detail?

• No.
• Items like windows, doors, fittings and furniture vary in details between different projects and take some longer time to model.
• All that QS needs is the number for writing up full descriptions or measuring the component quantities per number with reference to the detailed drawings provided by the Architects and Engineers.
• It should however be noted that the level of details of the models if provided by the Architects and Engineers should be no less than those traditionally provided for 2D CAD drawings for estimating, tendering and construction. This should be a simpler benchmark than whatever Level of Development or Level of Details (LOD) or Level of Information Need definitions.

-> Top

How much do we need to measure?

We may measure every bit of concrete, formwork, finishes, etc. exactly net when we measure the concrete members or room finishes.

However, see the following table for the inter-relationship between dimensions and quantities:

Description	Primary Qty	Multiplier	Unit
Wall - grade C40 - T thick (centre line area)	A		Super
Reinforced concrete Grade C40	A	T	Cube
Wall formwork	A	2	Super
To deduct junction with slab if wall measured through slab
To adjust for openings
To measure end of wall
Column - grade C40 - W wide x D deep (height)	H		Run
Reinforced concrete Grade C40	H	W x D	Cube
Column formwork (junction with slab to deduct if measured through slab)	H	(W + D) x 2	Super
To deduct junction with slab if column measured through slab
Suspended beam - grade C30 - W wide x D deep - S slab (length)	L		Run
Reinforced concrete Grade C30	L	W x (D – S)	Cube
Beam formwork below slab	L	W + (D – S) x 2	Super
Deduct slab formwork	L	W x -1	Super
To adjust for shoulders to higher grade walls or columns
Suspended slab - grade C30 - S thick (area)	A		Super
Reinforced concrete Grade C30	A	S	Cube
Slab formwork	A		Super
To deduct junctions with walls, columns and beams
To adjust for openings
Room - finishes group A - net plan area	A		Super
Floor finishes	A		Super
Ceiling finishes	A		Super
To adjust for beam sides
To adjust for door opening
To adjust for work behind fixtures
Room - finishes group A - perimeter including columns - H room height - S skirting height	L		Run
Skirting	L		Run
Wall finishes	L	H – S	Super
To adjust for beam sides and ends
To adjust for openings
To adjust for work behind fixtures
Window W1 - W wide x H high opening - T thick concrete wall - Room A	N		Nr
Window W1, fully described	N		Nr
Glazing	N	Detailed dimensions of W1	Super
Deduct T wall concrete	N	W x H x T x -1	Cube
Deduct wall formwork	N	W x H x 2	Super
Add jambs and soffit formwork, T thick (or boxing number as appropriate)	N	W + H x 2	Run
Deduct Room A wall finishes (assuming no dado)	N	W x H x -1	Super
Add Room A wall finishes to window reveal	N	(W + H x 2) x reveal width	Run
Add Room A window cill	N	W	Run
Door D1 - W wide x H high opening - T thick brickwall - Room finishes group A	N		Nr
Door D1, fully described	N		Nr
Deduct T thick brickwall	N	W x H x -1	Super
Add lintol	N	W + end laps x 2	Run
Deduct Room A skirting	N	W	Run
Deduct Room A wall finishes	N	W x H x -1	Super
Add Room A floor finishes	N	W x part of T as appropriate	Run
Door frames, architraves, painting, dowels, holdfasts, etc.	N	Detailed dimensions of D1	As appropriate

The above shows that:

• We may measure the:
  • aggregate areas of different wall and slab thicknesses,
  • aggregate lengths of different column and beam sections,
  • aggregate plan areas and girths of rooms of the same finishes first (we may call these “primary quantities”, those without bullets),
  • before converting them into concrete, formwork and finishes quantities (we may call these “secondary quantities”, those with bullets).
• We may measure the overall gross quantities first before making detailed adjustments.
• Some adjustments can be made when we measure other elements, e.g.:
  • deduct slab formwork using beam quantities,
  • adjust for wall openings when using window and door quantities, etc.
• The descriptions can be very short only to the extent of containing sufficient information for the purposes of the multiplier.

It follows that instead of expecting a Revit model to provide the quantity of every BQ item, we can just:

• extract the primary quantities adequately described from the Revit model; and
• use Excel to handle the secondary processing.

The need for modelling to the last bolt and nut is reduced.

Excel in fact is more powerful for setting up complicated formulae, and for sorting and filtering rows.

-> Top

Coding and why?

To convey the information from Revit to Excel, it would be good if the information is described in a concise, precise and consistent manner.

All Revit elements are classified and described by Family (Floor, Basic Wall, M_Fixed in the following images) and Types (those under the Search prompts in the images), but these descriptions may not contain the information the way we want and we would need to change them anyway.

Furthermore, if the model is provided by the Architect or Engineer and we do not want to change their descriptions of Families and Types, we may need to create a parameter to contain the precise and consistent information we need.

The following coding (“QS Desc”) should be sufficient and self-explanatory enough yet short and simple to represent the primary quantities in the above table:

• WA-C40-100
• CL-C40-500x600
• BM-C30-300500 : 120SL
• SL-C30-120
• RM-F-A
• RM-P-A
• WD-W1-1200x1500 : IWConc100-RoomA
• DR-D1-920x2200 : IWBrick125-RoomA

This way of coding should be more intuitive and understandable than those cryptic codes using (alpha) numeric codes.

CIC BIM Standards promote the use of FLIP as the element names. It is not that suitable for QS use. Conversion from FLIP to "QS Desc" would be necessary.

It should be easier to do the conversion in bulk with Excel than with Revit schedules.

-> Top

Schedules / Quantities

-> Top

Revit schedules

Revit has the capabilities to produce many schedules, under the project browser:

With so many schedules to represent a modelled project, the choices of the columns in the schedules must be well co-ordinated.

We cannot practically transfer the individual total quantity from the schedules manually one by one for billing purposes.

This would be prone to errors.

The above schedule has been designed such that:

• The left 6 columns are consistently designed for all schedules.
• The Level and Mark are for locational identification of the quantities, like our dimension sheets, for traceability.
• Only the Type, QS Tag, QS Qty and QS Unit are really essential for billing.
• The other columns are there for calculating the QS Qty or cross-checking, and can vary from schedules to schedules.

-> Top

Export function provided by Revit

Revit can also export the schedules to a txt file which can then be imported by Excel to become a worksheet for use:

However, the export can only handle one schedule at a time.

This would be a tedious process to export many schedules one at a time and merge all schedules together.

-> Top

Use macro to export to Excel

A macro (see later below) has been written whereby:

• All the schedules will be exported by a single command to one Excel file with one worksheet per schedule.
• Schedules intended to provide the uniform left 6 columns for billing should have their names prefixed by “Dim – ” and the macro will combine the 3 columns of Type, QS Tag and QS Unit into a single “QS Desc” column in the style of “Type : QS Tag : QS Unit”.
• An “All Dim” worksheet will be created to repeat all information contained in the “Dim – ” schedules:

• A “QS Desc” worksheet will be created to contain a list of the unique QS Desc and their total QS Qty:

• The total QS Qty will be useful for quick reference.
• The Excel file name will contain the time down to the second it is created so that a re-export will not overwrite an existing Excel file.

This macro vastly reduces the time to export Revit schedules to Excel worksheets.

-> Top

Billing Workflow

-> Top

Overall

The primary quantities in the “All Dim” worksheet can be further processed for billing. The workflow are as follows:

• Data --> (extraction from Revit schedules) --> Primary Qty --> (processing ) --> Secondary Qty --> (processing ) --> Estimate or BQ.
• Data --> (direct measurement without using Revit) --> Primary Qty --> (processing ) --> Secondary Qty --> (processing ) --> Estimate or BQ.

-> Top

Primary worksheet

The first 5 columns only of the “All Dim” worksheet shown above are copied to the lower region of a “Primary worksheet”:

The item sequence can be kept unchanged for easy referencing, or may be sorted temporarily.

The Seq column helps re-sort them back to the original sequence.

Highlighted columns are not used for data exported from Revit Schedules because they are reserved for direct measurement at the upper region of the worksheet, where the formula used for Row Qty, say at Row 6, is =PRODUCT(E6:J6), meaning product of Dim1 …. Times3:

-> Top

Secondary worksheet

Column A only of the “QS Desc” worksheet shown above is copied to Column B in the lower region of a “Secondary worksheet”:

right portion:

The upper region of the Secondary worksheet is similar to the upper region of the Primary worksheet for direct measurement which can straightly go to the Estimate or BQ without further processing like the lower region:

`

Further processing of the Primary Qty is done in the lower region.

By using a special formula in Column D in the lower region, each of the Primary Qty here is the total of the Primary Qty of the same QS Desc in the Primary worksheet.

There is no need to sort and group the lines in the Primary worksheet in order to give group total per each QS Desc.

The special formula is actually very simple.

For example,

• The Primary Qty at Row 14 is: =SUMIF(Primary!$J$5:$J$181;$C14;Primary!$K$5:$K$181)
• where $C14 is the QS Desc on the same row,
• Primary!$K$5:$K$181 is the range of QS Desc in the Primary worksheet, and
• Primary!$J$5:$J$181 is the range of Row Qty in the Primary worksheet.
• If the QS Desc in the Primary worksheet matches the QS Desc in the Secondary worksheet,
• then add in the corresponding Row Qty in the Primary worksheet.

The Code will be the code of the Estimate or BQ items.

Again, the Row Qty is the product of all Dims and Times of the same row, using the “=PRODUCT()” function.

They are also called Secondary Qty.

The columns like Times Used in BQ, BQ Unit, BQ Descriptions are making reference to the Estimate or BQ worksheet for error checking.

The Floor columns are for analysis of quantities by floors.

-> Top

Estimate or BQ worksheet

The Estimate or BQ worksheet is like this:

Similar to the Primary Qty or the Secondary worksheet, by using a special formula, each of the Qty here is the total of the Secondary Qty of the same Code in the Secondary worksheet.

The special formula is in the form of:

• =ROUND(SUMIF(Secondary!$J$6:$J$176;$C41;Secondary!$L$6:$L$176);0)

Basically, it means that if the Code in the Secondary worksheet matches the Code in the Estimate or BQ worksheet, then add in the corresponding Row Qty in the Secondary worksheet.

The Floor columns are for internal references only.

When issuing the Estimate or BQ in Excel softcopy, the formulae should be changed to values and other internal reference data should be removed with the empty columns hidden.

-> Top

Setting Revit Schedules

-> Top

Project units

Revit schedules show the units against numerical values by default.

It would not be convenient if the numerical values are exported to Excel worksheet for further calculations because they would not be recognised as numerical values unless the units are removed

A solution would be to define two decimal places for Area and three decimal places for Volume with the units hidden, so as to make them self-explanatory without the need of units.

To define the project units (in metric): click Manage > Project Unit icon:

-> Top

Parameters

Parameters provided by Revit are called system parameters, which cannot be changed though some permit entry of values.

There are two types of parameters which one can define at the Family, Type or Instance level:

• Project Parameters: can appear in schedules but not in tags, but cannot be shared by other projects and families.
• Shared Parameters: can appear in schedules and tags, shared by multiple projects and families, and exported to ODBC.

Shared Parameters are more versatile and useful.

For some Families and Types, a shared parameter can be added to the properties directly.

However, for others, only project parameters can be added to the properties, but a project parameter can borrow a shared parameter, so the shared parameter can still be used but indirectly.

-> Top

Project parameters

To add project parameters: select Manage > Settings > Project Parameters:

Note from the above that either a project parameter or a shared parameter can be added.

-> Top

Shared parameters

To add shared parameters: select Manage > Settings > Shared Parameters:

Select Create, go to a convenient folder, name a Shared Parameter file, e.g. "QS_shared_params", which is a txt file, save and return back to the screen on the left:

Select New under Group, name a New Parameter Group, e.g. "QS", and select OK.

Select New under Parameters, name a new parameter, e.g. “QS Tag”, select Common under Discipline, select Text under Type of Parameter, and select OK:

-> Top

Defining schedule columns

Revit schedule columns (fields) can be selected from “Available fields” (parameters).

If there are no suitable fields available, new columns can be defined either by “Add Parameter” or “Calculated Value”:

-> Top

Level and Mark

Level and Mark parameters are generally available with elements.

-> Top

Type

Elements are classified by Family and generally have a Type parameter available for use in the schedules.

However, instead of using Family and Type as provided by Revit, short-code-like-descriptions are used for Type.

To simplify matters, such descriptions should represent both the information of Family and Type.

-> Top

QS Tag

QS Tag is a new parameter specially added to supplement the information of “Type”.

Select Add Parameter as shown above to open the Parameter Properties window.

Select Shared parameter > Select to open another window.

Select the Parameter Group QS if it already exists, otherwise create it as described for Shared Parameters.

Select QS Tag if it already exists, otherwise create it as described for Shared Parameters by selecting Edit.

Select OK to go back to the Parameter Properties window.

Check “Add to all elements in the selected categories” and Instance.

Select Identity Data under “Group parameter under”, and select OK.

Move up the newly created parameter on the menu to the desired position.

-> Top

QS Qty and QS Unit

QS Qty is a calculated field borrowing its value from other parameters like Count, Length, Area, Volume, etc.

The unit of QS Qty can be Nr, m, m2, m3, i.e. a mixture.

However, Revit does not permit a column of such mixed nature.

Since QS Qty should have 2 decimal places, therefore, it is artificially defined as an Area parameter which according to the setting of the Project Units described earlier will show 2 decimal places.

However, tricks have to be done as shown below to make their numerical values still correct even though they are recognised by Revit as Area parameters:

 

QS Unit is a calculated field specially used to tell the real unit of QS Qty. The formulae are simply “m” for “m”, “m2” for “m2”, “m3” for “m3”, “Nr” for “Nr”, etc.

 

It may be possible for some elements that we need their Count, Length, Area as QS Qty, e.g. Column Nr and Column Length, Wall Area and Wall Length. To handle this, the same schedule can be duplicated only with the QS Qty and QS Unit suitably adjusted to suit while all other fields can be kept the same.

-> Top

Example schedules

-> Top

Wall schedules

A Wall Area schedule gives wall areas as QS Qty for the generation of concrete volume and formwork area:

A Wall Length schedule gives wall lengths as QS Qty for the processing of quantities at junctions with slabs:

Level is actually the base constraint renamed in the schedule heading. This is not absolutely necessary for billing purposes.

 

QS Tag is for entering information like the slab thickness and whether the wall is at slab edge to facilitate adjustment for slab and wall junctions.

Length, Width (i.e. thickness), Area and Volume are system parameters.

Note that Area is not always equal to the elevation areas along the centre line as explained later and would need special treatment.

Wall height is not available probably because the height can vary for a wall.

Lengths at wall ends are not available.

This is to be resolved.

Lengths around openings are not available.

This is to be resolved through giving more information on the Door and Window Schedules.

For blank openings, this is still to be resolved.

QS Mean Area is equal to Volume / Width.

“Mean Area / Length” is equal to QS Mean Area / Length.

-> Top

Representation of wall length, area and volume

Note the wall length, area and volume have the following representations:

 

	Wall length given	Area given	Volume given
Straight wall e.g. 12 m long x 4 m high x 300 mm thick	Centre line of wall = 12 m	Elevation area on one face = 12 x 4 m = 48 m2	Area along centre line x wall thickness = 12 x 4 x 0.3 m = 14.4 m3
Wall L-shaped on plan with mitre joint e.g. 400 mm wall 8 m long + 300 mm wall 5 m long, both x 4 m high	Centre line of wall with the corner shared between the two wings = 7.85 + 4.80 m = 12.65 m	Elevation area based on the extreme length of each wing = 8 x 4 + 5 x 4 m = 32 + 20 m2 = 52 m2	Area along centre line with the corner shared x wall thickness = 7.85 x 4 x 0.4 + 4.80 x 4 x 0.3 = 12.56 + 5.76 m3 = 18.32 m3
Wall L-shaped on plan with butt joint e.g. 8 m x 400 mm wall + 4.6 m x 300 mm wall, both x 4 m high	Same as above	Elevation area based on the self length of each wing = 8 x 4 + 4.6 x 4 m = 32 + 18.4 m2 = 50.4 m2	Area based on self length x wall thickness = 8 x 4 x 0.4 + 4.6 x 4 x 0.3 = 32 x 0.4 + 18.4 x 0.3 = 12.80 + 5.52 m3 = 18.32 m3
Wall L-shaped on plan with butt joint e.g. 7.7 m x 400 mm wall + 5 m x 300 mm wall, both x 4 m high	Same as above	Elevation area based on the self length of each wing = 7.7 x 4 + 5 x 4 m = 30.8 + 20 m2 = 50.8 m2	Area based on self length x wall thickness = 7.7 x 4 x 0.4 + 5 x 4 x 0.3 = 30.8 x 0.4 + 20 x 0.3 = 12.32 + 6 m3 = 18.32 m3
Wall T-off from another wall e.g. 4.6 x 300 mm wall T-off from 400 mm wall	Centre line of T-off wall measured to centre line of main wall = 4.6 + 0.2 = 4.8 m	Elevation area based on the self length of T-off wall = 4.6 x 4 m = 18.4 m2	Area based on self length x wall thickness = 4.6 x 4 x 0.3 = 5.52 m3

The Area and Volume do not make deduction at the junction with floor slab.

Whether the L-shaped wall is mitre or butt jointed, it does not make any difference to the Volume, but the Areas are different for the three cases. 
The area of formwork to sides of wall should be equal to the elevation area along the centre line x height x 2 = (7.85 + 4.8) x 4 x 2 = 50.6 m2 x 2.

The Areas given by Revit for the three cases of L-shaped wall are useless for this purpose.

Wall junctions on plan:

• It is preferred to use mitre joints at corner junction of walls.
• To change the type of wall junctions on plan: select a wall > Wall Joins:
• 
• Select Butt to give a butt joint:
• 
• Select Previous or Next to change the direction of the joint:
• 
• Select Miter for a mitre joint:
• 

After all these discussions, it seems that Volume is a more reliable value to use than Area.

Therefore, QS Qty using Wall Area for Estimate and BQ purposes takes the value of Volume / Width, i.e. the QS Mean Area.

QS Qty using Wall Length is to be used for adjustments for the wall and slab junction, it appears that Length is not accurate enough but close enough and is the only choice available. It is to be addressed.

-> Top

Architectural walls

Architectural walls can be modelled just like a structural wall.

However, unlike structural walls which can be taken as going up to the floor level because they usually have stronger concrete grade, architectural walls should go up to beam or ceiling soffit only and not the floor level.

Revit does not have a feature to let architectural walls automatically go up and stop there.

Therefore, architectural walls have to be modelled one by one to ensure correct height.

With so many architectural walls within a building, this is a time consuming process and is therefore prone to errors.

A solution is to model architectural walls like structural walls up to the floor level above, and select the Join > Switch Join function so that the slabs and beams will override the walls so that the wall volumes and areas reported will be those below the slabs or beams.

-> Top

Floor slab and structural wall junctions

When modelling, after selecting a floor slab >

> > Yes, the following dialogue will appear and will only appear if there are structural walls underneath the slab:

If “Yes” is selected, the volume of the structural walls below will be measured to the underside of the slab, as shown for the wall on the left below.

If “No” is selected, the volume of the structural walls below will be measured to the top of the slab, as shown for the wall on the right below, but the reported volume of the slab will not be reduced.

The reported height of the structural walls when defined to be to the top of the slab will not be changed in both cases.

In theory, when a structural wall is attached to the bottom of a floor slab, the wall top will move when the floor slab is moved up or down.

Furthermore, the Modify Wall menu also has the following choices:

However, the behaviour after attaching or detaching walls using the above slab or wall commands is not quite definite every time.

Furthermore, since the slab and wall junctions will need to be adjusted in any case, when encountering the above dialogue when editing slab boundary, it is better to answer “No” to retain the default treatment.

-> Top

Structural column schedules

A Structural Column Length schedule gives column lengths as QS Qty for the generation of concrete volume and formwork area:

A Structural Column Number schedule gives column numbers as QS Qty for the processing of quantities at junctions with slabs:

 

Column Location Mark is a system parameter which gives the grid line references.

This is used here instead of the usual Mark.

QS Tag is for entering information like the slab thickness and whether the column is an edge or corner column to facilitate adjustment for slab and column junctions.

For a column defined to be of floor to floor height, Volume is a system parameter which gives the volume of concrete below slab, while Length is a system parameter which gives the floor to floor height.

Since the floor to floor height is needed, therefore “QS Qty” takes the value of Length for Column Length schedule. 
It is strange that the column width (b) and depth (h) are not available to the properties window and schedules.

Therefore, two shared parameters QS Width and QS Depth have been added to the Family Type parameters to make them available to the schedules to facilitate error checking.

QS App Slab Tk = Length - Volume / (QS Width * QS Depth) which is useful for indicating the approximate slab thickness for counter-checking any errors in positioning the columns.

Vol / (Wi x Dp) = Volume / (QS Width * QS Depth) gives the length of column below slab.

-> Top

Structural framing length schedule

Structural Framing Length schedule is basically a beam length schedule:

For beams, there are two parameters of Length and Cut Length.

Only the Cut Length is the length between supporting columns or walls.

QS Qty for Beam Length takes the value of Cut Length.

Similar to structural columns, the beam width (b) and depth (h) are not available to the properties window and schedules.

Therefore, two shared parameters QS Width and QS Depth have been added to the Family Type parameters to make them available to the schedules to facilitate error checking:

-> Top

Floor slab area schedule

A Floor Slab Area schedule can be:

QS Tag is for the entry of information about the strutting height.

QS Qty takes Area.

-> Top

Door and window schedules

A Door Number schedule can be:

The values of “From Room : Wall Finish” and “To Room : Wall Finish” are obtained with the help of a self-written macro to facilitate adjustment of wall finishes on both sides of the windows.

A Window Number schedule can be:

QS Tag is for the entry of information about the walls housing the doors and windows to facilitate future measurement of formwork to jambs and soffit, boxing and lintels.

-> Top

Room schedules

The default room schedules provided by Revit can give Floor Area and Perimeter, but not the Nett Ceiling and Beam surface areas, nor wall and column surface areas:

By defining the floor level above a room as the Upper Limit, and entering the slab thickness in negative value as the Limit Offset, the reported Unbounded Height will give the floor to ceiling soffit height, which should be good for generating the wall and column surface areas.

Adjustment would need to be made for the beam surfaces and end junctions.

Adjustment for window and door openings may be taken care of when processing the window and door quantities.

To provide QS Qty for floor, wall, ceiling and skirting, 4 separate schedules are adapted from the Room Schedule.

Room Floor Finishes Area schedule:

Room Ceiling Finishes Area schedule:

Room Wall Finishes Area schedule, with skirting area to be deducted from wall area:

Room Perimeter Length schedule, for skirting:

Room elements do not have a Type parameter.

A calculated field has been created for Type, which takes the names of the finishes as its values.

-> Top

Conclude

Having set up the Revit schedules and the corresponding Excel worksheets once, they can be re-used as a set of templates for other Revit models.

The number of chains of QS Desc --> secondary calculations --> Codes can be expanded as and when they are encountered and retained in the templates to serve future use to reduce the burden of re-defining every time.

Get into modelling which is easy and powerful as soon as possible.

Understand it, identify the limitations and suggest solutions to make it really productive.

-> Top

Macro

A Macro to Export all Revit Schedules to a Single Excel File in One Go is given below.

-> Top

Original Revit View Schedule

-> Top

Exported to Excel Worksheet

with columns "Type", "QS Tag" and "QS Unit" combined as column D followed by column "QS Qty" as column E:

-> Top

"All Dim" Worksheet

containing contents of all worksheets beginning with "Dim - ":

 

-> Top

"QS Desc" Worksheet

containing all unique descriptions of "Type : QS Tag : QS Unit" with total "QS Qty"

 

-> Top

Codes

Revit Macro KCTCL (20150524a).txt

/* Export All Revit Schedules to One Excel file
* Written by: K C Tang
* Using: SharpDevelop.
*
* Date last revised: 24/5/2015
* Revision notes: Handle errors when there are no "Dim - " schedules to
*    fill the "All Dim" worksheet to serve as the source of
*    the pivot table on the "QS Desc" worksheet.
*    Handle errors when the folder is read only,
*    such as a folder of the sample projects provided by Revit.
* Revision notes on 6/5/2015:
*    Speed drastically increased (reduced to 1/4) by using Excel functions
*    as much as possible instead of manipulating Excel file cell by cell.
*    Pivot table used for "QS Desc" worksheet.
*    Sheet header added.
*    Frozen panes set.
*    Page setup set.
* Revision notes on 19/1/2015:
*    Bug fixing and general improvements.
* Date first released for use : 31/12/2014
*    Notes: Export all Revit view schedules to one Excel file.
*    Schedules with names beginning with "Dim - " will have:
*    - columns "Type", "QS Tag" and "QS Unit" combined into one column;
*    - column "QS Qty" moved to next to the combined column;
*    - an "All Dim" worksheet created to contain all these schedules; and
*    - a "QS Desc" worksheet created to contain unique list of "Type : QS Tag : QS Unit".
*
*/
// using libraries
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using Autodesk.Revit.DB;
using Autodesk.Revit.DB.Architecture;
using Autodesk.Revit.UI;
using Autodesk.Revit.UI.Selection;
using Excel = Microsoft.Office.Interop.Excel;
/* Microsoft.Office.Interop.Excel must be added separately
* by selecting SharpDevelop's menu: Project > Add References,
* and searching for it, then selecting it.
*/
namespace KCTCL
{
  [Autodesk.Revit.Attributes.Transaction(Autodesk.Revit.Attributes.TransactionMode.Manual)]
  [Autodesk.Revit.DB.Macros.AddInId("E77FD3DE-05E8-4FD3-B85A-116F5B6F2EEF")]
  public partial class ThisApplication
  {
    private void Module_Startup(object sender, EventArgs e)
    {
    }
    private void Module_Shutdown(object sender, EventArgs e)
    {
    }
    #region Revit Macros generated code
    private void InternalStartup()
    {
      this.Startup += new System.EventHandler(Module_Startup);
      this.Shutdown += new System.EventHandler(Module_Shutdown);
    }
    #endregion
    public void ExportAllSchedulesToOneExcel()
    {
      // all data names must be intialized first and have their types declared with type names before them
      // define row number to insert column header
      const int col_header_row = 3; // const int = integer constant type
      // keep the starting time
      DateTime time_start = DateTime.Now; // Datetime = datetime type
      // select active Revit document
      Document doc = this.ActiveUIDocument.Document; // Document = document type
      // get filename from doc.Title
      string filename_no_ext = doc.Title; // string = string type
      // add ".rvt" temporarily to doc.Title not ending with ".rvt"
      // because file explorer may have been set to hide the extension
      if (!filename_no_ext.EndsWith(".rvt")) // ! = not
      {
        filename_no_ext = filename_no_ext + ".rvt"; // + = join text together
      }
      // get active folder name by removing the full file name
      // from the full pathname which contains the full file name
      string folder_name = doc.PathName.Replace(filename_no_ext, ""); // replace filename with nothing
      // change file extension to the current datetime string
      // to avoid overwriting existing files
      filename_no_ext = filename_no_ext.Replace(".rvt",
        DateTime.Now.ToString("-yyyyMMdd-HHmmss")); // line considered complete only if ending with ";"
      // initilize Excel variables
      Excel.Application xlApp;
      Excel.Workbook xlWorkBook;
      Excel.Worksheet xlWorkSheet;
      Excel.Worksheet xlWorkSheetAllDim;
      Excel.Range xlRange;
      Excel.Range xlRange2;
      Excel.QueryTable xlQuery;
      xlApp = new Excel.Application();
      // check whether Excel is installed
      if (xlApp == null)
      {
        TaskDialog.Show("ExportAllSchedulesToOneExcel", "Excel is not installed!!");
        return;
      }
      // define an object to represent default value
      object default_value = System.Reflection.Missing.Value; // object = object type
      // create new workbook, which by default contains at least 1 worksheet
      xlWorkBook = xlApp.Workbooks.Add(default_value);
      // initialize 2 worksheet variables, all referring to Sheet1 for the time being
      xlWorkSheetAllDim = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
      xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
      // rename Sheet1 to contain contents of all future worksheets
      // with names starting with "Dim - "
      xlWorkSheetAllDim.Name = "All Dim";
      // maximize workbook window
      xlApp.ActiveWindow.WindowState = Excel.XlWindowState.xlMaximized;
      // show menu bars
      xlApp.Visible = true;
      // read viewschedules in Revit active document
      ViewScheduleExportOptions opt = new ViewScheduleExportOptions();
      FilteredElementCollector collector = new FilteredElementCollector(doc).OfClass(typeof(ViewSchedule));
      if (collector.ToElementIds().Count == 0) // == means compare for equality
      {
        TaskDialog.Show("ExportAllSchedulesToOneExcel", "No schedule available!!");
        // close workbook without saving
        xlWorkBook.Close(false, default_value, default_value);
        xlApp.Quit();
        // release objects
        releaseObject(xlWorkSheet);
        releaseObject(xlWorkSheetAllDim);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);
        return;
      }
      // sort elements in collector in ascending order
      IOrderedEnumerable< ViewSchedule > sorted_collector =
        from ViewSchedule view_schedule in collector orderby view_schedule.Name ascending select view_schedule;
      // process schedule in ascending order
      int all_dim_new_row = 0;
      foreach (ViewSchedule view_schedule in sorted_collector)
      {
        // check if schedule name too long
        if (view_schedule.Name.Length > 31 )
        {
          TaskDialog.Show("ExportAllSchedulesToOneExcel",
            view_schedule.Name + "\n" + "Schedule name should not be more than 31 characters!!");
          // release objects
          releaseObject(xlWorkSheet);
          releaseObject(xlWorkSheetAllDim);
          releaseObject(xlWorkBook);
          releaseObject(xlApp);
          return;
        }
      }
      foreach (ViewSchedule view_schedule in sorted_collector)
      {
        if (view_schedule.Name.StartsWith("<"))
        {
           // skip schedule with name beginning with "<", such as "<Revision Schedule>"
        } else
        {
          // reduce filename length longer than 31
          if (31 < view_schedule.Name.Length )
          {
            view_schedule.Name = view_schedule.Name.Substring(0, 14) + " name length > 31";
          }
          // replace special character with "_"
          view_schedule.Name = view_schedule.Name
            .Replace( ':', '_' )
            .Replace( '*', '_' )
            .Replace( '?', '_' )
            .Replace( '/', '_' )
            .Replace( '\\', '_' )
            .Replace( '[', '_' )
            .Replace( ']', '_' );
          // export schedule to txt file
          try {
            view_schedule.Export(folder_name, filename_no_ext + ".txt", opt);   
          } catch (Exception) {
            TaskDialog.Show("Exporting view schedules",
                            "Errors occurred -\n" +
                            "possibly the folder is read only\n" +
                            "e.g. in the case of sample projects provided by Revit,\n" +
                            "save the project to another folder first.\n\n" +
                            "Close to exit program.");
            // release objects
            releaseObject(xlWorkSheet);
            releaseObject(xlWorkSheetAllDim);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
            return;
          }
          // add a worksheet
          xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Add(default_value);
          // move it to become the last worksheet
          xlWorkSheet.Move(default_value, xlWorkBook.Worksheets[xlWorkBook.Worksheets.Count]);
          // name worksheet as schedule name
          xlWorkSheet.Name = view_schedule.Name;
          // import txt file into worksheet starting at cell at column A, one row above col_header_row
          xlQuery = xlWorkSheet.QueryTables.Add(
            "TEXT;" + folder_name + filename_no_ext + ".txt",
            xlWorkSheet.get_Range("A" + (col_header_row - 1)));
          xlQuery.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
          xlQuery.Refresh(false); // false means refresh but not return until refresh is finished
          xlQuery.Delete(); // delete the query
          // input into All Dim worksheet for schedules with names starting with "Dim - "
          if (view_schedule.Name.StartsWith("Dim - "))
          {
            // insert blank new column A
            xlWorkSheet.get_Range("A1").EntireColumn.Insert();
            // find Type, QS Tag and QS Unit columns
            int col_Type = 0;
            int col_QS_Tag = 0;
            int col_QS_Unit = 0;
            int col_QS_Qty = 0;
            col_Type = xlColumnFindExact(xlWorkSheet, "Type", col_header_row);
            col_QS_Tag = xlColumnFindExact(xlWorkSheet, "QS Tag", col_header_row);
            col_QS_Unit = xlColumnFindExact(xlWorkSheet, "QS Unit", col_header_row);
            // define cell formula of column A starting from col_header_row
            xlRange = xlWorkSheet.Range["A" + col_header_row]; // source range to copy from
            xlRange2 = xlWorkSheet.Range["A" + col_header_row, "A" + xlRowLast(xlWorkSheet)]; // target range to copy to
            string range_formula = "=";
            string colon = "&\" : \"&"; // which stands for quote &" : "& unquote
            if (col_Type != 0)
            {
              range_formula += xlColumnAddress(col_Type) + col_header_row;
               }
               if (col_QS_Tag != 0)
               {
              range_formula += colon + xlColumnAddress(col_QS_Tag) + col_header_row;
            }
               if (col_QS_Unit != 0)
            {
              range_formula += colon + xlColumnAddress(col_QS_Unit) + col_header_row;
            }
            if (range_formula != "=")
            {
              xlRange.Formula = range_formula;
              xlRange.AutoFill(xlRange2, Excel.XlAutoFillType.xlFillCopy);
            }
            // remove cell formula and leave value for the combined column A
            xlRange2.Value2 = xlRange2.Value2;
            // remove Type, QS Tag and QS Unit columns
            col_Type = xlColumnFindExact(xlWorkSheet, "Type", col_header_row);
            if (col_Type != 0)
            {
              xlRange = (Excel.Range)xlWorkSheet.Cells[1, col_Type];
              xlRange.EntireColumn.Delete(default_value);
            }
            col_QS_Tag = xlColumnFindExact(xlWorkSheet, "QS Tag", col_header_row);
            if (col_QS_Tag != 0)
            {
              xlRange = (Excel.Range)xlWorkSheet.Cells[1, col_QS_Tag];
              xlRange.EntireColumn.Delete(default_value);
            }
            col_QS_Unit = xlColumnFindExact(xlWorkSheet, "QS Unit", col_header_row);
            if (col_QS_Unit != 0)
            {
              xlRange = (Excel.Range)xlWorkSheet.Cells[1, col_QS_Unit];
              xlRange.EntireColumn.Delete(default_value);
            }
            // move QS Qty column to column D, but if it is before column D,
            // move to column E to compensate the shifting of columns after cut
            col_QS_Qty = xlColumnFindExact(xlWorkSheet, "QS Qty", col_header_row);
            if (col_QS_Qty < 4)
            {
              xlColumnMove(xlWorkSheet, col_QS_Qty, 5);
            } else
            {
              xlColumnMove(xlWorkSheet, col_QS_Qty, 4);
            }
            // move combined column A to column C
            xlColumnMove(xlWorkSheet, 1, 4);
            // bold down to col_header_row
            xlRange = xlWorkSheet.get_Range("A1", "A" + col_header_row);
            xlRange.EntireRow.Font.Bold = true;
            // copy whole worksheet to All Dim to the next new row
            all_dim_new_row += 1;
            xlWorkSheet.UsedRange.Copy(xlWorkSheetAllDim.get_Range("A"+all_dim_new_row));
            all_dim_new_row = xlRowLast(xlWorkSheetAllDim);
          } else
          {
            // bold down to col_header_row
            xlWorkSheet.get_Range("A1", "A" + col_header_row).EntireRow.Font.Bold = true;
          }
          // delete txt file
          System.IO.File.Delete(folder_name + filename_no_ext + ".txt");
        }
      }
      // move it to become the first worksheet
      xlWorkSheetAllDim.Move(xlWorkBook.Worksheets[1]);
      // add and name a worksheet to contain unique QS Desc
      xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Add(default_value);
      xlWorkSheet.Name = "QS Desc";
      // generate pivot table if "All Dim" has data rows
      if (xlRowLast(xlWorkSheetAllDim) > col_header_row)
      {
        // define pivot table data source
        xlRange = xlWorkSheetAllDim.get_Range("C" + col_header_row,"D" + xlRowLast(xlWorkSheetAllDim));
        Excel.PivotCache xlPivotCache = xlWorkBook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, xlRange);
        Excel.PivotTables xlPivotTables = (Excel.PivotTables)xlWorkSheet.PivotTables();
        // define pivot table in the QS Desc worksheet
        Excel.PivotTable xlPivotTable = xlPivotTables.Add(xlPivotCache, xlWorkSheet.Range["A2"], "QS Desc", default_value, default_value);
        xlPivotTable.SmallGrid = false;
        xlPivotTable.ShowTableStyleRowStripes = true;
        xlPivotTable.TableStyle2 = "PivotStyleLight1";
        Excel.PivotField xlPivotField = (Excel.PivotField)xlPivotTable.PivotFields("Type : QS Tag : QS Unit");
        xlPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
        xlPivotTable.AddDataField(xlPivotTable.PivotFields("QS Qty"), "Sum of QS Qty", Excel.XlConsolidationFunction.xlSum);
      }
      // format QS Qty column
      xlRange = xlWorkSheet.get_Range("B1");
      xlRange.EntireColumn.NumberFormat = "#,##0.00";
      // move it to become the first worksheet
      xlWorkSheet.Move(xlWorkBook.Worksheets[1]);
      // loop through all worksheets
      int loop_A_Mx = xlWorkBook.Worksheets.Count;
      for (int loop_A = loop_A_Mx; loop_A >= 1; loop_A--)
      {
        // get worksheet
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(loop_A);
        // freeze top rows
        ((Excel._Worksheet)xlWorkSheet).Activate(); // cast to Excel._Worksheet to avoid the ambiguity that "Activate" is also used as an event
        xlWorkSheet.Application.ActiveWindow.SplitRow = col_header_row;
        xlWorkSheet.Application.ActiveWindow.FreezePanes = true;
        // insert new column A
        xlWorkSheet.get_Range("A1").EntireColumn.Insert();
        // assign column A with row number
        int last_row = xlRowLast(xlWorkSheet);
        xlWorkSheet.Cells[1,1] = 1;
        xlRange = xlWorkSheet.get_Range("A1","A" + xlRowLast(xlWorkSheet));
        xlRange.Font.Bold = false;
        xlRange.DataSeries(default_value,
          Excel.XlDataSeriesType.xlDataSeriesLinear,
          Excel.XlDataSeriesDate.xlDay,
          "1", default_value, default_value);
        // autofit column widths
        xlWorkSheet.Columns.EntireColumn.AutoFit();
        // assign cell B1 with filename
        xlWorkSheet.Cells[1,2] = filename_no_ext.ToUpper();
        xlWorkSheet.get_Range("B1").Font.Bold = true;
        // define page setup
        xlWorkSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
        xlWorkSheet.PageSetup.PrintTitleRows = "$1:$" + col_header_row;
        xlWorkSheet.PageSetup.LeftFooter = filename_no_ext;
        xlWorkSheet.PageSetup.RightFooter = "&P/&N";
        xlWorkSheet.PageSetup.Zoom = false; // needs to be false for FitToPagesWide to work
        xlWorkSheet.PageSetup.FitToPagesTall = false; // need to be false for FitToPagesWide to work
        xlWorkSheet.PageSetup.FitToPagesWide = 1;
      }
      // save workbook
      xlWorkBook.SaveAs(folder_name + filename_no_ext,
        default_value, default_value, default_value,
        default_value, default_value,
        Excel.XlSaveAsAccessMode.xlNoChange,
        default_value, true, default_value,
        default_value, true);
      // release objects
      releaseObject(xlWorkSheet);
      releaseObject(xlWorkSheetAllDim);
      releaseObject(xlWorkBook);
      releaseObject(xlApp);
      TaskDialog.Show("Export All Schedules To One Excel",
        "Finished!" + "\nTime Spent " + DateTime.Now.Subtract(time_start).Seconds + " seconds");
    }
    private string xlCellAddress(int row, int col)
    {
      // change cell address from (100, 1) to (A100) style
      string prompt = (row + "\n\t" + col + "\n\t");  
      if (row < 1 || row > 1048576)
      {
        TaskDialog.Show("Excel Row Number", "Error - must be within 1 - 1048576!!");
        return null;
      }
      // append row number to alphabetical column reference
      return xlColumnAddress(col) + row.ToString();
    }
    private string xlCellValue2(Excel.Worksheet w_s, int row, int col)
    {
      // return value of worksheet cell
      Excel.Range xlRange = (Excel.Range)w_s.Cells[row,col];
      if (xlRange.Value2 != null)
      {
        return xlRange.Value2.ToString();
      } else
      {
        return "";
      }
    }
    private string xlColumnAddress(int col)
    {
      // convert column number to alphabetical reference
      if (col < 1 || col > 16384)
      {
        TaskDialog.Show("Excel Column Number", "Error - must be within 1 - 16384!!");
        return null;
      }
      int remainder = 0;
      string result = "";
      for (int loop_A = 0; loop_A < 3; loop_A++)
      {
        // get remainder after division by 26
        remainder = ((col - 1) % 26) + 1;
        if (remainder != 0)
        {
          // match the remainder to alphabets A to Z where A is char 65
          // precede the alphabet to the previous result
          result = Convert.ToChar(remainder + 64).ToString() + result;
        }
        col = ((col - 1) / 26);
        // do it three times
      }
      return result;
    }
    private int xlColumnFindExact(Excel.Worksheet w_s, string find_what, int which_row)
    {
      object default_value = System.Reflection.Missing.Value;
      Excel.Range xlFound = w_s.get_Range(which_row + ":" + which_row);
      xlFound = xlFound.Find(find_what, default_value,
        Excel.XlFindLookIn.xlValues,
        Excel.XlLookAt.xlWhole,
        Excel.XlSearchOrder.xlByRows,
        Excel.XlSearchDirection.xlNext,
        false, default_value, default_value);
      if (xlFound != null)
      {
        return xlFound.Column;
      } else
      {
        return 0;
      }
    }
    private void xlColumnMove(Excel.Worksheet w_s, int col_from, int col_to)
    {
      if ((col_from != 0) & (col_to != 0) & (col_from != col_to))
      {
        Excel.Range from_range = (Excel.Range)w_s.Cells[1,col_from];
        Excel.Range to_range = (Excel.Range)w_s.Cells[1,col_to];
        to_range.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, from_range.EntireColumn.Cut());
      }
    }
    private int xlRowLast(Excel.Worksheet w_s)
    {
      // return last used row number of worksheet
      return w_s.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell,Type.Missing).Row;
    }
    private void releaseObject(object obj)
    {
      try
      {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
      }
      catch (Exception ex)
      {
        obj = null;
        TaskDialog.Show("Excel file created","Exception Occurred while releasing object " + ex.ToString());
      }
      finally
      {
        GC.Collect();
      }
    }
  }
}

-> Top

Installation Procedures

-> Top

Change Project Units

As described above.

-> Top

Open Macro Manager

-> Top

Select Application Tab

-> Top

Create C# Module called "KCTCL" (all in capital letters)

 

-> Top

Default codes provided

 

-> Top

Copy and paste the Codes above

-> Top

Add Reference

 

-> Top

Select Microsoft.Office.Interop.Excel

-> Top

Build Solution

-> Top

Run

 

End of Page

-> Top