Macros & VB to create various letters

[heatherc3]

Hello - I'm trying to work with Microsoft's VB editor to create a series of macros. Information will be inputted into word using mail merge, and I think we'll be seeing it in comma-separated values. It'll be dumped from a Peoplesoft application into Word.

The code I'm writing needs to look through the variables in word and then replace them with text strings - these would be letters for students at a post-secondary institution.

I'm pretty new to VB, so I'd appreciate any help or advice!

- should I be using If...Then..Else statements? The macros need to be able to decide what kind of letter to send to a student (ie: general response to application, or admission or conditional admission letter OR general information letter)and then generate paragraphs of text for them.

- is it possible to execute perl inside of Word?

Thanks again!! <img>

[Comatose]

Ok, I got lost when you asked about converting variables to text. No, the chances of getting perl to work in word are real slim. It's easy enough to read the csv file, and work with it. It's definately easy enough to find out what kind of letter is required, and make it generate text based on that.... I need you to lay out for me though, in greater detail, (more like a step by step outline: program needs to first read the file, second get the student name, third, etc, etc), and I can help you more.

[heatherc3]

Ok, I got lost when you asked about converting variables to text. No, the chances of getting perl to work in word are real slim. It's easy enough to read the csv file, and work with it. It's definately easy enough to find out what kind of letter is required, and make it generate text based on that.... I need you to lay out for me though, in greater detail, (more like a step by step outline: program needs to first read the file, second get the student name, third, etc, etc), and I can help you more.

Sorry - variables is what we're calling it internally. Forgot that means something in VB, too.

Step by step, what the VB needs to:

- format name, address, and ID number
- salutation (Dear Firstname Lastname,
- determine if student is admitted or if acknowledging application for admission or if a general inforamtion letter
- if general information letter, print letter.
- if acknowledging application, print letter
- if student is admitted, then
- determine if early decision, and if so, generate letter
- if student's average is 80%+, generate blurb about scholarship
- determine if final offer and if so, generate letter.
- if student's average is 80%+, generate blurb about scholarship

What I'm doing right now is recording a basic macro that does a search and replace. After I've got my basic ones done, I'll start editing them in the VB editor to build the logic in (if this, then this kind of thing..)

From what I can tell, this is basically a giant search and replace job, with a bit of logic built in to make it a bit smarter.

Thanks again for your help!
Heather

[Comatose]

I still need the layout of the CSV file, and how it will be in the file. Where do I get the name address and ID number? Salutation is simple enough, assuming we can figure out part 1. How do we determine if they are admitted? Do we have admitted file that we can test the student against to see if they are admitted already? The rest of it is based on the rest of the information that we don't have yet.... so, it all can be done, assuming the rest of it gets layed out. It sounds like you have a pretty good idea what you are doing (recording macro's and going to add the logic in), so, what do you need from me?

Can this be done as an EXE from VB? Does this have to be a macro in the file? Can it be a WSH Script?

[heatherc3]

Problem is that I don't really know how things will look...we're in the beginning of a huge software implementation, so I'm drawing up a mock up that will be edited again when we know the actual format. I'm told that it'll just put a bunch of data into csv (I'm assuming we'll use Excel to look at the file) and then the mail merge will use that, and the macros will translate it after that.

Assuming we get a bunch of data, like

name=lastname,firstname
id=123456
condAdmit=yes
ave=82

name=lastname,firstname
id=987456
condAdmit=no
ave=n/a (or blank)

do I need to use 'if...then' statement? I'm mostly stuck on how to build in the logic so that it can decide if a student should get one kind of a letter over another. VBA is so new to me - I'm working on this with a textbook beside me.

Do you think I'm going about this the right way?

[heatherc3]

Can this be done as an EXE from VB? Does this have to be a macro in the file? Can it be a WSH Script?

I was told it needed to be a macro within Word.

[Comatose]

Well, if it's a coma delimited, then it won't be in the format of:

name=lastname,firstname
id=123456
condAdmit=yes
ave=82

name=lastname,firstname
id=987456
condAdmit=no
ave=n/a (or blank)

instead it would look more like: lastname,firstname,123456,yes,82 You probably will need more than one if else, and maybe even something more dramatic. I don't have office installed, so it's hard for me to test macro's, and figure out what commands and keywords will work, and which ones won't. I use VB6, which is a full fledged programming language.... VBA is not. It is possible to use WSH (or VB6) to directly work with the data, and even handle all of the word document, without the need for ever even loading word. I could make a WSH file (or a VB program) that does everything you specified above, without ever having to load word from the user interface (the program or script can create an instance of word, and do everything behind the scenes). What I can do, is give you some code, and a layout of how to work with it, and you can figure out if it works in VBA inside of word or not.

Some code that would be certainly required, would be to split apart the information by coma, and put each piece of that information into variables. Then you test each piece against whatever criteria you need to, and go from there. Some of the code would look like this:

dim Parts() as string
dim LastName as string
dim FirstName as string
dim ID as string
dim condAdmit as string
dim ave as string

Parts = split(data_from_file, ",")
LastName = Parts(0)
FirstName = Parts(1)
ID = Parts(2)
condAdmit = Parts(3)
ave = Parts(4)

if condAdmit = "Yes" then
     msgbox "Do Word Stuff For Conditional Admit"
end if

[heatherc3]

That's very helpful...thank you very much!

[heatherc3]

Another question - doesn't this need a range to be set?

[Comatose]

A Range, for what?

[heatherc3]

Code is starting to come together and work...I'll post it when it's running. Whee!