Copy data from excel to word

Mkeerthi

New Member
Joined
Nov 23, 2017
Messages
17
Please help in copying data from excel 2007 to word 2007 with a macro button:confused:. Urgent

I have a range of data from A1:I34 in the excel workbook "Sheet1" data in that range need to be copied to existing Word document "November".

And if macro is ran for second time it should copy the data in the same rang A1:A34 of Sheet1 to page 2 of existing word document "November".
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Try something along the lines of:
Code:
Sub SendRangeToDoc()
'Note: A reference to the Word library must be set, via Tools|References
Dim wdApp As New Word.Application, wdDoc As Word.document, StrNm As String
StrNm = "C:\Users\" & Environ("UserName") & "\Documents\November.docx"
If Dir(StrNm) <> "" Then
  wdApp.Visible = True
  With wdApp
    'open the Word Document
    Set wdDoc = Documents.Open(Filename:=StrNm)
    With wdDoc
        Sheets("Sheet1").Range("A1:I34").Copy
        .Characters.Last.InsertBefore Chr(12)
        .Characters.Last.Paste
        If .Characters.First = Chr(12) Then .Characters.First.Delete
        .Close True
    End With
    .Quit
  End With
Else
  MsgBox "File: " & StrNm & " not found."
End If
Set wdDoc = Nothing: Set wdApp = Nothing
End Sub
Adjust the filepath to suit.
 
Last edited:
Upvote 0
I have tried to replicate the above code with required modification and it says Compile error: User defined type not allowed. Following is the code that I have used. Please help me to debug if there is any error with the code/reference.

Sub SendRangeToDoc()
Dim wdApp As New Word.Application, wdDoc As Word.document, StrNm As String
StrNm = "C:\Users\keerthiM\Documents\NOVEMBER2017"
If Dir(StrNm) <> "" Then
wdApp.Visible = True
With wdApp
'open the Word Document
Set wdDoc = Documents.Open(Filename:=StrNm)
With wdDoc
Sheets("Sheet1").Range("A1:I34").COPY
.Characters.Last.InsertBefore Chr(12)
.Characters.Last.Paste
If .Characters.First = Chr(12) Then .Characters.First.Delete
.Close True
End With
.Quit
End With
Else
MsgBox "File: " & StrNm & " not found."
End If
Set wdDoc = Nothing: Set wdApp = Nothing
End Sub
 
Upvote 0
I think I have failed to add proper reference. Please guide to do so. I have opened Visual basic>Tools>Reference>Microsoft object 12.0 Objectt library is checked in. Am I supposed to check any other reference other than the above.:confused:
 
Upvote 0
I think I have failed to add proper reference. Please guide to do so. I have opened Visual basic>Tools>Reference>Microsoft object 12.0 Objectt library is checked in. Am I supposed to check any other reference other than the above.:confused:
The code I posted includes a note (which you deleted from your code) telling you what reference to need to set.

PS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.
 
Upvote 0
There is an improvement. I am able to fix the reference problem. I have checked Microsoft Word 12.0 Object library. Now that Compile error: User defined type not allowed is no more. But I am getting file not found error though I have enabled proper path reference. Please help me if I am missing some where.
 
Upvote 0
Code:
This is the code I have exactly pasted in my visual basic. Now the error is remote server machine does not exist or is unavailable

Sub SendRangeToDoc()
Dim wdApp As New Word.Application, wdDoc As Word.document, StrNm As String
StrNm = "C:\Users\keerthiM\Documents\NOVEMBER2017.docx"
If Dir(StrNm) <> "" Then
  wdApp.Visible = True
  With wdApp
    'open the Word Document
    Set wdDoc = Documents.Open(Filename:=StrNm)
    With wdDoc
        Sheets("Sheet1").Range("A1:I34").COPY
        .Characters.Last.InsertBefore Chr(12)
        .Characters.Last.Paste
        If .Characters.First = Chr(12) Then .Characters.First.Delete
        .Close True
    End With
    .Quit
  End With
Else
  MsgBox "File: " & StrNm & " not found."
End If
Set wdDoc = Nothing: Set wdApp = Nothing
End Sub
 
Upvote 0
Please clarify the nature of the problem. First you say it's 'file not found', then you say it's 'remote server machine does not exist or is unavailable'.

'.
 
Upvote 0
file not found got rectified after I added .docx behind NOVEMBER2017 in the code. Now 'remote server machine does not exist' is the problem. Many thanks for your patience.
 
Upvote 0
Your previous mention of 'Microsoft Word 12.0 Object library' suggests you're using Office 2007, so there shouldn't be any trouble accessing a docx file. Did you disable any other references when you added the Word one? If you did, you should re-enable them.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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