Converting a Hubitat Devices table to an Excel Spreadsheet

The Devices table in Hubitat has no apparent way to export itself to an Excel spreadsheet. However, the table can be selected, copied, and pasted into Excel -- with bad results in terms of formatting, handling of hex numbers, and preservation of links. I did come across a suggestion to paste the copied table into Google Sheets first, and then re-copy and paste into Excel -- this results in a somewhat more tractable spreadsheet (2-line items end up in the same cell), but a lot of manual formatting is needed to be presentable.

So I created a macro to ease things along, if you follow these steps exactly:

  1. In the Devices page of HE, select the setting "gear" icon, select "Display in list/table format", and select ONLY these items (note that "Type" will still be included in the table):
  • "Show device IDs"
  • "Show device network IDs"
  • "Show Source"
  • "Show room name"
  1. Back on the Devices page, place your cursor next to the word "Devices" at the top and hit Ctr-A to select all.
  2. Hit Ctrl-C to copy the entire table.
  3. In a blank Google Sheets spreadsheet (free online), click on cell A1 and press Ctrl-V to paste the copied table.
  4. Immediately hit Ctrl-C to copy that pasted version of the table.
  5. In a blank Excel spreadsheet, click on cell A1 and press Ctrl-V to paste the table copied from Google Sheets.
  6. Save the spreadsheet in the macro-enabled format (.xlsm).
  7. Copy the VBA macro from below into that spreadsheet and run it (apologies for the weird bolding and text coloration in the code -- that's what the forum software does; however, pasting into VBA fixes all that).

You should get a nicely formatted sortable table with the ID, Label, and Name values in separate columns, and the DNI values (short and long) in separate columns (note that some devices do not follow that format).

I don't know how to preserve the hyperlinks from the HE table -- they get messed up (missing or generally inaccurate) in the double-pasting process -- so I delete them. Maybe someone knows how to preserve them?

Sub FormatHubitatDeviceInfo()

'add 2 new columns
    Columns("A:A").Insert Shift:=xlToRight 'insert a new column A to receive the ID info
    Columns("C:C").Insert Shift:=xlToRight 'insert a new column C to receive the Name info
    
'some general initial formating
    Cells.Select
    Selection.ClearFormats
    'remove the hyperlinks because they are not all present and accurate after the pasting operation from Google Sheets
    Selection.ClearHyperlinks

    'some special formating of certain columns as "TEXT" to prevent Excel from treating hex addresses like 0E65 as scientific notation numbers
    Columns("C:H").Select
    Selection.NumberFormat = "@"
    
    Columns("A:H").Select
    Selection.ColumnWidth = 60 'this value may have to be adjusted upword if you have very long Labels, Names, Types, etc.
    'justify all cells to the top-left
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Columns.AutoFit
    Selection.Rows.AutoFit
    
'Dimension (declare) some variables
    Dim ws As Worksheet
    Dim cell As Range
    Dim lastRow As Long
    Dim lines As Variant
    Dim i As Long
    
    Set ws = ActiveSheet ' Set the worksheet to the active sheet

'move the device ID number from column B to column A
    ' Find the last row in the column (assume the data is in column B)
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    ' Loop through each cell in the column (A1:A[lastRow])
    For Each cell In ws.Range("B1:B" & lastRow)
        ' Check if the cell contains a numeric value
        If IsNumeric(cell.Value) And Not IsEmpty(cell.Value) Then
            ' Copy the value and move it one column left and one row down
            cell.Offset(1, -1).Value = cell.Value
            ' Optionally, clear the original cell after moving the value
            cell.ClearContents
        End If
    Next cell
    
'process the Label/Name column, splitting rows with 2 line into 2 columns, B and C
    ' Loop through each cell in the used range of column B
    For Each cell In ws.UsedRange.Columns("B").Cells
        ' Check if the cell contains line breaks (i.e., multiple lines)
        If InStr(cell.Value, Chr(10)) > 0 Then
            ' Split the cell content into lines using the line break (Chr(10))
            lines = Split(cell.Value, Chr(10))
            
            ' If there are two or more lines, move the second line to the next column (C)
            If UBound(lines) >= 1 Then
                ' Move the second line to the next column (C)
                cell.Offset(0, 1).Value = lines(1) ' This moves the second line to column C
                
                ' Remove the second line from the original cell
                cell.Value = lines(0) ' Retain only the first line in the original cell
            End If
        End If
    Next cell

'process the DNI column, splitting rows with 2 line into 2 columns, G and H
    ' Loop through each cell in the used range of column G
    For Each cell In ws.UsedRange.Columns("G").Cells
        ' Check if the cell contains line breaks (i.e., multiple lines)
        If InStr(cell.Value, Chr(10)) > 0 Then
            ' Split the cell content into lines using the line break (Chr(10))
            lines = Split(cell.Value, Chr(10))
            
            ' If there are two or more lines, move the second line to the next column (H)
            If UBound(lines) >= 1 Then
                ' Move the second line to the next column (B)
                cell.Offset(0, 1).Value = lines(1) ' This moves the second line to column H
                
                ' Remove the second line from the original cell
                cell.Value = lines(0) ' Retain only the first line in the original cell
            End If
        End If
    Next cell

'delete all blank rows
    ' Find the last row with data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop from the last row to the first row
    For i = lastRow To 1 Step -1
        ' Check if the entire row is blank
        If Application.WorksheetFunction.CountA(ws.Rows(i)) = 0 Then
            ' Delete the blank row
            ws.Rows(i).Delete
        End If
    Next i

'delete the first 3 rows and insert a header row
    Rows("1:3").Select
    Selection.Delete Shift:=xlUp
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "ID"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Label"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Name"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Type"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Room"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Source"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Device Network ID"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Zigbee ID"
    Range("A1:H1").Select
    
    'color the header row yellow and bold the text
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Font.Bold = True
    
    'freeze the top row
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
        .FreezePanes = True
    End With

'some final formatting
    Cells.Select
    Selection.Columns.AutoFit
    Selection.Rows.AutoFit
    
    'position the cursor at the head of the Label column and turn on the AutoFilter function
    Range("B1").Select
    Selection.AutoFilter

End Sub


Here's a partial screen shot of the Excel spreadsheet I get for my HE Devices:

EDITED macro code to give proper names to the "Device Network ID" and "Zigbee ID" columns.

5 Likes

When I first read your post I was going to suggest you talk to @thebearmay but it seems you worked out a solution to this yourself.... Nice work.... You seem like similarly motivated dev's.... :slight_smile:

Austin Powers Gangsta GIF

2 Likes

UPDATED FOR VERSION 2.4.0.133 [beta]

The Devices table in Hubitat still has no apparent way to export itself to an Excel spreadsheet. Further, with version 2.4.0.133 (and likely any 2.4.x version), the Devices table has changed formatting and the old VBA code above no longer works in Excel. Moreover, there are some oddities in the new Devices table that only show up when copied to a spreadsheet (like the cells for the very first device being misaligned with respect to all other devices, and App-created virtual devices being weird in general).

So I revised my VBA code to ease things along, if you follow these steps exactly:

  1. In the Devices page of HE, select the setting "gear" icon, select "Display in list/table format", and select ONLY these items (note that "Type" will still be included in the table):
  • "Show device IDs"
  • "Show device name" [but not the toggle]
  • "Show device network IDs"
  • "Show device type"
  • "Show device room" [but not the selection button]
  1. Back on the Devices page, place your cursor next to the word "Devices" at the very top of the table (above the table header row) and hit Ctr-A to select all.
  2. Hit Ctrl-C to copy the entire table.
  3. In a blank Google Sheets spreadsheet (free online), click on cell A1 and press Ctrl-V to paste the copied table.
  4. Immediately hit Ctrl-C to copy that pasted version of the table.
  5. In a blank Excel spreadsheet, click on cell A1 and press Ctrl-V to paste the table copied from Google Sheets.
  6. Save the spreadsheet in the macro-enabled format (.xlsm).
  7. Copy the VBA macro from below into that spreadsheet and run it (apologies for the weird bolding and text coloration in the code -- that's what the forum software does; however, pasting into VBA fixes all that).

You should get a nicely formatted sortable table with the ID, Label, Name, Type, Room, Device Network ID, and Zigbee ID values in separate columns (note that some devices do not follow the Device Network ID or Zigbee ID formats).

I don't know how to preserve the hyperlinks from the HE table -- they get messed up (missing or generally inaccurate) in the double-pasting process -- so I delete them.

NOTE WELL: The VBA code is entirely reliant on "dead reckoning" with respect to what cells, rows, and columns it expects to find values. This code works for me, BUT may not work for you.

Sub FormatHubitatDeviceInfo()

'delete first 18 rows
    Rows("1:18").Select
    Selection.Delete Shift:=xlUp
    
'fix misalignment of first device info
    Range("A2").Select
    Selection.Cut
    Range("A1").Select
    ActiveSheet.Paste
    Range("A2").Select
    Selection.Delete Shift:=xlToLeft

'add 2 new columns
    Columns("A:A").Insert Shift:=xlToRight 'insert a new column A to receive the ID info
    Columns("C:C").Insert Shift:=xlToRight 'insert a new column C to receive the Name info

'some general initial formating
    Cells.Select
'    Selection.ClearFormats
    'remove the hyperlinks because they are not all present and accurate after the pasting operation from Google Sheets
    Selection.ClearHyperlinks

    'some special formating of certain columns as "TEXT" to prevent Excel from treating hex addresses like 0E65 as scientific notation numbers
    Columns("C:G").Select
    Selection.NumberFormat = "@"
    
    Columns("A:G").Select
    Selection.ColumnWidth = 60 'this value may have to be adjusted upward if you have very long Labels, Names, Types, etc.
    'justify all cells to the top-left
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Columns.AutoFit
    Selection.Rows.AutoFit
    
'Dimension (declare) some variables
    Dim ws As Worksheet
    Dim cell As Range
    Dim lastRow As Long
    Dim lines As Variant
    Dim i As Long
    
    Set ws = ActiveSheet ' Set the variable ws to the active work sheet

'move the device ID number from column B to column A
    ' Loop through each cell in column B
    For Each cell In ws.UsedRange.Columns("B").Cells
        ' Check if the cell contains a numeric value
        If IsNumeric(cell.Value) And Not IsEmpty(cell.Value) Then
            ' Copy the value and move it one column left and one row down
            cell.Offset(1, -1).Value = cell.Value
            ' clear the original cell after moving the value
            cell.ClearContents
        End If
    Next cell
    
'for each device ID in column A for which "Label" in column B is blank, move the device ID up one row
    ' Loop through each cell in column A
    For Each cell In ws.UsedRange.Columns("A").Cells
        ' Check if the cell contains a numeric value and the next cell is blank
        If IsNumeric(cell.Value) And Not IsEmpty(cell.Value) And IsEmpty(cell.Offset(0, 1).Value) Then
            If cell.Row > 1 Then ' Ensure we're not trying to move the value above row 1
                ' copy the value to one row up
                cell.Offset(-1, 0).Value = cell.Value
                ' Clear the original cell
                cell.ClearContents
            End If
        End If
    Next cell
    
'process the Label/Name column, splitting rows with 2 line into 2 columns, B and C
    ' Loop through each cell in column B
    For Each cell In ws.UsedRange.Columns("B").Cells
        ' Check if the cell contains line breaks (i.e., multiple lines)
        If InStr(cell.Value, Chr(10)) > 0 Then
            ' Split the cell content into lines using the line break (Chr(10))
            lines = Split(cell.Value, Chr(10))
            
            ' If there are two or more lines, move the second line to column C
            If UBound(lines) >= 1 Then
                ' Move the second line to column C
                cell.Offset(0, 1).Value = lines(1) ' This moves the second line to column C
                
                ' Remove the second line from the original cell
                cell.Value = lines(0) ' Retain only the first line in the original cell
            End If
        End If
    Next cell

'process the DNI column, splitting rows with 2 line into 2 columns, F and G
    ' Loop through each cell in column F
    For Each cell In ws.UsedRange.Columns("F").Cells
        ' Check if the cell contains line breaks (i.e., multiple lines)
        If InStr(cell.Value, Chr(10)) > 0 Then
            ' Split the cell content into lines using the line break (Chr(10))
            lines = Split(cell.Value, Chr(10))
            
            ' If there are two or more lines, move the second line to the next column (G)
            If UBound(lines) >= 1 Then
                ' Move the second line to the next column (B)
                cell.Offset(0, 1).Value = lines(1) ' This moves the second line to column G
                
                ' Remove the second line from the original cell
                cell.Value = lines(0) ' Retain only the first line in the original cell
            End If
        End If
    Next cell

'for rows with blank Label and Name values, delete the Label cell and move rest of row left
    ' Loop through each cell in columns B and C
    For Each cell In ws.UsedRange.Columns("B").Cells
      If IsEmpty(cell.Value) And IsEmpty(cell.Offset(0, 1).Value) Then
           cell.Delete Shift:=xlToLeft
      End If
    Next cell

'delete all blank rows
    ' Find the last row with data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop from the last row to the first row
    For i = lastRow To 1 Step -1
        ' Check if the entire row is blank
        If Application.WorksheetFunction.CountA(ws.Rows(i)) = 0 Then
            ' Delete the blank row
            ws.Rows(i).Delete
        End If
    Next i

'insert a header row
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "ID"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Label"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Name"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Type"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Room"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Device Network ID"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Zigbee ID"
    
'some final formatting
    Cells.Select
    'remove the hyperlinks because they are not all present and accurate after the pasting operation from Google Sheets
    Selection.ClearHyperlinks
    Selection.ClearFormats
    Selection.Columns.AutoFit
    Selection.Rows.AutoFit
    
    'color the header row yellow and bold the text
    Range("A1:G1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Font.Bold = True
    
    'freeze the top row
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
        .FreezePanes = True
    End With

'position the cursor at the head of the Label column and turn on the AutoFilter function
    Range("B1").Select
    Selection.AutoFilter

End Sub