Use MS Excel to blaze through scripting grunt work

This tip details how to use MS Excel to automate parts of the scripting process.

I recently had a project that included converting bazillions of rows from flat text files to Notes Documents. Given the high volume of repetitive junk like 'Dim xyz as String' and 'doc.Firstname = FIRSTNAME'... I began using Microsoft Excel to compose code then copy and pasted to the Designer IDE. Using Excel formula language to concatenate strings together and then "Autofilling" the formulas down a column saved me hours of tedious writing and clicking...

Here's a simple example: In an Excel spreadsheet list Variable in Column A and field names from Notes forms in Column B.

Column A

Column B

Now in column C, create a simple formula like this:
' ="Dim "&A1&" as String" '  
Autofill this formula down the column to create all of your lines"
Dim FIRSTNAME as String
Dim LASTNAME as String 

A formula in column D:
'  ="doc."&a1&" = "&B1  '
will Autofill down to give you these:
doc.FirstName = FIRSTNAME
doc.LastName = LASTNAME

Copy and paste these columns to the Designer IDE.  Very easy and very effective!  I now use this trick all the time...  Most often to propogate lines that use multiple string handling functions like this:
VARIABLENAME = Trim(Mid$(VARx(250,15))

Dig Deeper on Lotus Notes Domino Administration Tools

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.




  • iSeries tutorials's tutorials provide in-depth information on the iSeries. Our iSeries tutorials address areas you need to know about...

  • V6R1 upgrade planning checklist

    When upgrading to V6R1, make sure your software will be supported, your programs will function and the correct PTFs have been ...

  • Connecting multiple iSeries systems through DDM

    Working with databases over multiple iSeries systems can be simple when remotely connecting logical partitions with distributed ...