Batch Editing a .txt with values from a table

Question

I've been tasked with distributing new e-mail signature blocks to every employee at my CO. I decided rather than send everybody instructions on how to change a few lines of html, I would just do it; so I sent out an excel table with some information for them to fill out (name, position, cell #). I have a template .txt file with all the correct formatting for the final signature. My question is what the best approach would be to batch replace the appropriate phrases in my template with the personal information in the table and save a new .htm file every time.

The template says things like "Your Name Here" and "Your Position Here" so I figure there has to be a way to replace each of those iteratively. I'm a scripting dunce, so any help would be greatly appreciated!

"Scripting"? Not sure you have the right tags. Please read [ask] and take the [tour]. The tags include text to provide guidance for which one(s) apply. — Ňɏssa Pøngjǣrdenlarp, Nov 09 '17 at 17:43
Batch files cannot read Excel files. Nor can they interact with any Graphical User Interface program. — Squashman, Nov 09 '17 at 19:51
I don't think the OP meant batch files. I think they just meant to do it automatically in code. — David Wilson, Nov 10 '17 at 07:09

score 0 · Answer 1 · answered Nov 09 '17 at 17:47

Assuming you're using Outlook 2008+, the Signatures I believe is stored in the C:\Users\\AppData\Roaming\Microsoft\Signatures directory as an .htm, .rtf, and .txt.
I believe the easiest way would be to use Visual Studio, write your application to:
1. Iterate the Excel file for the appropriate cell containing the information as a string: Get Cell Value in Excel from Range
2. Simply String.Replace("Your Name Here", cellName) from the .htm https://msdn.microsoft.com/en-us/library/fk49wtc1(v=vs.110).aspx
3. Save the modified .htm to \C$\\Users\\AppData\Roaming\Microsoft\Signatures\

If you're using Visual Studio, it wouldn't be too hard to add the template .HTM with the name changed to "Your Name Here". Then your program can simply stream read the .htm, make the change, and save the new .htm to the computer. Then, just on to the next!

score 0 · Answer 2 · answered Nov 09 '17 at 23:12

First you'll want to look here to get an idea of how to iterate over your spreadsheet to get the cell values from each row.

After that I'd check out this to learn about how to read your template file into a string.

Once that's done a quick String.Replace in a loop will get your text swapped, and this will give you an idea of how to write the text to a new .htm file. Basically you want

Dim template, line As String
Dim sr as StreamReader = new StreamReader("C:\template.htm")
Dim sw as StreamWriter
'Other vars as needed to read the Excel file and keep/stop looping....
'Read all lines from template file into template

while (keepgoing)
    cellName = ws.Cells(rowNum, colNum).Value2.ToString()
    'Grab other values from cells
    If (Not String.IsNullOrEmpty(cellName)
        line = template.Replace("Your Name Here", cellName)
        line = line.Replace("Your Position Here", cellPosition)
        'etc
        sw = new StreamReader("C:\Sig_" & cellName & ".htm")
        sw.Write(line)
        sw.close()
    Else
        keepgoing = false
    End If
End While

Hope it helps.

Batch Editing a .txt with values from a table

2 Answers2