Code to take data from cells in Excell and copy it to text field in Word

[Sunjinsak]

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-comfficeffice" /><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>

 

[Derek Brown]

Have a look at:
http://www.mrexcel.com/forum/showthread.php?t=491240
I have never tried it with a protected WORD document, so don't know if it will work for you.

 

[Sunjinsak]

Have a look at:
http://www.mrexcel.com/forum/showthread.php?t=491240
I have never tried it with a protected WORD document, so don't know if it will work for you.

Thank you. This looks to be what I'm after. More complicated than I thought it'd be though lol. I'll give it a good read and see if I can figure it out.

Thanks again.

 

[Sunjinsak]

Hi again.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I’ve had a look at the example given in the link above and I confess it’s really confused me!! I kind of get what’s going on there but I’m not sure about the whys and wherefores of it all. I already have a workbook with the values calculated on it at cells E31-E34. All I want to do is open a specific word document and “copy” those values across to specific bookmarks within the word document. I’ve made a little headway with it and I’ve got as far as opening the word doc. Here’s what I’ve got so far…<o></o>
<o></o>

Dim manName As String<o:p></o:p>
Dim cat1 As String<o:p></o:p>
Dim cat2 As String<o:p></o:p>
Dim training As String<o:p></o:p>
Dim hsTime As String<o:p></o:p>
<o:p></o:p>
manName = InputBox("Please enter the Responsible Manager's name")<o:p></o:p>
cat1 = Worksheets("MAR").Range("E31").Value<o:p></o:p>
cat2 = Worksheets("MAR").Range("E32").Value<o:p></o:p>
training = Worksheets("MAR").Range("E33").Value<o:p></o:p>
hsTime = Worksheets("MAR").Range("E34").Value<o:p></o:p>
<o:p></o:p>
Set wordapp = CreateObject("word.Application")<o:p></o:p>
wordapp.documents.Open "Z:\Time Accounts\Offtime\Monthly Report.doc"<o:p></o:p>
wordapp.Visible = True<o:p></o:p>

<o></o>
<o></o>
The word doc is now unprotected btw. I can rename, recreate or add new bookmarks/text fields if need be. Or anything else for that matter actually.<o></o>
<o></o>
I can’t quite get my head round which part of the code given in the example linked to above is responsible for taking the entries on the workbook and transposing them to the word doc though.<o></o>
<o></o>
Can anyone clarify/help?<o></o>
<o></o>
Thanks again and sorry for the n00b questions!

 

[Derek Brown]

The best thing to do is to run through the example that I describe in posts #4 an d#5 at http://www.mrexcel.com/forum/showthread.php?t=478182
That was a link in the post that I had directed you to.
Don't be put off by it - there is a lot of text there but most of describes what is being done.
Looking at your last post, I see that your values are in a column and not a row.
The process was originally designed for each row being a complete record with details that will replace the bookmarks in the Word document.
You will have to have another worksheet where those values in column E can be presented in a single row.
Try the example first, to see how it works and then we can look at applying it to your requirement.

 

[Sunjinsak]

Okay thanks Derek. I’ll definitely put your example together and step through it. I’m not in work for a couple of days but I’ll get back to you once I’ve (hopefully) got it up and running.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
In the mean time though I’ll take the opportunity to give you a little more info on what I’m trying to accomplish overall – just in case it impacts on how your example can be adapted for use in my circumstances…<o></o>
<o></o>
The workbook has 13 sheets – one for each month of the year and one ‘overview’ page giving totals for the year (but we can forget about that one in terms of what I want to accomplish here). On each sheet there are entries for time spent in each of the four categories each day. The totals for each category for the month are shown in cells E31 through E34 respectively, and are calculated by a formula contained in those cells.<o></o>
<o></o>
As I’ve already said I’d like to take the text values (the formatted hh:mm values as opposed to the underlying decimal values) of cells E31 through E34 and import them into their equivalent bookmarks in a pre-existing word document.<o></o>
<o></o>
The problem is that; a) this needs to take place on a sheet-by-sheet basis, depending on what sheet is active when the button calling the code is pressed, and b) the values need to be split into their separate ‘hours’ and ‘minutes’ constituents before being “sent” to word (this is a requirement of the monthly reporting sheet and beyond my control unfortunately). I’ve already written the code to split the values and it works fine – but I assume this extra step will impact on how your example is applied in this case?<o></o>
<o></o>
Anyway I hope the extra information helps a little in understanding what I want to achieve. Like I said above I’ll step through your example at the earliest convenience and see what I can learn from it. Thank you again for your time and help in this matter - it’s greatly appreciated.

 

[Sunjinsak]

Re: [SOLVED] Code to take data from cells in Excell and copy it to text field in Word

Hi again.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Well, I solved it!<o></o>
<o></o>
Thought I’d post the solution here in case anyone else has this problem/requirement in the future.<o></o>
<o></o>
Derek thanks for your input. I stepped through your example and I learned quite a bit from it, but in the end I stumbled upon an easier and less involved way to achieve what I wanted to do. <o></o>
<o></o>
Basically I set the names of the bookmarks in the word doc the same as the names of the variables I wanted to put in them, then simply “linked” them to the variables containing the relevant data.<o></o>
<o></o>
The relevant section of code looks like this (truncated for the sake of example):<o></o>
<o></o>

Dim manName As String<o:p></o:p>
Dim cat1 As String<o:p></o:p>
Dim cat1Split As Variant<o:p></o:p>
Dim cat1Hr As String<o:p></o:p>
Dim cat1Min As String<o:p></o:p>
<o:p></o:p>
manName = InputBox(“Please enter the manager’s name”)<o:p></o:p>
<o:p></o:p>
cat1 = Worksheets("MAR").Range("E31").Text<o:p></o:p>
cat1Split = <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:City w:st="on"><st1:place w:st="on">Split</st1:place></st1:City>(cat1, ":")<o:p></o:p>
cat1Hr = Val(cat1Split(0))<o:p></o:p>
cat1Min = Val(cat1Split(1))<o:p></o:p>
<o:p></o:p>
Dim wdApp As Word.Application<o:p></o:p>
Dim reportDoc As Word.Document<o:p></o:p>
<o:p></o:p>
Set wdApp = New Word.Application<o:p></o:p>
With wdApp<o:p></o:p>
.Visible = True<o:p></o:p>
.WindowState = wdWindowStateMaximize<o:p></o:p>
End With<o:p></o:p>
<o:p></o:p>
Set reportDoc = wdApp.Documents.Add("Z:\Time Accounts\Offtime\MonthlyReport.doc")<o:p></o:p>
With reportDoc.Bookmarks<o:p></o:p>
.Item("manName").Range = manName<o:p></o:p>
.Item("cat1Hr").Range = cat1Hr<o:p></o:p>
.Item("cat1Min").Range = cat1Min
…etc
End With<o:p></o:p>

<o></o>
<o></o>
This achieves what I want pretty well. Took a while to figure it all out but got there in the end

 

[Derek Brown]

Well done and thank you for the feedback.
I did not get a chance earlier to look at your previous post in detail but the suggestion would have been along the same lines as your solution.
You will of course need to change the name of the worksheet each month ... or get it from an InputBox or alternative coding ....

 

[Sunjinsak]

Thanks.

The code above was truncated for the sake of example.

I've already written code to get the name of the active sheet and to change the name of MonthlyReport.doc based on it. Something along the lines of:

Dim docName As String
If ActiveSheet.Name = "x" Then docName = "y"
reportDoc.SaveAs docName

Again just as an example.

I'm not sure if it's the most efficient or elegant way to achieve the desired result but it works

I hope this thread helps others anyway.

Thanks again for your help and suggestions - greatly appreciated!