Free Macro for Importing Excel Into Revit

From the HKS LINE website:

By Timothy Logan

There are a couple of ways to import spreadsheet information into a Revit model.  You could do it with the expectation to create or modify elements in the model, or you could do it just to have a schedule that’s not associated with model objects.  Of the former, there are plenty of examples, from Autodesk’s Excel Based Model Generator that lets you generate new model elements via an Excel file, to the various Revit/Excel data interop tools that are available from the many resellers and plugin builders of the world.  The other side of things, just being able to place a spreadsheet in your document set, has been a little harder to accomplish, mostly because the functionality didn’t really exist yet within Revit or its Application Programming Interface (API).

For years my response to people asking about how to accomplish this was to tell them about linking the spreadsheet into AutoCAD, then linking the AutoCAD file into Revit.  Any time you made changes to the spreadsheet you would have to open AutoCAD and re-save it with the update, and then reload the AutoCAD link in Revit.  Not the greatest workflow, and in the end it still didn’t really look like it belonged in your document set.

More recently, it’s actually become possible to create a Revit schedule from a spreadsheet, though the process is a little odd.  Recent versions of Revit have added a fair bit of new functionality (and related API hooks) into schedules, particularly in the header portion of the schedule.  It’s now possible to subdivide a single header cell into as many rows and columns as you want, and define custom borders and content for them.  You still cannot control the actual data rows with the same level of control, nor can you add empty rows into a schedule (excepting Key Schedules) to freely define content.

So after investigating the newer API content related to the schedule headers, I found that it is definitely possible to create your own schedule and get it pretty close to a base Excel file.  My first test was creating a macro in Revit 2015 to figure out the general process of creating a schedule and subdividing its header for the purposes of adding data.  Below is my first attempt (as a Revit macro) at this, but all it’s currently set up to do is create a schedule and add dummy information into its header.  All the logic for connecting to Excel was added later to a full blown external command and external application.

1:  public void CreateSchedule()  
2:  {  
3:  // Get the current Revit document  
4:  Document doc = this.ActiveUIDocument.Document;  
5:    
6:  // Start a transaction to create and modify a new schedule  
7:  Transaction trans = new Transaction(doc, "Create Schedule");  
8:  trans.Start();  
9:    
10:  // Creates a new schedule. The new ElementId(-1) defining the category creates the schedule  
11:  // as a multi-category schedule rather than any particular category.  
12:  ViewSchedule sched = ViewSchedule.CreateSchedule(doc, new ElementId(-1));  
13:  sched.Name = "TEST SCHEDULE";  
14:    
15:  // Here I'm using the Assembly Code parameter, a parameter that's built into all Revit families  
16:  // and adding it to the schedule. This way I can make sure it doesn't throw an error because of  
17:  // a missing parameter.  
18:  ElementId assemblyCodeId = new ElementId(BuiltInParameter.UNIFORMAT_CODE);  
19:  ScheduleFieldId parameterId = null;  
20:    
21:  // This searches through all of the schedulable fields in the multi-category schedule and  
22:  // then adds it to the schedule. In the end this will create a schedule with a single column/parameter.  
23:  foreach(SchedulableField schedField in sched.Definition.GetSchedulableFields())  
24:  {  
25:    ElementId paramId = schedField.ParameterId;  
26:    if(paramId == assemblyCodeId)  
27:    {  
28:      ScheduleField field = sched.Definition.AddField(schedField);  
29:      ScheduleFieldId fieldId = field.FieldId;  
30:      parameterId = fieldId;  
31:      break;  
32:    }  
33:  }  
34:    
35:  // Make sure the assembly code parameter was added and then create a filter to make sure nothing shows up.  
36:  // This just makes two opposing rules to make sure no actual data shows up, basically only showing elements  
37:  // whose Assembly Code parameter has two distinct values, which is impossible in Revit.  
38:  if(sched.Definition.GetFieldCount() > 0 && parameterId != null)  
39:  {  
40:    ScheduleFilter sf = new ScheduleFilter(parameterId, ScheduleFilterType.Equal, "NO VALUES FOUND");  
41:    ScheduleFilter sf2 = new ScheduleFilter(parameterId, ScheduleFilterType.Equal, "ALL VALUES FOUND");  
42:    sched.Definition.AddFilter(sf);  
43:    sched.Definition.AddFilter(sf2);  
44:  }  
45:    
46:  // The ShowHeaders function in the ScheduleDefinition is only possible in Revit 2015 R2 and Revit 2016.  
47:  // Earlier versions of Revit and non-R2 versions of 2015 will error here. This is hiding the "Assembly Code"  
48:  // header that would show up based on the parameter we added above.  
49:  ScheduleDefinition definition = sched.Definition;  
50:  definition.ShowHeaders = false;  
51:    
52:  // This sets the overall width of the schedule. Remember that the RevitAPI units are Feet based Imperial,  
53:  // So if you are using different units then you'll have to convert it using the new unit conversion tools  
54:  // in the Revit API. In this we are defining the overall width of the schedule to 0.25', or 3".  
55:  TableSectionData bodyData = sched.GetTableData().GetSectionData(SectionType.Body);  
56:  bodyData.SetColumnWidth(0, 0.25);  
57:    
58:  // This is where we start dividing up the single header and inserting rows and columns in order to add  
59:  // more content.  
60:  TableSectionData headerData = sched.GetTableData().GetSectionData(SectionType.Header);  
61:  headerData.InsertRow(1);  
62:  headerData.InsertColumn(1);  
63:  int numberOfRows = headerData.NumberOfRows;  
64:  int numberOfColumns = headerData.NumberOfColumns;  
65:    
66:  // And here we set the size of the individual rows and columns. Note that with two columns at 0.125' wide,  
67:  // we reach a total of 0.25' which matches our overall width defined in the body section above this.  
68:  headerData.SetRowHeight(0, 0.02083333333333333333333333333333);  
69:  headerData.SetRowHeight(1, 0.02083333333333333333333333333333);  
70:  headerData.SetColumnWidth(0, 0.125);  
71:  headerData.SetColumnWidth(1, 0.125);  
72:    
73:  // And finally we add information to the individual cells. This is all hard coded and doesn't read  
74:  // from any source, but you could add your own code to read a spreadsheet or CSV and add the data to  
75:  // the schedule.  
76:  headerData.SetCellText(0, 0, "THIS IS CELL A1");  
77:  headerData.SetCellText(0, 1, "THIS IS CELL A2");  
78:  headerData.SetCellText(1, 0, "THIS IS CELL B1");  
79:  headerData.SetCellText(1, 1, "THIS IS CELL B2");  
80:    
81:  // And finally commit the transaction to finish the creation process.  
82:  trans.Commit();  
83:  }  

in Revit named “TEST SCHEDULE” that looks something like this:

The video below shows an example of a more fully fleshed out plugin.  It has the ability to import or link an excel file, and updates the file whenever the Revit file is opened or when told to reload.  This does start to show some issues with the plugin.  Some of the cells, particularly on the right side, do not show the cell border’s fully as they should.  If you select the cell in the schedule view it shows it as having a border, but it is not treated as visible.  Also since this video was made Relative and Absolute file pathing has been added for when you link a file.

---

There's more information available on the HKS LINE website