Export Revit schedules to Excel

A Macro to Export all Revit Schedules to a Single Excel File in One Go

Back to top

Original Revit View Schedule

Back to 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

Back to top

"All Dim" Worksheet

containing contents of all worksheets beginning with "Dim - "

Back to top

"QS Desc" Worksheet

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

Back to 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();
      }
    }
  }
}
Back to top

Installation Procedures

Back to top

Change Project Units

http://kctang.com.hk/web/content/set-project-units

Back to top

Open Macro Manager

Back to top

Select Application Tab

Back to top

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

Back to top

Default codes provided

Back to top

Copy and paste the Codes above

Back to top

Add Reference

Back to top

Select Microsoft.Office.Interop.Excel

Back to top

Build Solution

Back to top

Run

Back to top