how to copy and past content Word document from Excel, VBA

Question

I have a Word document containing a few lines of text and a table with many place holders I will fill in from Excel later in the same document but saving it as PDF.

My final goal is to duplicate the whole text in the word file as it is before any modification and paste it every time I have to complete it with the values in the Excel file.

Inside word VBA, this works perfectly. Copy the whole document and past it at the end, duplicating the table and the lines of text.

Selection.WholeStory
Selection.Copy
Selection.MoveDown Unit:=wdParagraph, Count:=2 
Selection.PasteAndFormat (wdFormatOriginalFormatting)

And in Excel VBA, I have this working just fine. Except the * Asterics part, I don't know how to execute the code that works in Word VBA from Excel VBA.

Sub GenerateDoc()

DocLoc = Application.ActiveWorkbook.Path & "\CategoryTable2.docx"
'Open Word Template

With Sheet2
On Error Resume Next 'If Word is already running
Set WordApp = GetObject("Word.Application")
    If Err.Number <> 0 Then
            'Launch a new instance of Word
            Err.Clear
            'On Error GoTo Error_Handler
            Set WordApp = CreateObject("Word.Application")
    End If ' Work Running Check
            WordApp.Visible = True 'Make the application visible to the user
            Set WordDoc = WordApp.Documents.Open(Filename:=DocLoc, ReadOnly:=False) 'Open Template
            
        'This is not workin, no error throw however
        '*****************************************
            WordDoc.Content.WholeStory
            WordDoc.Content.Copy
         '***************************************   
            For component = 15 To 150 
            iRow = component
            
            If .Cells(iRow, 1).Value = 0 And .Cells(iRow, 2).Text <> "" Then
            
                For CustCol = 3 To 85 'Move Through  Columns
                        If Left(.Cells(13, CustCol).Text, 1) = "[" And Right(.Cells(13, CustCol).Text, 1) = "]" Then
                            varName = .Cells(13, CustCol).Value 'Determine Variable Name
                            'varName = "[" & varName & "]"
                            VarValue = Trim(.Cells(iRow, CustCol).Text) 'Determine Variable Value
                             With WordDoc.Content.Find
                                .Text = varName
                                .Replacement.Text = Application.WorksheetFunction.Text(VarValue, "General")
                                .Wrap = wdFindContinue
                                .Execute Replace:=wdReplaceAll 'Find & Replace all instances
                             End With
                        End If
                 Next CustCol
                 
           End If
           Next component
            
        'This is not working, no error throw however
            '**************************************************************************
            WordDoc.Content.MoveDown Unit:=wdParagraph, Count:=2
            WordDoc.Content.PasteAndFormat (wdFormatOriginalFormatting)
            '**************************************************************************
            
            Filename = Application.ActiveWorkbook.Path & "\ComponentsTable.pdf"   'Create full filename & Path with current workbook location, Last Name & First Name
            On Error Resume Next
            Kill (Filename) 'Delete filename with the same name if it exists
            On Error GoTo 0
            On Error Resume Next
            WordDoc.ExportAsFixedFormat OutputFileName:=Filename, ExportFormat:=wdExportFormatPDF
        WordDoc.Close False
    
    WordApp.Quit
    Set WordDoc = Nothing
    Set WordApp = Nothing

    
End With
End Sub

Variatus · Accepted Answer · 2021-02-16 01:50:13Z

1

You need to change the logic of your approach.

'WordDoc.Content.WholeStory' specifies an object. Your code does nothing with it. WordDoc.Content.Copy copies an unrelated, other object. Perhaps you mean 'WordDoc.Content.WholeStory.Copy' but this argument is moot. Imagine the entire Word document as one string containing text as well as formatting characters. Therefore you can't copy the WholeStory which is a range. You can only copy its Text.

Once you assign the Text to a string you can paste it to a single cell in Excel. In other words, the String created in Word is understood by Excel and handled within Excel the way Excel handles its own strings. However, that string will definitely contain many characters Excel can't interpret and may contain some that Excel interprets differently. They may even cause Excel to split the original string into more than one cell.

Therefore you need to parse the string lifted from Word and manipulate it into the format you want it to have in Excel. The transition you are asking about takes place at the point where a Word-string becomes an Excel-string. Bear in mind that a Word-range can't become an Excel-range because the two are entirely different animals.

answered Feb 16, 2021 at 1:50

Variatus

14.4k2 gold badges16 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

German Over a year ago

Thank you very much for the explanation. Now I understand better WHY it finally worked after try and error. My lack of bibliography on the subject is scary. I just pasted my solution. I learn to copy a range (actually, cutting it, and pasting when needed)

German · Accepted Answer · 2021-02-16 02:09:19Z

Ok, I found something interesting.

I was not getting any error message because of the

ON ERROR RESUME NEXT

I found that to avoid this, ON ERROR GOTO 0 worked. After that, it was easy to google for errors and find what was wrong. Also, my logic was flawed, I fix it like this. The *Asterix is the interesting part.

Reference https://learn.microsoft.com/en-us/office/vba/api/word.range.copy

Sub GenerateDoc()

 
Dim WordApp As New Word.Application
Dim WordDoc As Word.Document

    DocLoc = Application.ActiveWorkbook.Path & "\CategoryTable2.docx"
    'Open Word Template
    
    With Sheet2
    On Error Resume Next 'If Word is already running
    Set WordApp = GetObject("Word.Application")
        If Err.Number <> 0 Then
                'Launch a new instance of Word
                Err.Clear
                'On Error GoTo Error_Handler
                Set WordApp = CreateObject("Word.Application")
        End If ' Work Running Check
                WordApp.Visible = True 'Make the application visible to the user
                Set WordDoc = WordApp.Documents.Open(Filename:=DocLoc, ReadOnly:=False) 'Open Template
                On Error GoTo 0

                '**********************************************************
                WordDoc.Range(WordDoc.Content.Start, WordDoc.Content.End).Cut
    '
    '            WordDoc.Content.Selection.WholeStory
    '            WordDoc.Content.Selection.Copy
                
                For component = 15 To 150
                iRow = component
                
                If .Cells(iRow, 1).Value = 0 And .Cells(iRow, 2).Text <> "" Then
                'Now past a template copy
                '*****************************************************************************************
                Set myRange = WordDoc.Range(Start:=WordDoc.Content.End - 1, End:=WordDoc.Content.End - 1)
                myRange.Paste
                                       
                    For CustCol = 3 To 85 'Move Through  Columns
                            If Left(.Cells(13, CustCol).Text, 1) = "[" And Right(.Cells(13, CustCol).Text, 1) = "]" Then
                                varName = .Cells(13, CustCol).Value 'Determine Variable Name
                                'varName = "[" & varName & "]"
                                VarValue = Trim(.Cells(iRow, CustCol).Text) 'Determine Variable Value
                                 With WordDoc.Content.Find
                                    .Text = varName
                                    .Replacement.Text = Application.WorksheetFunction.Text(VarValue, "General")
                                    .Wrap = wdFindContinue
                                    .Execute Replace:=wdReplaceAll 'Find & Replace all instances
                                 End With
                            End If
                     Next CustCol
    
                     
               End If
    
               Next component
                
    '            WordDoc.MoveDown Unit:=wdParagraph, Count:=2
    '            WordDoc.PasteAndFormat (wdFormatOriginalFormatting)
    
                Filename = Application.ActiveWorkbook.Path & "\ComponentsTable.pdf"   'Create full filename & Path with current workbook location, Last Name & First Name
                On Error Resume Next
                Kill (Filename) 'Delete filename with the same name if it exists
                On Error GoTo 0
                On Error Resume Next
                WordDoc.ExportAsFixedFormat OutputFileName:=Filename, ExportFormat:=wdExportFormatPDF
            WordDoc.Close False
        
        WordApp.Quit
        Set WordDoc = Nothing
        Set WordApp = Nothing
    
        
    End With
    End Sub

Collectives™ on Stack Overflow

how to copy and past content Word document from Excel, VBA

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related