Can I make Word prompt for a file? 1

[EdwardMartinIII]

I'm using Word 2007 and Excel 2007.

We write a lot of reports. A lot.

For all of them, there are bits of text that are common, such as "company info," etc.

For all of them, some of their information comes from places in a spreadsheet, which is a custom spreadsheet developed for each client.

So...

Right now, I finally have an approved report template, from which I've made a Word Template.

In my ideal world, I could open a page from this Master Template, and it would be a small skeleton. That skeleton would:

1. populate itself where specified using external docs (such as an external doc of the "About our Company" information).
2. prompt for the location of that Client's Spreadsheet (which could be named anything and saved anywhere on the network).
3. continue populating its fields using data from that spreadsheet.

Now, in Word 2007, I'm pretty sure #1 is possible in a couple of different ways.

I'm ALSO pretty sure #3 is possible, as I've been able to transfer spreadsheet data to a Word doc by-reference before. But I can't count on my report writers to always place the spreadsheet as the same name in the same network location.

I have NO idea (yet) if I can do #2 on the above list.

Is this possible in Word 2007?

Thanks!


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[kjv1611]

The only one I think will give you any difficulty is:

3. continue populating its fields using data from that spreadsheet.

The reason being is this: how sure are you that you can count on the 3rd parties in always using the same format themselves, much less between all of them.

So, what might could be done is to have someone first manually verify the file, and make any format changes (column locations, formats, etc) before grabbing the data.

So you could prompt for the location, and then have Excel open the file directly, have the user do their work in it, and then perhaps have them save and close the Excel workbook, come back to Word and hit some button to finish up where they left off.

 

[kjv1611]

Oh, and I wonder... could you not simply use "mail merge" for some of this, without even going the code route? If it's a static template, and has static data sources, at least where the file names, and content layout do not change, but rather only the content, then I'd imagine a merge between the docs would work... at least I believe that's possible in Word; just never had to do it myself.

 

[EdwardMartinIII]

how sure are you that you can count on the 3rd parties in always using the same format themselves, much less between all of them."

Pretty sure -- I have the master Excel spreadsheet in a network location. They can open it, but they can't write to it (well, that's the plan, anyway).

They (our consultants) use the spreadsheet to check a lot of things about the Client's location (think of it as a big house inspection). Then, they save THEIR copy of the spreadsheet to the Client directory.

So, the Excel spreadsheet will be in different places in the directory, and it could have any name, but they are under STRICT orders to not monkey with the contents -- only to work things through the supplied controls*.

Does that make more sense?

-=-=-=-
* And I'm pretty sure I can lock everything but the comment fields, too.



Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[EdwardMartinIII]

It's POSSIBLE I can try to enforce them saving it under the same name each time, but that introduces a real problem later, when we have a hundred clients and all their Excel result files have the exact same name.

That would shatter our minds.

The items for Step #1 above are relatively static, which means I'll probably make changes to them every couple of months.


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[kjv1611]

1. populate itself where specified using external docs (such as an external doc of the "About our Company" information).
Merge Documents with Word 2007
Merge Documents with Word 2007 (VBA)

2. prompt for the location of that Client's Spreadsheet (which could be named anything and saved anywhere on the network).
Use the FileDialog in VBA within Word

3. continue populating its fields using data from that spreadsheet.
Using Word and Excel to create a Form Letter

I could have type up some of the details myself, but I think in this case the references will give you far more than I could just type up in a jiffy.

My thoughts are that you'll be best served using VBA, build into the Word template(s).

Then in the code, you could add a progress meter if you wanted, but that'll be more advanced than the rest, I guess. And/or make sure you put some sort of message box at the end, saying, "Yay! Finished!" or whatever.

 

[kjv1611]

Oh, and if you end up for sure going with VBA, and need some help along the way, be sure to post in the VBA forum on the specifics:
forum707

 

[EdwardMartinIII]

Groovy!

Lemme see if I can muddle through this...

Thanks!


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door