Copy data from excel to word

[Mkeerthi]

Please help in copying data from excel 2007 to word 2007 with a macro button. 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".

 

[Macropod]

Try something along the lines of:

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.

 

[Mkeerthi]

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

 

[Mkeerthi]

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.

 

[Macropod]

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.

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.

 

[Mkeerthi]

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.

 

[Mkeerthi]

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

 

[Macropod]

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'.

'.

 

[Mkeerthi]

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.

 

[Macropod]

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.