Referencing "used" form fields in Word

[Sunjinsak]

Hello all…
<o></o>
Firstly, this isn’t strictly speaking an Excel question. It does involve Excel but if you read on you’ll see that the code/solution I’m after has to be in a Word document – the Excel side of things I can handle. Thought I’d post it here anyway though as this is the most helpful community I’ve come across with regard to Excel/Office and VBA! That said; mods please accept my apologies if you don’t think this is an appropriate place for this question and feel free to move/delete. Thanks.
<o></o>
<o>Using Office 2003 on Windows XP
</o>
We have a spreadsheet for individuals to record certain information on. This info has to be collated and reported at the end of each month on a report form which is a Word document.
<o></o>
I’ve written code to take the entries from the spreadsheet and put them in the Word doc using form fields with bookmark names.
<o></o>
The code looks a little like this (just for example)…
<o></o>

[FONT=Arial][SIZE=3][COLOR=#000000]Set ReportDoc = wdApp.Documents.Add("C:\Path\To\Document\ReportDoc.doc")[/COLOR][/SIZE][/FONT]
[SIZE=3][COLOR=#000000][FONT=Arial]   With ReportDoc.Bookmarks[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Arial]   .Item("Name").Range = NameVariableGoesHere[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Arial]   .Item("MonthEnd").Range = MonthEndVariableGoesHere[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Arial]   .[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Arial]   .[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Arial]   .[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Arial]   etc[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Arial]   End With[/FONT][/COLOR][/SIZE]

<o></o>
So as you can see I have named form fields in the Word doc where specific entries are made. The problem is, once those entries have been made the form field disappears so can no longer be referenced.
<o></o>
The report forms are e-mailed off and then used to complete a master spreadsheet by one individual in a different office. I’ve been asked by that individual to automate that process as well. So I’m basically looking to reverse the process of importing the info into the Word doc. The idea being that there will be a new button on the Word doc that will call a sub to do the dirty work.
<o></o>
I have no problem at all referencing the master spreadsheet and writing code to put the info where it needs to go etc… the problem I have got though is actually referencing the info in the Word doc in the first place as the form fields disappear as soon as they’re populated with the initial import of info from the first spreadsheet.
<o></o>
Anyone know how I can get around this problem?
<o></o>
I hope I’ve explained it enough but feel free to fire a few question marks at me if you need more info/clarification.
<o></o>
Just one thing to add – I can’t “cut out the middleman” by referencing or linking to the master spreadsheet direct from our spreadsheets as we have no shared network storage between offices, that’s why the info has to be transposed to a Word doc and sent via e-mail to be entered on the master sheet at another site.
<o></o>
Thanks in advance.

 

[Sunjinsak]

Anyone?

If there's no way to keep the form fields after the intial import of data from Excel to the Word doc, is there anything I can replace the form fields with that'll perform the same function but remain after the import so I can reference them again when exporting the data?

 

[John_w]

[FONT=Arial][SIZE=3][COLOR=#000000]Set ReportDoc = wdApp.Documents.Add("C:\Path\To\Document\ReportDoc.doc")[/COLOR][/SIZE][/FONT]
[SIZE=3][COLOR=#000000][FONT=Arial]   With ReportDoc.Bookmarks[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Arial]   .Item("Name").Range = NameVariableGoesHere[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Arial]   .Item("MonthEnd").Range = MonthEndVariableGoesHere[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Arial]   .[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Arial]   .[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Arial]   .[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Arial]   etc[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Arial]   End With[/FONT][/COLOR][/SIZE]

<o></o>
So as you can see I have named form fields in the Word doc where specific entries are made. The problem is, once those entries have been made the form field disappears so can no longer be referenced.

I tried to reproduce the problem, but couldn't - the bookmarks (form fields) are retained in the active document and in the saved document. So this works for me (I put the code in an Excel module, but I'm not sure where your code is meant to be).

Set ReportDoc = wdApp.Documents.Add("C:\Path\To\Document\ReportDoc.doc")
    With reportDoc.Bookmarks
        .Item("Name").Range = "ABCD"
       .Item("MonthEnd").Range = "11"

'Reference the same bookmarks
        .Item("Name").Range = "1234"
       .Item("MonthEnd").Range = "12"
    End With

I saved the Word document and ran the above code successfully again, showing that the bookmarks still exist in the document.

 

[Sunjinsak]

Weird – doesn’t work for me.
<o> </o>
Although there are a couple of differences that may account for that…
<o> </o>
Firstly, the code to grab the data from the spreadsheet and then place it in the Word doc is in a module in Excel, whereas the code to export the data in the Word doc to another Excel sheet is (or will be, if I can get it to work) in a module in the Word doc. It has to be this way as the Word doc will be shipped off via e-mail to another office where the export will take place.
<o> </o>
Secondly, and I’m really not sure if this’ll make a difference or not, is that in your code example you have the values hard coded, whereas I’m reading the values in off the spreadsheet and performing some string manipulation on them (or at least most of them). Therefore I have to store the values as variables.
<o> </o>
It’d be so much easier to explain if I could just post either all of the code or even the spreadsheet/Word docs themselves but unfortunately I can’t for confidentiality reasons - even redacting them is out of the question.
<o> </o>
Thanks for getting back to me anyway – and sorry for the late reply but I’ve not been in work for a few days.