I
ilia
I have an issue that I have been solving programmatically, but now I
have to show someone else how to do it, and a VBA crash course is not
an option.
I have a letter form in a Word file that indicates to new hires which
trainings they have not taken. It basically goes like this:
"Dear <Name>,
According to our records, you have not taken the following trainings:
* Training 1
* Training 2
* Training 3
Thanks."
The training department has a spreadsheet in Excel that consists of a
column for names and a column for each training. Trainings taken
contain the date of training. Training not taken is left blank. Each
person has one row. Obviously, some people may be missing only 1 or 2
trainings, whereas others might be missing as many as 10 or 15. Some
people may not get a letter at all, and for those who do, the number
of bullets will vary from record to record. A separate letter for
each missing training is also not an option, because we'll destroy a
rainforest in a few months doing that.
Name Training 1 Training 2 Training 3 Training 4
John Doe 12/15/07 12/18/07 <blank> 1/3/08
John Doe is missing Training 3 - that column contains a blank (zero-
length).
With VBA, what I did is manually inserted two bookmarks into the .doc
file containing letter text - one for name, one for first bullet
called TrainingList - and had Excel go through each record and create
a letter for it, inserting as many bullets as needed at the
TrainingList bookmark.
What I need to happen, ideally, is either have Word fetch the next
column and check whether it is blank (doesn't seem possible without
VBA) or rearrange the source data in some way that allows a NEXTIF
kind of logic. In the end, I need a casual Word/Excel user to be able
to do this. If there isn't a built-in way for Word to handle such a
task (and someone more Word-savvy can confirm this), I'll just write
an add-in they can use. However if any Word guru out there can think
of a better way, please post back.
We are using Word 2003/Excel 2003 for this task.
Thanks for your help.
have to show someone else how to do it, and a VBA crash course is not
an option.
I have a letter form in a Word file that indicates to new hires which
trainings they have not taken. It basically goes like this:
"Dear <Name>,
According to our records, you have not taken the following trainings:
* Training 1
* Training 2
* Training 3
Thanks."
The training department has a spreadsheet in Excel that consists of a
column for names and a column for each training. Trainings taken
contain the date of training. Training not taken is left blank. Each
person has one row. Obviously, some people may be missing only 1 or 2
trainings, whereas others might be missing as many as 10 or 15. Some
people may not get a letter at all, and for those who do, the number
of bullets will vary from record to record. A separate letter for
each missing training is also not an option, because we'll destroy a
rainforest in a few months doing that.
Name Training 1 Training 2 Training 3 Training 4
John Doe 12/15/07 12/18/07 <blank> 1/3/08
John Doe is missing Training 3 - that column contains a blank (zero-
length).
With VBA, what I did is manually inserted two bookmarks into the .doc
file containing letter text - one for name, one for first bullet
called TrainingList - and had Excel go through each record and create
a letter for it, inserting as many bullets as needed at the
TrainingList bookmark.
What I need to happen, ideally, is either have Word fetch the next
column and check whether it is blank (doesn't seem possible without
VBA) or rearrange the source data in some way that allows a NEXTIF
kind of logic. In the end, I need a casual Word/Excel user to be able
to do this. If there isn't a built-in way for Word to handle such a
task (and someone more Word-savvy can confirm this), I'll just write
an add-in they can use. However if any Word guru out there can think
of a better way, please post back.
We are using Word 2003/Excel 2003 for this task.
Thanks for your help.