Excel macro referring to Word doc

S

scotawful

Board Regular
Joined
Mar 6, 2003
Messages
148
This one's a tuffy (I think). I've got a macro (from a previous post) that creates a word doc. and then allows me to copy/paste from excel to the newly-created word doc. I'm trying to modify this so that instead of the macro creating a new word doc. from scratch, the user will define an already-open word document in which the stuff from excel is to be pasted. I've gotten as far as settin the variable that represents the user-defined word doc. The problem I'm having is when I try to define that variable so that it can be used in the statements that paste into word. The code looks something like this:

Sub excel_to_word()

Dim FName As Variant
FName = Application.GetOpenFilename("Word Documents (*.doc), *.doc")

Dim wdDoc As Object

Set wdDoc = FName ****here is where the problem starts*****

Range("d9").Select
Selection.Copy
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste

end sub

Does anyone have any suggestions or similar code that I might be able to scratch a solution out of???
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Hi,

Is this what you mean?
Code: 
Sub excel_to_word()
Dim FName As Variant
Dim appWD As Word.Application
Dim wdDoc As Word.Document

FName = Application.GetOpenFilename("Word Documents (*.doc), *.doc")
Set appWD = CreateObject("Word.Application")
Set wdDoc = appWD.Documents.Open(FName)

Range("d9").Copy
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste

End Sub
HTH
 
Upvote 0
Whew! This works, for the most part. How would you define the word document without having to use the open command: I'm trying to get this code to work with the word document already open.

Thanks so much for the help so far though!!

PS. I wasn't able to get the:

Code: 
dim wdApp as word.application
dim wdDoc as word.document

to work properly, so I simply defined them as objects since I kept getting an undefined variable type error.
 
Upvote 0
Hi,

Sorry, forgot to explain about the reference needed - see code below:
Code: 
Sub excel_to_word()
Const FName As String = "C:\My Documents\Letters\TERMINATION PAYMENTS.doc"
Dim appWD As Word.Application
Dim wdDoc As Word.Document
'need to set a reference (Tools menu) to the Microsoft Word Object Library
'otherwise, define as Objects

Set appWD = CreateObject("Word.Application")
Set wdDoc = appWD.Documents.Open(FName)

Range("d9").Copy
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste

End Sub
HTH
 
Upvote 0
I ended up reworking the code you gave earlier to this:

Code: 
Dim FName As Variant
Dim wdApp As Object
Dim wdDoc As Object

FName = Application.GetOpenFilename("Word Documents (*.doc), *.doc")
Set wdApp = GetObject(, "Word.Application")
Set wdDoc = wdApp.Documents(FName)

This let me set FName as variable to an already-open word doc. From there I had to use the GetObject command instead of CreateObject because I am assuming that Word will already be a running application.
Now the problem I'm running into is when I want to run a small piece to make sure an instance of word is already up. Does anyone know the correction I need to make to the following code to check for Word instead of Excel like this one does:
Code: 
Dim chk As Long
chk = FindWindow("xlMAIN", 0)

Richie, Thanks for all the help so far. Much appreciated.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Macro run from Excel to transfer data in new Word doc won't output into Word
Replies
5
Views
389
cpu97
C
J
  • Solved
Create Word document and center title
Replies
5
Views
565
Tetra201
Tetra201
J
  • Question Question
Copy Picture - VBA
2
Replies
10
Views
209
Logit
Logit
O
  • Question Question
Change MS Word Doc Properties via MS Excel VBA Macro
Replies
3
Views
685
NdNoviceHlp
NdNoviceHlp
A
  • Question Question
Save as dialogue box for excel, but pre populating filename from source word document
Replies
8
Views
264
RoryA
RoryA

Forum statistics

Threads
1,224,616
Messages
6,179,909
Members
452,949
Latest member
beartooth91

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top