Monthly Archives: December 2021

Setting up Mitel VoIP phones with PC ports on different VLANs

Phones, , ,

Recently ran into a situation where we were taking over a office that had a single Ethernet run to each desk that we needed to provide new IP phone along with PC connectivity. Now I don’t really like doing this, multiple points of failure, more complicated config, etc but running new cabling was going to be cost prohibitive so I have to work with what I got.

The core switch was going to be a older Dell N3024p, the phones Mitel 5340. We use DHCP exclusively for the Mitels using Option 43 which there is already a good guide on setting up. The tricky part was getting the phone on one VLAN but the PC port on a different VLAN and not having to manually set anything up on the phones. So this is what we ended up doing.

For this network VLAN 10 was for users/computers (10.40.10.*) and VLAN 20 was for phones (10.40.20.*). The switch ports on the Dell were setup as “General” ports using the following config:

switchport mode general
switchport general pvid 10
switchport general allowed vlan add 10
switchport general allowed vlan add 20 tagged
switchport general allowed vlan remove 1
switchport voice vlan 20

So any packet arriving at the switch that was not tagged would be tagged VLAN 10. It also accepted tagged packets as long as they were from VLAN 20. After removing the default VLAN 1 nothing else would be accepted on the port. I also set VLAN 20 as voice traffic as the Dell switches have some QoS for voice that is enabled.

Now the tricky part. I want the phone to request DHCP but by default it’s going to start off on VLAN 10 since it’s traffic won’t be tagged at first. In comes Option 43. I add the manufacture specific DHCP option to the DHCP scope for the users subnet, 10.40.10.*, since that’s what the phone will first request on and I only put in enough for the phone to switch over to the phones vlan:

id:ipphone.mitel.com;vlan=20

The phone gets this, sets itself to VLAN 20, and retries DHCP. Now the request is coming from the 10.40.20.* subnet which in DHCP has the full Option 43 string:

id:ipphone.mitel.com;sw_tftp=(serverIP);call_srv=(serverIP);l2p=6v6;vlan=20;dscp=46v46s26

So now it gets the correct config from the correct subnet. And anything plugged into the PC port gets passed through untagged which gets put on the users subnet and pulls DHCP from there correctly.

Batch Converting Excel XLS files to XLSX

Scripts and Scripting, , , , ,

A little while back I posted a macro to batch covert Visio VSD files to VSDX files which got a decent number of people messaging me. Recently I found how many excel files we had using the old format which just like old Visio files take up a lot of extra space. So I went through and modified my Visio converter over for Excel. So here is a step by step to write your own Excel file converter:

  1. Open a new Excel document. Save it as a “Excel Macro-Enabled Workbook (*.xlsm)
  2. In the first cell put something like “To run the conversion hit ALT+F11 to open the program then F5 to run it”.
  3. Hit ALT+F11 to open up the Microsoft Visual Basic for Applications screen
  4. Right click “ThisWorkbook” at the top left then Insert -> Module
  5. In the module copy and paste the following in:
Public FilesAttempted As Integer
Public FilesConverted As Integer
Public FilesDeleted As Integer
Public FilesSkipped As String

Sub ConvertToXlsx()
FilesAttempted = 0
FilesConverted = 0
FilesDeleted = 0
FilesSkipped = ""
Dim FileSystem As Object
Set FileSystem = CreateObject("Scripting.FileSystemObject")

Dim HostFolder As String
Dim DeleteOriginal As Boolean
Dim RemovePersonal As Boolean

''' HostFolder is directory to start at.  Change to your base directory.
HostFolder = "C:\temp"

''' DeleteOriginal will delete the original file as long as the xlsx was created.  Either True or False
DeleteOriginal = False

DoFolder FileSystem.GetFolder(HostFolder), DeleteOriginal

MsgBox "Conversion complete! " & vbCrLf & vbCrLf & "Files attempted: " & FilesAttempted & vbCrLf & "Files converted: " & FilesConverted & vbCrLf & "Files deleted: " & _
    FilesDeleted & vbCrLf & "Files with issues: " & vbCrLf & FilesSkipped, vbOKOnly + vbInformation, "Conversion Complete"
  
End Sub

Sub DoFolder(Folder, DeleteOriginal)
  On Error GoTo ErrHandler:
  Dim SubFolder
  For Each SubFolder In Folder.SubFolders
    DoFolder SubFolder, DeleteOriginal
  Next
  Dim File
  Dim myWorkbook As Workbook
  For Each File In Folder.Files
    ' For each file name sure its a xls and not a temp file
    If ((Right(File, 3) = "xls") And (Right(File, 4) <> "~xls")) Then
      FilesAttempted = FilesAttempted + 1
      ' Open the file
      Set myWorkbook = Workbooks.Open(File)
       
      ' Save as a xlsx and increase our counter
      myWorkbook.SaveAs Filename:=File & "x", FileFormat:=xlOpenXMLWorkbook
      myWorkbook.Close (False)
      FilesConverted = FilesConverted + 1
      
      ' Delete the original if set and the new xlsx exists
      If ((DeleteOriginal = "True") And (FileExists(File & "x"))) Then
        SetAttr File, vbNormal
        Kill File
        FilesDeleted = FilesDeleted + 1
      End If
NextFile:
    End If
  Next
Done:
  Exit Sub
  
ErrHandler:
Debug.Print "Error encountered.  Error number: " & Err.Number & " - Error description: " & Err.Description
  If File <> "" Then
    FilesSkipped = FilesSkipped & File & vbCrLf
    GoTo NextFile:
  End If
    
End Sub

Function FileExists(ByVal FileToTest As String) As Boolean
   FileExists = (Dir(FileToTest) <> "")
End Function

Change the HostFolder to the directory you want to run this on and hit F5 to run. It will open each Excel workbook with a xls extension in that directory, and all sub directories, then save it as a xlsx. If you want it to automatically delete the old xls file change the DeleteOriginal variable to True or just manually delete them after conversion.