Export Revit schedules to Excel

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

Original Revit View Schedule

Exported to Excel Worksheet

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

"All Dim" Worksheet

containing contents of all worksheets beginning with "Dim - "

"QS Desc" Worksheet

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

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

Installation Procedures

Change Project Units

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

Open Macro Manager

Select Application Tab

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

Default codes provided

Copy and paste the Codes above

Add Reference

Select Microsoft.Office.Interop.Excel

Build Solution

Run