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:
- 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"
- Back on the Devices page, place your cursor next to the word "Devices" at the top and hit Ctr-A to select all.
- Hit Ctrl-C to copy the entire table.
- In a blank Google Sheets spreadsheet (free online), click on cell A1 and press Ctrl-V to paste the copied table.
- Immediately hit Ctrl-C to copy that pasted version of the table.
- In a blank Excel spreadsheet, click on cell A1 and press Ctrl-V to paste the table copied from Google Sheets.
- Save the spreadsheet in the macro-enabled format (.xlsm).
- 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.
