Pasting a String from excel to Word using word-VBA

Question

I have my DB in Excel and want to consult it to create reports in Word. I have searched and tried different options but none seem to work. Any suggestions would be of great help. I essentially want to paste the content of the second Message box into the word document.

Dim ctl As Control  
Dim some As String
Dim objExcel As Object
Dim objWord As Object
On Error Resume Next

    Set objExcel = GetObject(, "Excel.Application")
    If objExcel Is Nothing Then
        Set objExcel = CreateObject("Excel.Application")
    End If

    Set objWord = GetObject(, "Word.Application")
    If objWord Is Nothing Then
        Set objWord = CreateObject("Word.Application")
    End If

    On Error GoTo 0

    objExcel.Workbooks.Open ("File_Dir")
    objExcel.Visible = False
    objWord.Documents.Open ("File_Dir")
    objWord.Visible = True

    For Each ctl In Me.Controls
         Select Case TypeName(ctl)
             Case "CheckBox"
                 If ctl.Value = True Then
                     MsgBox ctl.Name
                     MsgBox objExcel.Names(ctl.Name).RefersToRange.Value
                     some = objExcel.Names(ctl.Name).RefersToRange.Value

                 End If
         End Select
        Next ctl
    objExcel.Quit
    Set objExcel = Nothing
    MsgBox "complete"

Where do you want to place the content in the Word doc? A good approach might be to create bookmarks in your word file where you can put the content. Without knowing more about what you want to create in Word it's tricky to make useful suggestions as to what wuold be the best approach to take. — Tim Williams
Right now I want to paste it at the end of the document, I could use bookmarks but if I am correct they need to be predefined and my reports will not always have the same number of bookmarks but I'll give it a try. — MrPapity
Where are you planning to run this code - you shouldn't need to use CreateObject() for both Word and Excel... — Tim Williams
I figured it out, I was making a big mess by trying to use 2 Word Documents, that's why I was creating an object for Word. — MrPapity

keronconk keronconk · Accepted Answer · 2016-11-29T00:16:51

You can read the documentation, are you using interop?

    Dim ctl As Control  
    Dim some As String
    Dim objExcel As Object
    Dim objWord As Object
    On Error Resume Next

    Set objExcel = GetObject(, "Excel.Application")
    If objExcel Is Nothing Then
            Set objExcel = CreateObject("Excel.Application")
    End If

    Set objWord = GetObject(, "Word.Application")
    If objWord Is Nothing Then
            Set objWord = CreateObject("Word.Application")
    End If

    On Error GoTo 0

    objExcel.Workbooks.Open ("File_Dir")
    objExcel.Visible = False
    objWord.Documents.Open ("File_Dir")
    objWord.Visible = True

    'give a counter for paragraph
    Dim ctr as Integer = 1

    For Each ctl In Me.Controls
        Select Case TypeName(ctl)
            Case "CheckBox"
                If ctl.Value = True Then
                     MsgBox ctl.Name
                     some = objExcel.Names(ctl.Name).RefersToRange.Value
                     MsgBox some

                     'You can write data to Word document here
                     'You must add paragraph
                     objWord.Documents.addParagraph()
                     objWord.Documents.Paragraph(ctr).Range(ctl.Name)
                     ctr = ctr + 1

                End If
        End Select
    Next ctl
    objExcel.Quit
    Set objExcel = Nothing
    MsgBox "complete"

Pasting a String from excel to Word using word-VBA

1 Answers