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

1 Like