Before I switch from my C-7 to my C-8 I wanted to put my Z-Wave table into an Excel table so I could see how it changed.
I started out by just dragging and copying the table and then pasting it into Excel. However, that had some problems. I couldn't read the header row (because for some reason it comes through as a white fill) and the routing info and device brand name lines were converted into their own rows.
So I wrote some code that would clean all that up and then add a new column with just the speed in it. I'm pasting in the code below for anyone that wants to use it. As will see I made references to "modHubitat". In Excel VBA I added a module and then renamed it to that. I'll also post a screenshot below of how the first few lines look. As it is an Excel table, if you want the speed in the other order then just use the drop down to reverse.
Of course, you will need to save this in an .xlsm file (macro enabled) which you can pick when you first save it.
Here are all the subs and functions: (BTW, I know "real" programmers just jam all the words together without the underscores, but I find this much more readable. I also know that starting with the variable type (e.g. "s" for string or "l" for long) is no longer in fashion, but I'm just used to it.)
Option Explicit
Sub AA_Z_Wave_Details_With_Speed()
'I made this with AA at the start to make it sort to the top
'This will clean up the copied Z-Wave details
' By getting rid of row 2,
' Making the rows 55 high
' Formatted like a table
' Get rid of the fill
'Then format as medium blue table
' Add the text of every other row to Stats and Device Class
' Add a new last column that will contain the speed it is communicating at
' And then sort if by speed and name
On Error GoTo lblError
Dim lLast_Row As Long
Dim rng As Range
lLast_Row = modHubitat.Last_Row
'Start by getting rid of row 2 if it is empty
If Range("A2").Value = "" Then
ActiveSheet.Rows(2).Delete shift:=xlUp
End If
'Set the range to be the whole table
Set rng = Range("A1:G" & lLast_Row)
modHubitat.Row_Height rng, 55 'I made it that high as it will be 2 lines. Just change 55 to whatever # you want
Range("1:1").RowHeight = 20 'Make header small again. I did this as I wanted to set the range to the whole table above. Change 20 to whatever you like if you don't like 20
modHubitat.No_Fill rng 'When I copied the Z-Wave details from the Web it had a white fill that makes the header text effectively invisible
modHubitat.Make_Table rng
modHubitat.Condense_Rows lLast_Row 'When pasted in the Neighbor info and Device Class (Mfg name) is on a separate row. This appends it to the row above
modHubitat.Speed_To_New_Col (lLast_Row) 'This parses out the speed and puts it into its own column ("H") so we can sort on it.
lblExit:
Set rng = Nothing
Exit Sub
lblError:
Debug.Print "In modHubitat AA_Z_Wave_Details_With_Speed got error: " & Err.Number & ", " & Err.Description
GoTo lblExit
End Sub
Sub Condense_Rows(lLast_Row As Long)
' Macro is to move the route changes (Col B) to a new line in the cell one row above it
' do the same for Device Class (Col D)
On Error GoTo lblError
Dim i As Long
Dim sDevice_Class As String
Dim sRoute As String
Dim sText As String
For i = lLast_Row To 2 Step -1
sRoute = Range("B" & i).Value
If Left(sRoute, 3) <> "PER" Then 'Then this is an extra row. 2nd test prevents
sDevice_Class = Range("D" & i).Value
Range(i & ":" & i).Delete shift:=xlUp
i = i - 1
Range("B" & i).Value = Range("B" & i).Value & Chr(10) & sRoute
Range("D" & i).Value = Range("D" & i).Value & Chr(10) & sDevice_Class
End If
Next
lblExit:
Exit Sub
lblError:
Debug.Print "In modHubitat Condense_Rows got error: " & Err.Number & ", " & Err.Description
GoTo lblExit
End Sub
Function Last_Row(Optional sSheet_Name As String) As Long
On Error GoTo lblError
If sSheet_Name = "" Then
sSheet_Name = ActiveSheet.Name
End If
Last_Row = Sheets(sSheet_Name).Cells(Rows.Count, 1).End(xlUp).Row
If Range("A" & Last_Row).Value = "" Then
Last_Row = Last_Row + 1 'Need + 1 as nothing in column A last cell row
End If
lblExit:
Exit Function
lblError:
Debug.Print "In modHubitat Last_Row got error: " & Err.Number & ", " & Err.Description
GoTo lblExit
End Function
Sub Make_Table(rng As Range)
' Make the copied Z-Wave details into an Excel Table
' Use On Error in case the table already exists
On Error GoTo lblError
With rng
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "ZWave"
End With
lblExit:
Exit Sub
lblError:
GoTo lblExit
End Sub
Sub No_Fill(rng As Range)
' When I copy the Z-Wave details from the web it has a white fill color
' This removes that
On Error GoTo lblError
With rng.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
lblExit:
Exit Sub
lblError:
Debug.Print "In modHubitat No_Fill got error: " & Err.Number & ", " & Err.Description
GoTo lblExit
End Sub
Sub Row_Height(rng As Range, Optional lHeight As Long)
On Error GoTo lblError
If lHeight = 0 Then
lHeight = 55
End If
rng.RowHeight = lHeight
lblExit:
Exit Sub
lblError:
Debug.Print "In modHubitat Row_Height got error: " & Err.Number & ", " & Err.Description
GoTo lblExit
End Sub
Sub Speed_To_New_Col(lLast_Row As Long)
'Doing this will automatically add column H to the table
'if it isn't already there
On Error GoTo lblError
Dim i As Long
Dim lSpace As Long
Dim sSpeed As String
For i = 2 To lLast_Row
sSpeed = Range("G" & i).Text
lSpace = InStrRev(sSpeed, " ") 'Distance from left where that was
sSpeed = Mid(sSpeed, lSpace + 1) 'Go past the space
Range("H" & i).Value = sSpeed
Next
If Range("H1").Value <> "Speed" Then
Range("H1").Value = "Speed"
End If
lblExit:
Exit Sub
lblError:
Debug.Print "In modHubitat Speed_To_New_Col got error: " & Err.Number & ", " & Err.Description
GoTo lblExit
End Sub
This is what the first few rows of mine looks like when it is all done:
