copy range to word

[jjsanders]

Hello all,

So far i have the following code to move data from the current worksheet to word:

Private Sub CommandButton1_Click()
    
     Dim wdApp As Object
     Dim wdDoc As Object
     Set wdApp = CreateObject("Word.application")
     
     'Set wdDoc = wdApp.Documents.Open _
       (Filename:="T:/temp/temp.doc")
    
     'hier met VBA je document vullen
    
     'wdDoc.SaveAs "T:/temp/test1.doc"
     wdDoc.Close savechanges:=False
     Set wdDoc = Nothing
     wdApp.Quit
     Set wdApp = Nothing
End Sub

I have 2 quetions:
1. how do I copy all the data from A1 to E20 from excel to my word file
2. how can i make the filename dynamic. I want it to be the value of A1 + A5 .doc

Thanx in advance!

 

[Marc]

Utilizing Mr. Excel's Tip at the following link I have modified your code as shown below.

http://www.mrexcel.com/tip014.shtml

Private Sub CommandButton1_Click()

Dim wdApp As Object
Dim wdDoc As Object
Set wdApp = CreateObject("Word.application")

Set wdDoc = wdApp.Documents.Open _
(FileName:="T:/temp/temp.doc")

Sheets("Sheet1").Select
'Copy the data for the new document to the clipboard
Range("A1:E20").Copy
' Tell Word to create a new document
'wdApp.Documents.Add
' Tell Word to paste the contents of the clipboard into the new document
wdApp.Selection.Paste
' Save the new document
wdApp.ActiveDocument.SaveAs "T:\temp\" & Range("A1").Value & Range("A5").Value & ".doc"

'hier met VBA je document vullen


Set wdDoc = Nothing
wdApp.Quit
Set wdApp = Nothing
End Sub

 

[jjsanders]

Hello thanks for your help

I changed the code a bit.

Private Sub CommandButton1_Click()
    Dim wdApp As Object
    Dim wdDoc As Object
    Set wdApp = CreateObject("Word.application")

    Set wdDoc = wdApp.Documents.Open _
    (Filename:="T:/temp/temp.doc")

    Sheets("Voortgang").Select
    'Copy the data for the new document to the clipboard
    Range("A1:E20").Copy
    ' Tell Word to create a new document
    wdApp.Documents.Add
    ' Tell Word to paste the contents of the clipboard into the new document
    wdApp.Selection.Paste
    ' Save the new document
    wdApp.ActiveDocument.SaveAs "T:\temp\" & Range("A1").Value & Range("A5").Value & ".doc"

    Set wdDoc = Nothing
    wdApp.Quit
    Set wdApp = Nothing
End Sub

I got the following error (translated from dutch):
error 5174: error defined by object or application.

How do I deal with this. Maybe its worthwile to mention that we're using excel 2000.

Thanks in advance!

 

[Marc]

I noticed that you removed the remark from in front of this line:

'wdApp.Documents.Add

This means that you will copy the range to a new Word Document as opposed to your temp.doc

I'm not sure if this is the problem, but try commenting out the line above.

I'm using '97 SR-2 version of Excel.

It runs fine on my machine with that line commented out.