Jump to content

Welcome to Geeks to Go - Register now for FREE
Geeks To Go is a helpful hub, where thousands of friendly volunteers serve up answers and support. Get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more. This message and all ads will be removed once you have signed in.
Create an Account Login to 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


Similar Topics: Paste,format, insert new tables via macro in word document     x

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On: