Automating a copy from Excel to Word
 

Hi everyone,

I have looked around for this answer but cannot seem to find it.

I have one spreadsheet in which I put numbers and it automatically
adjusts them. Once a month I add in more numbers in a row underneath the
previous one.
I need to be able to automate the process of copying Row 1 into a Word
Template with bookmarks, so rent goes to rent, expenses to the expenses
heading, etc. So it creates a statement for that month.

Each month I would like to be able to work down the row up until 12. I
would not mind making 12 macros, one for reach row/month.

I have come across one link with an example but I cannot make heads or
tails of it emb1

Hope you can help!

tigs


--
tigs
------------------------------------------------------------------------
tigs's Profile: http://www.thecodecage.com/forumz/member.php?u=1802
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=198453

http://www.thecodecage.com/forumz

Automating a copy from Excel to Word
 

Are you putting the data into word as a table? I may be easier to
delete the table and add a new table. I don't know your expertese in
VBA put the link you posted doesn't look to complicated. What problems
are you having? Can you better descirbe what cells you are trying to
copy and the book marks in Word you plan to use?


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=198453

http://www.thecodecage.com/forumz

Automating a copy from Excel to Word
 

Hi,

Sorry if I was being a bit vague. The information I am copying over is
not a table, but amounts such as 175.00, 242.00, etc. They are pasted
into bookmarks in the word template like <Subtotal and <Charges.

In trying the link version I found it does not seem to open a word
template. I can follow it up until

VBA Code:
--------------------


Range("A1:F15").Copy
' Tell Word to create a new document

--------------------




Instead I want to copy a few cells from excel and paste those into a
word template. I hope that helps wko


--
tigs
------------------------------------------------------------------------
tigs's Profile: http://www.thecodecage.com/forumz/member.php?u=1802
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=198453

http://www.thecodecage.com/forumz

Automating a copy from Excel to Word
 

See if this helps. The code you posted was from 1999 which would of
been Office 1998. I changed the code to Office 2007 (12). Office 2002
is 10, and Office 2003 is (11). "bookmarkA" is a test bookmark that I
added to a document. You can change the name to anything you want to.


Dim appWD As Word.Application
' Create a new instance of Word & make it visible
Set appWD = CreateObject("Word.Application.12")
appWD.Visible = True
FName = "c:\temp\test.doc"
Set mydoc = appWD.Documents.Open(Filename:=FName)

With mydoc
.GoTo What:=wdGoToBookmark, Name:="bookmarkA"
End With


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=198453

http://www.thecodecage.com/forumz

Automating a copy from Excel to Word
 

tigs, if I were you, I'd use DocVariables. First of all, do some research on
DocVariables. You'll see that you can see them in Word with Alt+F9. You
need to create named ranges in Excel. Then, urn this code (from Excel):

Sub PushToWord()

Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bkmk As Word.Bookmark
sWdFileName = Application.GetOpenFilename(, , , , False)
Set doc = objWord.Documents.Open(sWdFileName)

objWord.activedocument.variables("BrokerFirstName" ).Value =
Range("BrokerFirstName").Value
objWord.activedocument.variables("BrokerLastName") .Value =
Range("BrokerLastName").Value

ActiveDocument.Fields.Update

objWord.Visible = True

End Sub

Of course your DocVariables and your named ranges will be different than
mine (they have to be the same in Word and in Excel), but anyway, that will
get you going.



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"joel" wrote:


See if this helps. The code you posted was from 1999 which would of
been Office 1998. I changed the code to Office 2007 (12). Office 2002
is 10, and Office 2003 is (11). "bookmarkA" is a test bookmark that I
added to a document. You can change the name to anything you want to.


Dim appWD As Word.Application
' Create a new instance of Word & make it visible
Set appWD = CreateObject("Word.Application.12")
appWD.Visible = True
FName = "c:\temp\test.doc"
Set mydoc = appWD.Documents.Open(Filename:=FName)

With mydoc
.GoTo What:=wdGoToBookmark, Name:="bookmarkA"
End With


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=198453

http://www.thecodecage.com/forumz

.

Automating a copy from Excel to Word
 

Thanks you two! I can begin to see where I am going wrong, I had not
even named ranges or anything. And changing the word version would not
have crossed my mind.

The variables is still baffling me a bit but I managed to find some code
to export excel to a report template. I now just have to dissect it and
make 11 more for throughout the year. So I will probably run into
trouble lol

Thanks a lot for pointing me in the right direction and for the quick
response :o:

Regards,

One happy tigs

P.S. I'll attach the test files here in case anyone stumbles onto this
site. It seems to be a rather common problem





ryguy7272;709478 Wrote:

tigs, if I were you, I'd use DocVariables. First of all, do some
research on
DocVariables. You'll see that you can see them in Word with Alt+F9.
You
need to create named ranges in Excel. Then, urn this code (from
Excel):

Sub PushToWord()

Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bkmk As Word.Bookmark
sWdFileName = Application.GetOpenFilename(, , , , False)
Set doc = objWord.Documents.Open(sWdFileName)

objWord.activedocument.variables("BrokerFirstName" ).Value =
Range("BrokerFirstName").Value
objWord.activedocument.variables("BrokerLastName") .Value =
Range("BrokerLastName").Value

ActiveDocument.Fields.Update

objWord.Visible = True

End Sub

Of course your DocVariables and your named ranges will be different
than
mine (they have to be the same in Word and in Excel), but anyway, that
will
get you going.



--
Ryan---
If this information was helpful, please indicate this by clicking
''Yes''.


"joel" wrote:


See if this helps. The code you posted was from 1999 which would of
been Office 1998. I changed the code to Office 2007 (12). Office
2002
is 10, and Office 2003 is (11). "bookmarkA" is a test bookmark that
I
added to a document. You can change the name to anything you want
to.


Dim appWD As Word.Application
' Create a new instance of Word & make it visible
Set appWD = CreateObject("Word.Application.12")
appWD.Visible = True
FName = "c:\temp\test.doc"
Set mydoc = appWD.Documents.Open(Filename:=FName)

With mydoc
.GoTo What:=wdGoToBookmark, Name:="bookmarkA"
End With


--
joel

------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: 'Automating a copy from Excel to Word - The Code
Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=198453)

'The Code Cage - Mobile' (http://www.thecodecage.com/forumz)

.



+-------------------------------------------------------------------+
|Filename: WorkWithWord.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=567|
+-------------------------------------------------------------------+

--
tigs
------------------------------------------------------------------------
tigs's Profile: http://www.thecodecage.com/forumz/member.php?u=1802
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=198453

http://www.thecodecage.com/forumz