Forum Discussion

Smokemover's avatar
Smokemover
Copper Contributor
Jul 05, 2019

Macro help. Importing Table into Word

Hello Everyone,

 

I have recently started using macros in excel as I wanted to streamline some of my business. I have created a word template that is a generic proposal document for my business. I am now trying to create a macro in my excel document that will automatically create a new word document from my template and populate some of the areas. I have managed to create the word document and get some of the text replaced with values from my excel document. I can not, however, automatically import a table into the document. I have followed online tutorials and adapted the macro as best I can but I can not get any further. The macro does not through up any faults when it runs and will complete. However, it just does not import the table over at all no matter what I change. I have tried to make it import based on Paragraph location, Page location and bookmark location and nothing seems to work.

 

Bellow is the macro that I am currently running:

 

Sub CreateProposal()

Dim wApp As Word.Application
Dim wDoc As Word.Document
Dim tbl As Excel.Range
Dim WordTable As Word.Table
Set wApp = CreateObject("Word.Application")
wApp.Visible = True


'create new proposal document
Set wDoc = wApp.Documents.Add(Template:="C:\Users\Edward Baker\Documents\Custom Office Templates\Baiceir - Proposal Template2.dotx", NewTemplate:=False, DocumentType:=0)

'auto input total project value


With wDoc
.Application.Selection.Find.Text = "<<system cost>>"
.Application.Selection.Find.Execute
.Application.Selection = Range("C20")
.Application.Selection.EndOf

'Code optimisation
Application.ScreenUpdating = False
Application.EnableEvents = False

'Copy Range from Excel
Set tbl = ThisWorkbook.Worksheets("Equipment Table").ListObjects("Table1").Range

'Create an Instance of MS Word
On Error Resume Next

'Is MS Word already opened?
Set WordApp = GetObject(Class:="Word.Application")

'Copy Excel Table Range
tbl.Copy

'Paste Table into MS Word
myDoc.Paragraphs(1).Range.PasteExcelTable _
LinkedToExcel:=False, _
WordFormatting:=True, _
RTF:=False

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

EndRoutine:
'Optimize Code
Application.ScreenUpdating = True
Application.EnableEvents = True

'Clear The Clipboard
Application.CutCopyMode = False


End With

End Sub

 

Any help would be greatly appreciated in this matter.

 

Thank you.

excel
Macros and VBA
office 365
training

1 Reply

  • Berndvbatanker's avatar
    Berndvbatanker
    Iron Contributor
    Jul 05, 2019

    Smokemover 

    Hi,

    try this.

     

    Sub ExcelZelleInWordDokument()
    Dim wsApp As Object
    Dim myDoc As Object
    Dim myRange As Object
    Dim myTab As Object
    Dim Vardat As Variant
    Dim intZeile As Integer
    Dim intSpalte As Integer

    Set wdApp = CreateObject("Word.application")

    With wdApp
    .Visible = True
    .WindowState = wdWindowStateMaximize
    End With

    Set myDoc = wdApp.Documents.Add

    Vardat = Tabelle1.Range("A1:D10").Value
    Set myRange = myDoc.Range(0, 0)

    Set myTab = myDoc.Tables.Add _
    (Range:=myRange, _
    NumRows:=UBound(Vardat, 1), _
    NumColumns:=UBound(Vardat, 2))

    For intZeile = 1 To UBound(Vardat, 1)

    For intSpalte = 1 To UBound(Vardat, 2)
    myTab.Cell(intZeile, intSpalte).Range.Text = Vardat(intZeile, intSpalte)
    Next intSpalte

    Next intZeile


    End Sub

     

    Best regards

    Bernd

    https://vba-tanker.com/

Resources