Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Other Office > Word VBA
|
Word VBA Discuss using VBA to program Word.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Word VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 13th, 2011, 07:51 AM
Registered User
 
Join Date: Nov 2011
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Paste, format, Insert new Tables

Paste format insert new tables
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.
 
Old November 17th, 2011, 08:40 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Naidu Garu

The data might get populated to the same table as the code doesnot add the table

You need to add the following line

Code:
ActiveDocument.Tables.Add
before

Code:
With ActiveDocument.Tables(ActiveDocument.Tables.count)
]
HTH

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old November 17th, 2011, 03:02 PM
Registered User
 
Join Date: Nov 2011
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello Shasur,
I have solved the issue. Anyways thanks for posting





Similar Threads
Thread Thread Starter Forum Replies Last Post
how Insert record into two tables mfarooqw ASP.NET 1.0 and 1.1 Basics 2 July 10th, 2007 05:38 PM
Insert Data into two tables Lofa PHP Databases 4 July 7th, 2007 10:36 PM
how to insert values in tables using php?? asalaam SQL Server 2000 1 March 27th, 2007 11:23 AM
Insert into Multiple Tables bmalex1 Beginning PHP 0 February 6th, 2006 12:45 PM
Insert into different Tables cs9896 SQL Language 2 September 16th, 2003 03:52 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.