I'm a newbie PABX tech with a hobby for programming so when I learnt most of the telephone system programming can be completed with scripts I got curious.
With a bit of trial and error to work out the seemingly undocumented syntax of the script I decided once I'd cracked it, to create a spreadsheet where the user enters a list of names in an excel sheet (in column B from row 2) and then clicks a CommandButton to create a script to easily input the name list for the telephone extensions into the system.
I've chosen VBA as my preferred language as
- I'm most competent in VBA, and
- It's common to copy/paste a list from excel already (it's just slightly more tedious that way in my oppinion)
I've not had much experience writing to external files (other than other office applications) so I'm looking to see if there are any more efficient ways to achieve this than the way I have with FSOs.
General housekeeping is welcomed too, so any improvements in the way it's written; order of the code, efficiencies elsewhere etc.
The comments are aimed at someone who has very little VBA experience as as far as I know I'm the only person familiar with it in the office.
Public Sub WriteNamesInRangeToPCSFile()
'Description of operations:
'----------------------------------------------------------------------------------------------------------
'
'First the sub finds the last row of column B.
'Then the range containing the extensions and names is created using these variables.
'
'A file system object is created and a new .pcs file is created (represented by variable 'objScriptFile'
'The file path for the .pcs file is defined by the user on Sheet1 in cell C1
'
'The range is put into an array as this is more efficient than reading directly from each cell in the range.
'The output string is built by concatanating itself with each array element contatining a name.
' Each iteration has a carraige return/line feed (chr(9)) at the end of the string so it's written on a new line
'
'The OutputText string is then written to the .pcs file.
'
'==========================================================================================================
'------------ Set variables for our range and dynamically define the range of ext and names --------------=
'==========================================================================================================
Dim PopulatedRangeOfNamesAndExtensions As Range
Dim LastRow As Long
With ThisWorkbook.Sheets("Sheet1")
LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
If LastRow = 1 Then
MsgBox "Please enter at least 1 extension AND name!", vbCritical + vbOKOnly, "No Extension And Name"
Exit Sub
Else
Set PopulatedRangeOfNamesAndExtensions = .Range(Cells(2, "B"), Cells(LastRow, "B"))
End If
End With
'==========================================================================================================
'------------ Create scripting file system object and create .pcs file to user defined path --------------=
'==========================================================================================================
Dim objFSO As Object
Dim objScriptFile As Object
Dim UDFilePath As String
UDFilePath = ThisWorkbook.Sheets("Sheet1").Range("E3").Value
If UDFilePath = "" Then
MsgBox "Please enter a file path in cell E3 to save the script file to.", vbInformation, "Save Location Required"
ThisWorkbook.Sheets("Sheet1").Range("E3").Select
Exit Sub
ElseIf Not Right(UDFilePath, 1) = "\" Then
UDFilePath = UDFilePath & "\" 'Error check to ensure back slash is last character
End If
Set objFSO = CreateObject("Scripting.FileSystemObject")
On Error GoTo PathNotFound
Set objScriptFile = objFSO.CreateTextFile(UDFilePath & "NEC_15-01_Names_Script.pcs", 2)
On Error GoTo 0
'==========================================================================================================
'------------ Build our output string by dumping the data to an array and looping the array --------------=
'==========================================================================================================
Dim OutputText As String
Dim ArrayElementCounter As Long
Dim ArrayForRange As Variant
ArrayForRange = PopulatedRangeOfNamesAndExtensions
For ArrayElementCounter = 0 To (UBound(ArrayForRange) - 1)
If Not ArrayForRange(ArrayElementCounter + 1, 1) = Empty Then 'counter + 1 because counter is zero based and array is 1 based
OutputText = OutputText & "SET" & vbTab & "15-01" & vbTab & "(" & ArrayElementCounter & ",0,00)" & vbTab & vbDoubleQuote & ArrayForRange(ArrayElementCounter + 1, 1) & vbDoubleQuote & vbCrLf
End If
Next ArrayElementCounter
'Write the built output string to the newly created .pcs file.
objScriptFile.Write (OutputText)
Exit Sub 'Exit before error handler is run.
PathNotFound: 'Error handler if non valid file path is used (such as non existent path)
If Err.Number = 76 Then
MsgBox "Run time error (76) has occured." & vbNewLine & vbNewLine & _
"The following path does not exist or is not in a valid format:" & vbNewLine & _
vbDoubleQuote & UDFilePath & vbDoubleQuote & vbNewLine & vbNewLine & _
"Please check the path in cell E3 and try again.", _
vbCritical + vbOKOnly, "Invalid File Path"
Else 'Raise normal error if not due to invalid file path
Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
End If
End Sub
The scripts are tab-spaced with the syntax:
SET <Memory-Block> (parameters) "Value"
Where (Parameters) further breaks down to (<Row>,<Column>,<Item>)
It should be noted that the parameters are zero-based - i.e the first row, column and item is 0 (though in the system it's shown in the GUI as 1 just to make things confusing).
Here are some example screenshots of source data and the output file:
Source data on sheet:
Output file:
For bonus points here is a snip of the system after running the output script file:
