Jump to content

Create Account

Paste,format, insert new tables via macro in word document

  • Please log in to reply



    New Member

  • Member
  • Pip
  • 1 posts
Hello All,

I am a newbie to Word VBA macros Programming.
I have a probelem in copying data from Excel sheets and placing them in new tables in a word document. The requirement is as follows:
I need to program a macro that opens an Excel document stored at a particular location. Go to a sheet in the file. Copy a specific range of data inside the sheet, Place the data in a new table inside word document, the table should be autoformattted to type Word table colourful2 and contents of the table shall be autofit to window.

Following is the macro
Sub Macro1()
' Macro1 Macro

Dim xlApp As Object
Dim St As String
Dim i As Long
Dim ret As Long
Dim count As Integer
Dim myRg As Range
Dim myTbl As Table

' Open the Excel sheet saved in a particular location
' For example I have stored the table in D drive, you need to add the path of exact file location and also type
Set xlApp = GetObject("D:test.xlsx")

' Loop Over the number of tables you want to insert
' Here I have taken a count of 3 as test
For count = 1 To 3

' Go to the excel sheet
With xlApp.Sheets("CmdConfigurate")

' Loop over the specified range where you want to copy inside the sheet
' In every sheet I have the word Size as end row
For i = 5 To 100
If xlApp.Sheets("CmdConfigurate").Cells(i, 1).Value = "Size" Then ret = i
Next i
End With

' Copy the specified range

xlApp.Sheets("CmdConfigurate").Range("A5:G" & ret).Copy

Selection.PasteExcelTable _
LinkedToExcel:=False, _
WordFormatting:=True, _

With ActiveDocument.Tables(ActiveDocument.Tables.count)

.AutoFormat Format:=wdTableFormatColorful2
.AutoFitBehavior wdAutoFitContent

End With

Next count
Set xlApp = Nothing
End Sub

When I run this macro I am getting all the data in one table, what I want is to place the data in three seperate tables, please correct the macro.
In the attachment I an sending the excel file

Thanks and Kind Regards,
  • 0


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Malware Removal How to Guides Windows 7 System Building Download Files Register welcome

Never used a forum? Learn how.