Macro help. Importing Table into Word

[Smokemover]

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.

[#2]

@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

vba-Tanker.com - a databse full of usefull macros