Jump to content

Welcome Guest 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. Feel free to browse the site as a guest. However, you must log in to reply to existing topics or start a new topic of your own, and enjoy all this forum has to offer. Additionally, if you can assist another member by sharing your knowledge, please post a reply! Best of all - Registration and all assistance, is FREE! Learn more about How it Works. Infected? Malware Cleaning Guide. What are you waiting for?
Create an Account Login to Account

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


  • Please log in to reply

#1
rana101

rana101

    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, _
RTF:=True

With ActiveDocument.Tables(ActiveDocument.Tables.count)

.Columns.AutoFit
.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,
Naidu.
  • 0

Advertisement




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:

featured