We are currently in the throws of creating a VOIP system. One of the things we would like to do is auto-provision the handsets. We have the base config files working which is fine and where i hit a snag.
I have a CSV which has the relevant information which needs to be inserted into each file.
I have a field in the spreadsheet which contains all mac addresses, what i need is a config file with the MacAddress as the name but containing the information from the other fields in the spreadsheet.
If not just the file names created so macaddress.cfg for each address and I can just create a mail-merge and paste the information in which i would rather not!
Examples of the config and CSV linked
Anybody any idea where to start?
If I've understood, I had this idea: a cfg file is just a text file, so why not use a bit of VBA code to loop through your CSV of phone examples, subsituting it into your Mac Address template and saving it as a one column CSV (except with a cfg file extension)?
I've drawn up some VBA code you might use (apologies if you don't like my verbose coding style) that might work: in the workbook the Template worksheet contains your Mac Address template file (opened as a CSV in Excel - this takes up column A in the worksheet) and the CSV worksheet contains the data from your phone example.
N.B. There is little proper error-handling in the code (e.g. I don't check the worksheets exist or whether the files to be saved already exist).
I don't seem to be able to upload files, so here's the code:
HTH.Code:Option Explicit ' The template worksheet contains the template of the config file ' The CSV worksheet contains the details to be embedded into the template ' and then individually saved. ' Change the values here depending upon how you have named your worksheets Private Const strTEMPLATE_WORKSHEET As String = "Template" Private Const strCSV_WORKSHEET As String = "CSV" ' Layout of the template worksheet ' (by default this should only have one column of data) Private Const lngTEMPLATE_HEADING_ROW As Long = 1 Private Const lngTEMPLATE_FIRST_DATA_ROW As Long = 2 Private Const lngTEMPLATE_DATA_COLUMN As Long = 1 ' Layout of the CSV worksheet Private Const lngCSV_HEADING_ROW As Long = 1 Private Const lngCSV_FIRST_DATA_ROW As Long = 2 Private Const lngCSV_MAC_COLUMN As Long = 1 Private Const lngCSV_EXTENSION_COLUMN As Long = 2 Private Const lngCSV_ID_COLUMN As Long = 3 Public Sub CreateMacAddressCfgFile() On Error GoTo ErrorHandler Const strROUTINE_NAME As String = "CreateMacAddressCfgFile" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Name : Public Sub CreateMacAddressCfgFile ' ' Description : Create text files with a suitable name from a ' "template" worksheet filling in the data from a ' CSV of data. ' ' The text file will be saved from Excel as a CSV ' but with a cfg file extension. ' ' Created by : Pico (EduGeek) ' Date : 02/03/2012 ' ' Last modified by : ' Date : ' ' Changes made : ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Dim wkshTemplateWorksheet As Worksheet Dim wkshCSVWorksheet As Worksheet Dim strCurrentFilePath As String Dim wkbkConfigWorkbook As Workbook Dim strMacAddress As String Dim strExtension As String Dim strID As String Dim lngEachMacAddress As Long Dim lngNumberOfMacAddresses As Long ' The workbook this code has been embedded in With ThisWorkbook ' N.B. I haven't checked these worksheets exist! Set wkshTemplateWorksheet = .Worksheets(strTEMPLATE_WORKSHEET) Set wkshCSVWorksheet = .Worksheets(strCSV_WORKSHEET) strCurrentFilePath = GetPathToFile(.FullName) End With With wkshCSVWorksheet.Cells(lngCSV_HEADING_ROW, lngCSV_MAC_COLUMN) lngNumberOfMacAddresses = .CurrentRegion.Rows.Count End With ' Loop through all rows in the CSV worksheet For lngEachMacAddress = lngCSV_FIRST_DATA_ROW To lngNumberOfMacAddresses Step 1 ' Get the data With wkshCSVWorksheet strMacAddress = CStr(.Cells(lngEachMacAddress, lngCSV_MAC_COLUMN).Value) strExtension = CStr(.Cells(lngEachMacAddress, lngCSV_EXTENSION_COLUMN).Value) strID = CStr(.Cells(lngEachMacAddress, lngCSV_ID_COLUMN).Value) End With ' Copy the template worksheet to a new workbook (becomes the active workbook) ' Save the new workbook as a CSV (text file) but with a cfg file ' N.B. You will be prompted whether to overwrite if the file already exists on disk wkshTemplateWorksheet.Copy Set wkbkConfigWorkbook = ActiveWorkbook wkbkConfigWorkbook.SaveAs Filename:=strCurrentFilePath & strMacAddress & ".cfg", FileFormat:=xlCSV ' Substitute in the data (add to this list as necessary) With wkbkConfigWorkbook.Worksheets(1) .Cells(4, lngTEMPLATE_DATA_COLUMN).Value = "Label = " & strID .Cells(5, lngTEMPLATE_DATA_COLUMN).Value = "DisplayName = " & strID .Cells(6, lngTEMPLATE_DATA_COLUMN).Value = "AuthName = " & strExtension .Cells(7, lngTEMPLATE_DATA_COLUMN).Value = "UserName = " & strExtension End With ' Save and close With wkbkConfigWorkbook .Save .Close SaveChanges:=True End With Set wkbkConfigWorkbook = Nothing Next lngEachMacAddress CleanUpAndExit: If Not wkbkConfigWorkbook Is Nothing Then Set wkbkConfigWorkbook = Nothing End If If Not wkshTemplateWorksheet Is Nothing Then Set wkshTemplateWorksheet = Nothing End If If Not wkshCSVWorksheet Is Nothing Then Set wkshCSVWorksheet = Nothing End If Exit Sub ErrorHandler: Call MsgBox("An error was encountered in " & strROUTINE_NAME & "." & _ vbCrLf & vbCrLf & _ "Error Number: " & CStr(Err.Number) & vbCrLf & _ "Error Description: " & Err.Description, _ vbCritical + vbOKOnly, "Error Message") Resume CleanUpAndExit End Sub Private Function GetPathToFile(ByVal strFullFilePath As String) As String Dim strPathArray() As String Dim lngLastPosition As Long Dim strFilePath As String If strFullFilePath = "" Then strFilePath = "" Else strPathArray = Split(strFullFilePath, Application.PathSeparator) lngLastPosition = UBound(strPathArray) ReDim Preserve strPathArray(lngLastPosition - 1) strFilePath = Join(strPathArray, Application.PathSeparator) & Application.PathSeparator End If GetPathToFile = strFilePath End Function
There are currently 1 users browsing this thread. (0 members and 1 guests)