Sunjinsak
Board Regular
- Joined
- Jul 13, 2011
- Messages
- 151
- Office Version
- 365
- 2019
- 2016
- 2013
- Platform
- Windows
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
>
<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.
<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
</o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
The code looks a little like this (just for example)…
<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
Code:
[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]
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
Anyone know how I can get around this problem?
<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
Thanks in advance.