Quick followup on the previous build script posts. In short the following will write host specific config files based on spreadsheet data, writing to files based on hostname in column 1 – in the following example “xs62cn1.cfg”.  

So, in short, a spreadsheet with the following entries:

CFG_HOSTNAME CFG_MANAGEMENTIP CFG_MANAGEMENTNETMASK
xs62cn1.mylab.lcoal 192.168.0.30 255.255.255.0

will result in a new config file xs62cn1.cfg with the following entries:

CFG_HOSTNAME="xs62cn1.mylab.local";
CFG_MANAGEMENTIP="192.168.0.30";
CFG_MANAGEMENTNETMASK="255.255.255.0";

VBA script:

Sub CreateHostConfigFiles()
'Exports spreadsheet data to config files

Dim intCol, intRow, arrHeaders(), intMaxcols, strOutputbuffer, objFSO, strFilename, arrFilename, objFile
    
    intRow = 2
    intCol = 1
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    'Find headings
    ReDim arrHeaders(0)
    While Cells(1, intCol) <> ""
        ReDim Preserve arrHeaders(UBound(arrHeaders) + 1)
        arrHeaders(intCol - 1) = Cells(1, intCol)
        intCol = intCol + 1
    Wend
    intCol = intCol - 1
    intMaxcols = intCol
    
    'Write config
    While Cells(intRow, 1) <> ""
        strFilename = Left(Cells(intRow, 1), InStr(Cells(intRow, 1), ".") - 1) & ".cfg"
        Set objFile = objFSO.CreateTextFile(strFilename, True)
        For intCol = 1 To intMaxcols
            strOutputbuffer = strOutputbuffer & arrHeaders(intCol - 1) & "=" & Chr(34) & Cells(intRow, intCol) & Chr(34) & ";" & Chr(10)
        Next
        objFile.write strOutputbuffer
        objFile.Close
        strOutputbuffer = ""
        
        intRow = intRow + 1
    Wend
    
MsgBox "All done"
   
End Sub

Posted by Dag

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s