Word -> Excel -> Word -> Excel

Hi All,

Apologies, I have not written any VBA code for a long time so not really sure where to start with this one...

I am trying to set up a system where a user will be using Word. When they click a button (not yet created), Word will find the next form number from Excel (or some other method), complete the relevant field and then send all of the form data to Excel.

"Master form.docx" is the file that the user will have open. Once the programming has been sorted, I will be saving this as a macro enabled template so that the form cannot be modified. Once the user has filled out all of the fields shown (with the exception of the Formnumber field) they will submit the form. This will make Word look up the next form number available from the excel file (copy attached). The form number will be made up of the year and a sequential number. The Formnumber field will then be completed and all of the relevant information then passed back to excel to store in the appropriate sheet. The form should then be saved with the form fields locked so they cannot be changed

There will be a new sheet for each year (2018 is shown because I would like to introduce this from 2018) with the same information across the top. The information from the form will need to be saved in the appropriate sheet (2018 for the forms created in 2018, 2019 for the forms created in 2019 etc)..

I would like to keep the ActiveX controls for the form as individuals will need to add text to the form going forward (in the yellow shaded part) and this function is not available if using the legacy text form fields.

Any ideas on how I can achieve the above?

I have seen one method where information was passed from word to excel via a "lockfile" (a txt file that had the information) but this used the legacy text form fields.

Thank you for any help you can give.

Your document contains no ActiveX controls that I can see, only content controls.

You say you want the 'yellow shaded part' to remain available 'to add text to the form going forward'. This can be done when using formfields, either by having formfields there or by inserting a Section break before that part and leaving that Section unprotected when applying the 'filling in forms' protection. You would have the same issue if you used 'filling in forms' protection with content controls and, if you don't use some form of editing restriction, there'll be nothing to stop users unlocking & editing (even deleting) the content controls after you've locked them.

As for logging, I'd suggest using a CSV file. Word can access and update those as plain text files, without any of the Excel overhead, and Excel can read & update them too.