PDA

View Full Version : Solved: More ??? on Excel to Word



joelle
10-17-2005, 02:32 PM
Hello VBA Guru's:

I *did search on rondebruin.com and other excel tips links but I cannot see my case.

Since I'm so shallow in VBA please help with some macro codes so that when clicked, it does the followings:

1. From Excel: Open a Word doc named "test.doc" that is saved on the company intranet. The url is: http://abc.xxx.com/

2. Copy the data in A10:M50 of from the active worksheet called "sheetA" to page 2 of the Word doc called "test.doc"

Just that. I believe it is simpler than the other similar cases because it does not deal with title, subject, or mail merge.

Please do not direct me to another link (I beg) and pls shed some light -- I'm truly appreciative of any help.

Nee

TonyJollans
10-17-2005, 02:51 PM
Hi Nee,

To open a Word (session and) document from Excel ..

Set wdApp = CreateObject("Word.Appliaction")
Set wdDoc = wdApp.Documents.Open "http://abc.xxx.com/test.doc"

Going to Page 2 of your document is, however, a bit more awkward. Word doesn't work particularly well with pages. Could you say a bit more about your document structure - how can page 2 be recognised? and where on page 2 do you want to paste a range that may not fit on a page?

joelle
10-17-2005, 03:06 PM
Hello Tony,

First off, so glad to see your post ... thanks!

In reply to your question about page 2 in the Word doc -- I need it as part of the VB code. Also, I forgot to mention that VB first need to locate the last line that has data in the active sheet before it does the copying. Please see structure below:

1. From Excel, open a Word doc called "test.doc" locate on the company intranet
(I saw your code, thx)
2. Go to the active worksheet called "sheetA" and locate the last line that has data, then copy range A10:Mxx to the clipboard (this means col M, xx means last line with data)
3. Go back to the already opened Word doc (test.doc) and do a Edit/Go to/
then, enter a number 2 to the dialog box (to go to page 2 of the word doc)
4. Paste the data from excel to page 2 of the word doc "test.doc". If the data flows to page 3, thats ok.
5. [end sub]. My ws does not need to save. My Word doc does not need to save.


Thanks so much in advance,

Nee

Anne Troy
10-17-2005, 07:49 PM
Part of #3 I was able to record:

Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, Name:="2"

Have you tried recording any of this to give yourself a head-start?

TonyJollans
10-18-2005, 03:42 AM
Hi Nee,

To find the last row and copy your range, then put it together with the other bits you get this. I have just made the word app visible at the end because I don't know what you want to do.

With ActiveSheet.Range("A:M")
Lastrow = .Find(What:="*", _
After:=.Cells(.Rows.Count, .Columns.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End With
ActiveSheet.Range("A10:M" & Lastrow).Copy

Set wdApp = CreateObject("Word.Application")
wdApp.Documents.Open "http://abc.xxx.com/test.doc"

wdApp.Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, Name:="2"
wdApp.Selection.Paste

Application.CutCopyMode = False

wdApp.Visible = True

joelle
10-18-2005, 08:20 AM
Hello Tony,

Thanks for the codes -- Cant wait to try them out!
And I'll sure learn to fish before learning to drink ;)

Later,
Nee

joelle
10-26-2005, 08:43 AM
Hi Tony,

So swamped that I did not have time to run your codes until recently.
And they work!

Thanks a lot,

Nee