copy range to word

jjsanders

New Member
Joined
Oct 16, 2006
Messages
36
Hello all,

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

Code:
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!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Marc

Active Member
Joined
Feb 21, 2002
Messages
388
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

New Member
Joined
Oct 16, 2006
Messages
36
Hello thanks for your help

I changed the code a bit.

Code:
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

Active Member
Joined
Feb 21, 2002
Messages
388
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.
 

Forum statistics

Threads
1,136,305
Messages
5,674,984
Members
419,540
Latest member
Nereus A

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
Top