Microsoft Excel to Microsoft Word

[johnbird1988]

Hello,

I am just wondering if some one can help. I need to create a performance template for a number of business. The template is going to be generic for all business.

At the moment I have linked charts from Excel to Word along with specific text. The problem I have it with tables. I wanted some VBA code that will copy a range from Excel into a specific location in the word document. The specific location in the word document is bookmarked but I am not sure where to start writing the VBA. I have a good knowledge of using VBA in Excel but not across applications.

Thank you for your help

John

 

[RedBeard]

Something like this:

Sub test()Dim objWord As Object
Dim ws As Worksheet


    Set ws = ThisWorkbook.Sheets("Sheet1")


    Set objWord = CreateObject("Word.Application")


    objWord.Visible = True


    objWord.Documents.Open "C:\test.docx" ' change as required


    With objWord.ActiveDocument
        .Bookmarks("Text1").Range.Text = ws.Range("A1").Value
        .Bookmarks("Text2").Range.Text = ws.Range("A2").Value
        .Bookmarks("Text3").Range.Text = ws.Range("A3").Value
    End With
 
    Set objWord = Nothing


End Sub

 

[johnbird1988]

Something like this:

Sub test()Dim objWord As Object
Dim ws As Worksheet


    Set ws = ThisWorkbook.Sheets("Sheet1")


    Set objWord = CreateObject("Word.Application")


    objWord.Visible = True


    objWord.Documents.Open "C:\test.docx" ' change as required


    With objWord.ActiveDocument
        .Bookmarks("Text1").Range.Text = ws.Range("A1").Value
        .Bookmarks("Text2").Range.Text = ws.Range("A2").Value
        .Bookmarks("Text3").Range.Text = ws.Range("A3").Value
    End With
 
    Set objWord = Nothing


End Sub

This is great I can get it to work if the Word Document is not open. However, if the word document is open it errors. I tried taking out the

objWord.Documents.Open

do I need something like activate word book.

This is my current code

Private Sub Insert_Cht_Tbl()


    Set objWord = CreateObject("Word.Application")


    objWord.Visible = True


    objWord.Documents.Open "G:\Business\[Business NAME] Visit Pack.docx" ' change as required


Sheet2.Range("F6:L" & (46 - Application.WorksheetFunction.CountBlank(Sheet2.Range("F6:F51"))) + 5).Copy
objWord.ActiveDocument.Bookmarks("TEST").Range.PasteAndFormat (wdFormatOriginalFormatting)


    Set objWord = Nothing


End Sub

Thank you

John

 

[RedBeard]

Didn't consider that. Silly miss.

Try checking if Word is already open with something like:

Dim WordApp As Word.Application
Set WordApp = GetObject(class:="Word.Application")

Then test it with:

If WordApp Is Nothing Then Set WordApp = CreateObject(class:="Word.Application")

That should do it I think.