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.
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"
(Parameters) further breaks down to
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).