Z-Wave Table to an Excel Spreadsheet

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:

2 Likes

Better formatting:

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

1 Like