VBA script to substitute text from an excel sheet into various word docs by filepath

Hi,

I'd be grateful for some advice on the following VBA requirement I have, which I imagine has been created in one form or another but I've not been able to track it down.

The company I work for has a word doc template for an inspection report. The user has to edit each report and fill in the data about a property. It would be useful to edit the data in a centralised work sheet, and then have this placed in the respective word documents for each property using either a substitution approach. I don't think mail merge is feasible as the company template is very and formatted with corporate branding etc.

Is it possible to create a VBA that, when you tell it the filepath of a word document, can substitute a string for another desired string contained in the excel sheet?

I've attached a zip folder which contains what I am looking to achieve.

Thank you

Tom

Hi David,

Thanks for the code. It works great but wasn't quite what I was looking for - I don't think I was being clear.

My company doesn't use templates so I'm more looking for something that will do a substitution of existing word docs based on the items in the square brackets only, and not something that creates new fresh word docs.

The target word file is just a place for me to refer to the word doc wherever it is sitting on the server, i.e. I don't need the script to make up filenames etc based on the property name as I can deal with this plus the folder path will be a bit irregular.

I've reattached before and after spreadsheet and word doc which hopefully makes it a bit clearer what I am looking for.

Thank you

Tom

Originally Posted by Tinbendr

Never too late to start.


Ok. After you replace the square brackets (or any string) with the data, how will you find that data again? That was the point of using the template. Every time you create a new document, the brackets will be waiting to be replaced.

We could use bookmarks, but those are too easy to delete by users.

We could use formfields, but you have to protect the document in order for the fields to work correctly.

Maybe content controls as you can set a 'not delete' property for those.

Wait, are you just trying to grab the data from an already completed file? If so, you might be able to search for the title and grab the next line down.

Ok, I think I have a solution for this.

Thanks for the response.

My intention was to do my working on the excel file and then once I’m happy I execute that macro which does a find and replace - finding the square bracketed stuff and replacing it with the stuff in the cells. That means, if I make subsequent changes to the excel after I’ve ran the find and replace macro, I understand that these won’t then feed into the word document. I.e., this is a one time operation.

Bumping this thread! I'm looking to do exactly this as well. I wonder if OP ever came to a solution? Would be a huuuuge help!

Thanks