Copy from Excel to Word Doc as Multiple Images

[jwb1012]

I have a spread sheet that consolidates multiple worksheets into one. Then, the user sets the page breaks. Next, the user can hit a "PDF" button and it will export to pdf. It looks great - exactly as it does in excel. But, I cant quite seem to get the Word export to work.

When the "Word" button is clicked, it opens new word doc, pastes the print area from excel, and autofits to fit the window. Unfortunately, its not clean looking when I do this.

I would like to copy the print area and paste each page as a separate image one after the other
Any thoughts?

Current Code:

Sub printer()
    Const strRangeToCopy As String = "print_area"
    Dim appWord As Object
    Range(strRangeToCopy).Copy
    'Copy Excel Table Range
 
    On Error Resume Next
    Set appWord = GetObject(, "Word.Application")
    On Error GoTo 0
    If appWord Is Nothing Then Set appWord = CreateObject("Word.Application")
    With appWord
        .Documents.Add
        .Selection.Paste
        .Visible = True
    End With
    ActiveDocument.Tables(1).AutoFitBehavior _
    wdAutoFitWindow
     
End Sub

 

[jwb1012]

If I use the code below, I can get it to paste as an image... but what I really need is to copy/paste the area as multiple images along the page breaks defined in excel.

Sub printer()
    Const strRangeToCopy As String = "print_area"
    Dim appWord As Object
    Range(strRangeToCopy).Copy
    'Copy Excel Table Range
 
    On Error Resume Next
    Set appWord = GetObject(, "Word.Application")
    On Error GoTo 0
    If appWord Is Nothing Then Set appWord = CreateObject("Word.Application")
    With appWord
        .Documents.Add
        .Selection.PasteAndFormat wdChartPicture
        .Visible = True
    End With
     
End Sub

 

[Macropod]

This is a continuation of the topic you started in: http://www.mrexcel.com/forum/genera...l-paste-formatted-text-new-word-document.html. Per Forum Rule #12 (http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html), kindly don't start multiple threads on the same topic.

 

[Macropod]

An object pasted into a Word document cannot span a page break. Consequently, if you need an image of each Excel page, each of those pages would have to be pasted into Word as a separate image.

That said, if all you're doing is producing PDFs, I really can't see the point of involving Word when you can do the job directly from Excel.

 

[jwb1012]

The goal is to be able to export the deliverable into various formats for inclusion into proposals. Sometimes we are able to submit this artifact as a separate PDF, but other times we are required to include the artifact within our word document.

Is there an easy way to assign each page as a separate range? And cycle through all of the ranges to copy/paste them into word?

 

[Macropod]

In that case, when you need PDFs, create them directly from Excel; when you want Word documents, either copy & paste the data to Word as a Word table (which can span multiple pages) or loop through the 'pages' in Excel (which means the macro would have to be able to work out what the range to copy is, both for the individual page and overall) and copy & paste each page as an image into Word.

 

[Worf]

See the following thread on how to work with Excel ranges and page breaks, especially post #29:

http://www.mrexcel.com/forum/excel-questions/926071-dont-want-rows-repeat-top-condtion-3.html