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.