Creating a word report using multiple excel figures and tables

[Lshulster]

Hi
I am creating about 100 reports. There is a separate report for every long-term care institution in our jurisdiction. I have several figures and some tables in excel that i would like to import to a word document (template) from excel for each report (institution). In excel essentially i have a dropdown for the institution name, selecting a new name updates all the figures and tables in the file. After changing the name i want to click a button that would export all the figures and tables to a word document. I have the excel document working as I would like but figure out how export figures and tables (these a text tables) to an excel file.

Please help

 

[Chihiro]

It would be hard to help you without sample workbook and Word doc.

However, for what you are looking to do, it will require VBA solution.

Ex: Lets say you have single page which is set up as print area. Assuming that the button is located on the same sheet as range that's being exported. This can be exported to Word using something like below.

Sub Test()
Dim wsName As String
Dim ws As Worksheet
Dim WordApp As Object

wsName = ActiveSheet.Name
Set ws = Worksheets(wsName)
With ws
    If .PageSetup.PrintArea = "" Then
        MsgBox "PrintArea has not been set, please set print area"
    Else
        .Range(.PageSetup.PrintArea).Copy
    End If
End With

Set WordApp = CreateObject("Word.Application")
With WordApp
    .Documents.Add
    .Selection.Paste
    .Visible = True
    .Activate
End With

Set WordApp = Nothing

End Sub

Note: This code just copies print area from Excel and pastes onto Word, including any Objects. So make sure to have your button outside print area.