Sunjinsak
Board Regular
- Joined
- Jul 13, 2011
- Messages
- 151
- Office Version
- 365
- 2019
- 2016
- 2013
- Platform
- Windows
Hi all,
I have searched for an answer to this one but can't seem to find anyting that quite matches what I want to accomplish. This is what I want to do if possible:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
ffice
ffice" /><o
></o
>
<o
></o
>
I’m using Excel and Word 2003 SP3 on Win XP Professional<o
></o
>
<o
></o
>
I have a workbook that records time spent on various different activities each month. The time falls into 4 categories – cat.1, cat.2, Training and H&S and the monthly totals for these are calculated in cells E31, E32, E33 and E34 respectively. Each month is on a separate sheet within the workbook.<o
></o
>
<o
></o
>
The time is in hh:mm format.<o
></o
>
<o
></o
>
At the end of each month I have to submit a report for the total time spent in each category. The thing is this report is on a protected Word document. The only areas of the Word document available for editing are the text fields for the respective categories and the responsible manager’s name. At the moment I have to manually enter the values from the spreadsheet on the Word document.<o
></o
>
<o
></o
>
I would like, if it is possible, to have the report automatically generated by a macro attached to a button on the spreadsheet.<o
></o
>
<o
></o
>
The steps I think I need to accomplish when the button is clicked are as follows:<o
></o
>
<o
></o
>
1) Pull the data from cells E31 etc into (string?) variables - I have no problem doing this bit
<o
></o
>
<o
></o
>2) Ask the user to input the responsible manager’s name (stored in a string variable) - once again no problem
<o
></o
>
<o
></o
>3) Open the word document (located at “Z:\Time Accounts\Offtime\MonthlyReport.doc”) and make it visible to the user - this is where I start to waver
<o
></o
>
<o
></o
>4) Navigate to the correct text field for each entry and insert (paste?) the respective values - this I have no clue how to do!<o
></o
>
<o
></o
>
So is what I want to do even possible with VBA? If so how would I go about it? I’m not sure how to get the names or positions of the text fields on the Word document in order to reference them in the code. I could potentially re-create the sheet myself from scratch though if need be.<o
></o
>
<o
></o
>
Any input will be greatly appreciated.<o
></o
>
<o
></o
>
Please let me know if you need any more information or anything clarifying.<o
></o
>
<o
></o
>
Cheers guys.<o
></o
>
I have searched for an answer to this one but can't seem to find anyting that quite matches what I want to accomplish. This is what I want to do if possible:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com




<o


I’m using Excel and Word 2003 SP3 on Win XP Professional<o


<o


I have a workbook that records time spent on various different activities each month. The time falls into 4 categories – cat.1, cat.2, Training and H&S and the monthly totals for these are calculated in cells E31, E32, E33 and E34 respectively. Each month is on a separate sheet within the workbook.<o


<o


The time is in hh:mm format.<o


<o


At the end of each month I have to submit a report for the total time spent in each category. The thing is this report is on a protected Word document. The only areas of the Word document available for editing are the text fields for the respective categories and the responsible manager’s name. At the moment I have to manually enter the values from the spreadsheet on the Word document.<o


<o


I would like, if it is possible, to have the report automatically generated by a macro attached to a button on the spreadsheet.<o


<o


The steps I think I need to accomplish when the button is clicked are as follows:<o


<o


1) Pull the data from cells E31 etc into (string?) variables - I have no problem doing this bit
<o


<o


<o


<o


<o


<o




<o


So is what I want to do even possible with VBA? If so how would I go about it? I’m not sure how to get the names or positions of the text fields on the Word document in order to reference them in the code. I could potentially re-create the sheet myself from scratch though if need be.<o


<o


Any input will be greatly appreciated.<o


<o


Please let me know if you need any more information or anything clarifying.<o


<o


Cheers guys.<o

