Revision history [back]

To move from IDF to excel go under Edit > Copy for spreadsheet.

For the other way around, I'll share a macro that I wrote to copy in IDF format to clipboard so you can import tomorrow (I forget, just remind me please)

To move from IDF to excel go under Edit > Copy for spreadsheet.

For the other way around, I'll share I wrote a macro that I wrote to copy will read in IDF format to clipboard an excel table and export that to an IDF compatible object so that you can import tomorrow (I forget, just remind me please)paste it in the IDF editor or in your .idf text file itself.

Eplus:ObjectClass,
    Object 1,
    (1,1),
    (1,2),
    (1,3);

Eplus:ObjectClass,
    Object 2,
    (2,1),
    (2,2),
    (2,3);

Eplus:ObjectClass,
    Object 3,
    (3,1),
    (3,2),
    (3,3);

Sub Export_To_IDF()
' Author: Julien Marrec
' Date: 2014-05-13
' Version: 1.0
' References: Microsoft Forms Object 2.0, needed for using the Clipboard

' Export current region to an IDF readable format. Paste in text format

Dim wS As Worksheet
Set wS = ActiveSheet

Dim rS%, rE%, cS%, cE%
Dim i%, j%
Dim Class As String
Dim s As String


' Define active region (currentregion = same as CTRL +A) by start and end lines and rows
With ActiveCell.CurrentRegion

    rS = .Rows(1).row
    rE = rS + .Rows.Count - 1
    cS = .Columns(1).Column + 1
    cE = cS + .Columns.Count - 2

End With


' Try setting the IDF class from the cell two lines above the start of the table
Class = wS.Cells(rS - 2, cS - 1).Value


' Ask for the IDF Class of the object
Class = InputBox(Prompt:="Input Class of object (eg: Zone, Building, BuildingSurface:Detailed", Title:="Object Class", Default:=Class)

' If nothing was entered or "Cancel" pressed, exit the sub
If Class = "" Then Exit Sub



' Loop through each column
For j = cS To cE Step 1

    ' Each object (column) starts with the name of the class and a coma
    s = s & Class & ","

    ' Loop on each line
    For i = rS To rE - 1 Step 1

        ' Each attribute of the object (line) is separated by a coma
        s = s & vbCrLf & Chr(9) & wS.Cells(i, j).Value & ","

    Next i

    ' Finish with a semi-colon to close the object
    s = s & vbCrLf & Chr(9) & wS.Cells(rE, j).Value & ";" & vbCrLf & vbCrLf

Next j


' Ask for saving method: write as a file or copy to clipboard
Answer = MsgBox(Prompt:="Click Yes to save it as an idf or txt file, and click No to copy it in the clipboard", Buttons:=vbYesNo, Title:="Saving Method")

If Answer = vbYes Then

    strPath = Application.GetSaveAsFilename(InitialFileName:=Class, FileFilter:="EnergyPlus IDF Files (*.idf), *.idf, Text Files (*.txt), *.txt", Title:="Save output string")

    Dim oFSO As Object
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Dim oFile As Object
    Set oFile = oFSO.CreateTextFile(strPath)
    oFile.WriteLine s
    oFile.Close

    ' Clean-up objects
    Set oFSO = Nothing
    Set oFile = Nothing


ElseIf Answer = vbNo Then

    ' Put generated string to Clipboard
    Dim MyDataObj As New DataObject
    MyDataObj.SetText s
    MyDataObj.PutInClipboard

    MsgBox Prompt:="Copied to clipboard", Buttons:=vbInformation, Title:="Success"

End If

End Sub

IDF to Excel

To move from IDF to excel go under Edit > Copy for spreadsheet.

Excel to IDF

Usage

For the other way around, I wrote a macro that will read in an excel table and export that to an IDF compatible object so that you can paste it in the IDF editor or in your .idf text file itself.

I've personally added this macro to an Excel Add-In and added a button on a separate tab for easy access:

To use the macro, place your cursor in any of the cells of the table and launch it. It will try to find the EPlus class name in the cell that's 2 rows above the top-left corner of the table to prepopulate a dialog box asking you for the class name: either it's good and click OK, or it isn't or it didn't find it, type the name of the class. It'll then generate proper IDF objects that you can paste in IDF editor or paste directly in the .idf file. It'll ask you whether you want to save it as a file (and subsequently ask for a file name) or copy it to clipboard.

Example

Here's an example:

And Here's the output it produced for the example table:

Eplus:ObjectClass,
    Object 1,
    (1,1),
    (1,2),
    (1,3);

Eplus:ObjectClass,
    Object 2,
    (2,1),
    (2,2),
    (2,3);

Eplus:ObjectClass,
    Object 3,
    (3,1),
    (3,2),
    (3,3);

MacroAdd-In

Sub Export_To_IDF()
' Author: Julien Marrec
' Date: 2014-05-13
' Version: 1.0
' References: Microsoft Forms Object 2.0, needed for using the Clipboard

' Export current region to an IDF readable format. Paste in text format

Dim wS As Worksheet
Set wS = ActiveSheet

Dim rS%, rE%, cS%, cE%
Dim i%, j%
Dim Class As String
Dim s As String


' Define active region (currentregion = same as CTRL +A) by start and end lines and rows
With ActiveCell.CurrentRegion

    rS = .Rows(1).row
    rE = rS + .Rows.Count - 1
    cS = .Columns(1).Column + 1
    cE = cS + .Columns.Count - 2

End With


' Try setting the IDF class from the cell two lines above the start of the table
Class = wS.Cells(rS - 2, cS - 1).Value


' Ask for the IDF Class of the object
Class = InputBox(Prompt:="Input Class of object (eg: Zone, Building, BuildingSurface:Detailed", Title:="Object Class", Default:=Class)

' If nothing was entered or "Cancel" pressed, exit the sub
If Class = "" Then Exit Sub



' Loop through each column
For j = cS To cE Step 1

    ' Each object (column) starts with the name of the class and a coma
    s = s & Class & ","

    ' Loop on each line
    For i = rS To rE - 1 Step 1

        ' Each attribute of the object (line) is separated by a coma
        s = s & vbCrLf & Chr(9) & wS.Cells(i, j).Value & ","

    Next i

    ' Finish with a semi-colon to close the object
    s = s & vbCrLf & Chr(9) & wS.Cells(rE, j).Value & ";" & vbCrLf & vbCrLf

Next j


' Ask for saving method: write as a file or copy to clipboard
Answer = MsgBox(Prompt:="Click Yes to save it as an idf or txt file, and click No to copy it in the clipboard", Buttons:=vbYesNo, Title:="Saving Method")

If Answer = vbYes Then

    strPath = Application.GetSaveAsFilename(InitialFileName:=Class, FileFilter:="EnergyPlus IDF Files (*.idf), *.idf, Text Files (*.txt), *.txt", Title:="Save output string")

    Dim oFSO As Object
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Dim oFile As Object
    Set oFile = oFSO.CreateTextFile(strPath)
    oFile.WriteLine s
    oFile.Close

    ' Clean-up objects
    Set oFSO = Nothing
    Set oFile = Nothing


ElseIf Answer = vbNo Then

    ' Put generated string to Clipboard
    Dim MyDataObj As New DataObject
    MyDataObj.SetText s
    MyDataObj.PutInClipboard

    MsgBox Prompt:="Copied to clipboard", Buttons:=vbInformation, Title:="Success"

End If

End Sub