Here i have a vba code which currently only copy a single table from excel into a microsoft word document. What I really want to do is, I have 6 different excel sheets with Tables and I want to just copy all these tables into a microsoft word document. How can i modify this code to do this? or what is the correct approach to do this??
Code:
Sub ExportDataToWord()
'Worksheets("Val Balance Sheet").Range("A1:D22").Copy
Dim wdapp, wddoc As Object
Dim t As Word.Range 'the new table in Word as a range
Dim strdocname As String
On Error Resume Next
Set wdapp = GetObject(, "Word.Application")
If Err.Number = 429 Then
Err.Clear
Set wdapp = CreateObject("Word.Application")
End If
wdapp.Visible = True
strdocname = "C:\Users\ako\report.docx"
If Dir(strdocname) = "" Then
MsgBox "The file " & strdocname & vbCrLf & "was not found " & vbCrLf & "C:\Users\ako.", vbExclamation, "The document does not exist."
Exit Sub
End If
wdapp.Activate
Set wddoc = wdapp.documents.Open(strdocname)
If wddoc Is Nothing Then Set wddoc = wdapp.DocumentOpen(strdocnme)
Set t = wdDoc.Content 'set the range in Word
t.Paste 'paste in the table
With t 'working with the table range
.Style = "GreenBar" 'set the style created for the table
'we can use the range object to do some more formatting
'here, I'm matching the table with using the Excel range's properties
.Tables(1).Columns.SetWidth (rng.Width / rng.Columns.Count), wdAdjustSameWidth
End With 'paste in the table
wddoc.Activate
'wddoc.Range.Paste
wddoc.Save
wdapp.Quit
Set wddoc = Nothing
Set wdapp = Nothing
Application.CutCopyMode = False
End Sub