Creating multiple files from CSV

[glennda]

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

 

http://thankyou.theweald.org.uk/phoneexample.csv.txt

http://thankyou.theweald.org.uk/macaddress.cfg.txt

 

Anybody any idea where to start?

[Pico]

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:

 

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

 

HTH.