1
votes

I have multiple Excel tabs that I would like to output into a word document. I was able to take some code (with help from the community of course!) and output the first tab of my excel to the word document. When I try to take the second tab on sheet 3 it just replaces the first page that I have created.

I have also tried another route of exporting each sheet as a separate word document and then merging them but that also ran into the same problem where the word just replaced the first page over and over.

Basically my code does the following:

  1. Creates a word file with specific margin outlines.
  2. Sets the table as a usedrange in the excel sheet. copies this
  3. Pastes into word.

Tries again for the next sheet. Runs into issues.

(Set tbl = ThisWorkbook.Worksheets(Sheet3.Name).UsedRange)

Dim tbl As Excel.Range
Dim WordApp As Word.Application

Dim WordTable As Word.Table

Dim MainDoc As Word.Document
Dim mydoct1 As Word.Document

Dim sFolderPath As String

Sub Export_to_Word()

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    'Create an Instance of MS Word
    On Error Resume Next
        'Is MS Word already opened?
        Set WordApp = GetObject(class:="Word.Application")
        'Clear the error between errors
        Err.Clear
        'If MS Word is not already open then open MS Word
        If WordApp Is Nothing Then Set WordApp = CreateObject(class:="Word.Application")
        'Handle if the Word Application is not found
        If Err.Number = 429 Then
            MsgBox "Microsoft Word could not be found, aborting."
            GoTo EndRoutine
        End If
    On Error GoTo 0
      
    'Make MS Word Visible and Active
    WordApp.Visible = True
    WordApp.Activate
        
    'Create a New Document
    Set mydoc1 = WordApp.Documents.Add
    With mydoc1.PageSetup
        .TopMargin = Application.CentimetersToPoints(1)
        .BottomMargin = Application.CentimetersToPoints(1)
        .LeftMargin = Application.CentimetersToPoints(1)
        .RightMargin = Application.CentimetersToPoints(1)
    End With
    
    Set tbl = ThisWorkbook.Worksheets(Sheet2.Name).UsedRange
    tbl.Copy
        
    'Paste Table into MS Word
    mydoc1.Paragraphs(1).Range.PasteExcelTable False, False, False
    
    'Autofit Table so it fits inside Word Document
    Set WordTable = mydoc1.Tables(1)
    WordTable.AutoFitBehavior (wdAutoFitWindow)
    
    
    mydoc1.Range.InsertAfter Chr(13) & "Hello"
    
    my.Collapse Direction:=wdCollapseEnd
    mydoc1.Range.InsertBreak
    
    Set tbl = ThisWorkbook.Worksheets(Sheet3.Name).UsedRange
    tbl.Copy
    
    mydoc1.Range.PasteExcelTable False, False, False
    
EndRoutine:
    
    'Clear The Clipboard
    Application.CutCopyMode = False
    
    mydoc1.SaveAs Filename:=Application.ActiveWorkbook.Path & "\Application_Temp\" & "Sheet1"
    mydoc1.Close
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
End Sub
1
@CindyMeister Thank you! I thought it was similar to excel where if you selected a cell and then wrote into a different cell the activecell would change. I will give it a try and let you know how it goes. I have been trying various versions of that line to see if I can move the range along, but have been having some problems with that portion.kamikazzy

1 Answers

0
votes

The trick to adding things to existing content in Word is to use Range objects. Think of ranges like invisible selections with the major difference that there can be only one selection, but code can use as many ranges as needed to manipulate content.

To your comment: Selection in Word is very much like ActiveCell in Excel; Excel coding pros will always say: avoid using "Active"-anything, work with specific objects, for example Ranges instead of ActiveCell. The same is true with Word :-) You can never be sure which is the active cell, or where the selection is; Range("A3") tells you exactly what is meant, Document.Paragraphs(2) the same.

In that spirit, I've altered an extract of the code in the question, declaring (Dim) and instantiating (Set) a Range object to the entire main body of the document. The range is then "collapsed" (think of it like pressing the right-arrow of a selection) to its end-point so that adding new content won't replace previous content (the same problem as typing over a selection).

Dim rngTarget as Object     'Word.Range for early-binding or within Word
Set rngTarget = mydoc1.Paragraphs(1).Range

'Paste Table into MS Word
rngTarget.PasteExcelTable False, False, False

'Autofit Table so it fits inside Word Document
Set WordTable = mydoc1.Tables(1)
WordTable.AutoFitBehavior (wdAutoFitWindow)

rngTarget.InsertAfter Chr(13) & "Hello"    
rngTarget.Collapse Direction:=wdCollapseEnd

rngTarget.InsertBreak 7  'Word.WdBreakType.wdPageBreak (default type)
rngTarget.Collapse Direction:=wdCollapseEnd

Set tbl = ThisWorkbook.Worksheets(Sheet3.Name).UsedRange
tbl.Copy

rngTarget.PasteExcelTable False, False, False

I've also made some adjustments to the clean-up. When working with an outside application it's important to correctly release its declared and instantiated objects. This should be done in the reverse order they were created. If these are not correctly released, it could hold them in memory, causing errors the next time the code is run.

EndRoutine:

  'Clear The Clipboard
  Application.CutCopyMode = False

  mydoc1.SaveAs Filename:=Application.ActiveWorkbook.Path & "\Application_Temp\" & "Sheet1"
  mydoc1.Close
  Set rngTarget = Nothing
  Set WordTable = NOthing
  Set tbl = Nothing
  Set mdyDoc1 = Nothing

  'Possibly you also want
  'WordApp.Quit
  'This you definitely need
  Set WordApp = Nothing

  Application.ScreenUpdating = True
  Application.EnableEvents = True