Automating a copy from Excel to Word

[tigs]

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 automaticall
adjusts them. Once a month I add in more numbers in a row underneath th
previous one.
I need to be able to automate the process of copying Row 1 into a Wor
Template with bookmarks, so rent goes to rent, expenses to the expense
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.
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 o
tails of it emb1

Hope you can help!

tigs

 

[joel]

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

 

[tigs]

Hi,

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

In trying the link version I found it does not seem to open a wor
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
word template. I hope that helps wko

 

[joel]

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

 

[ryguy7272]

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.

 

[tigs]

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

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

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

Regards,

One happy tigs

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





tigs, if I were you, I'd use DocVariables. First of all, do som
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 (fro 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 differen than
mine (they have to be the same in Word and in Excel), but anyway, tha will
get you going.



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



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

Cage Forums
(http://www.thecodecage.com/forumz/showthread.php?t=198453)

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