How to do a macro that can create such table format?

[thedark123]

I just copy over some values from excel and wanted to place all these values into position with the table format as shown:

How do i do a macro to create this kind of formatting, so it will be like a standard table template.

http://i6.photobucket.com/albums/y226/thedark123/screenshot3.gif

[fumei]

It would FAR FAR easier to make a Word template file with a blank (but formatted) table. Then you have a table already formatted. You just need to put in the data.

Trying to build and format a table certainly can be done by code. But again...it is much much easier to simply have the table there, and fill it in.

[thedark123]

ok if that is the case, I will create this table format beforehand first, but how do I paste the data that I copied from excel to its respective positions in the word table?

Have a sample code or something that could help?

This is the range of cells I copied from excel that are suppose to fall in the correct place in my word table

http://i6.photobucket.com/albums/y226/thedark123/test12345.gif

[fumei]

Hmmm. Look up tables in VBA Help. There ar emany examples.

Essentially - especially if you are going to do a number of actions on it - you can make a Table object of the table.Dim oTable As Word Table
Set oTable = ActiveDocument.Tables(2)
With oTable
.Cell(1,2).Range.Text = whatever
.Cell(1,3).Range.Text = some other stuff
.Cell(1,4).Range.Text = even more stuff
End With
Set oTable = NothingCreates a Table object ofthe second table in the document. It then simply makes the cell range a piece of information (text). The cell can be filled directly - by index number. .Cell(row_num, col_num)

[thedark123]

fumei I have done the coding to be something like this, do you mind testing it out?

1) how do I make the table border appear?


2)
These 2 text fields are shown in the screenshot of my first thread above:
1. TD-VSC-MA001-A02A08.01/SCENARIO 1
2. Other Information:
How do I create the position or to say the space to place all these 2 values there?


Sub ExampleInsertTable()
Dim oTbl As Table

' in case the selection is extended,
' collapse it
Selection.Collapse wdCollapseStart

Set oTbl = ActiveDocument.Tables.Add( _
Range:=Selection.Range, _
NumRows:=11, NumColumns:=2)
oTbl.Split(BeforeRow:=oTbl.Rows(11)).Shading _
.Texture = wdTextureNone


' put text in the table (in this example,
' just the header row)
With oTbl
.Cell(1, 1).Range.Text = "Test Data ID:"
.Cell(2, 1).Range.Text = "Scenario ID:"
.Cell(3, 1).Range.Text = "Tester:"
.Cell(4, 1).Range.Text = "Date(DD/MM/YYYY):"
.Cell(5, 1).Range.Text = "Results:"
.Cell(6, 1).Range.Text = "Trouble Ticket No:"
.Cell(8, 1).Range.Text = "Test Condition:"
.Cell(9, 1).Range.Text = "Rule ID:"
.Cell(10, 1).Range.Text = "Rule Description:"
.Cell(1, 2).Range.Text = "Value"
.Cell(2, 2).Range.Text = "Value"
.Cell(3, 2).Range.Text = "Value"
.Cell(4, 2).Range.Text = "Value"
.Cell(5, 2).Range.Text = "Value"
.Cell(6, 2).Range.Text = "Value"
.Cell(8, 2).Range.Text = "Value"
.Cell(9, 2).Range.Text = "Value"
.Cell(10, 2).Range.Text = "Value"
End With
' you can do formatting, too
With oTbl
.Rows(1).Range.Bold = True
.Rows(2).Range.Bold = True
.Rows(3).Range.Bold = True
.Rows(4).Range.Bold = True
.Rows(5).Range.Bold = True
.Rows(6).Range.Bold = True
.Rows(8).Range.Bold = True
.Rows(9).Range.Bold = True
.Rows(10).Range.Bold = True
.Cell(1, 1).Shading.BackgroundPatternColor = wdColorGray15
.Cell(2, 1).Shading.BackgroundPatternColor = wdColorGray15
.Cell(3, 1).Shading.BackgroundPatternColor = wdColorGray15
.Cell(4, 1).Shading.BackgroundPatternColor = wdColorGray15
.Cell(5, 1).Shading.BackgroundPatternColor = wdColorGray15
.Cell(6, 1).Shading.BackgroundPatternColor = wdColorGray15
.Cell(8, 1).Shading.BackgroundPatternColor = wdColorGray15
.Cell(9, 1).Shading.BackgroundPatternColor = wdColorGray15
.Cell(10, 1).Shading.BackgroundPatternColor = wdColorGray15
.Rows(1).HeadingFormat = True
.Rows(2).HeadingFormat = True
.Rows(3).HeadingFormat = True
.Rows(4).HeadingFormat = True
.Rows(5).HeadingFormat = True
.Rows(6).HeadingFormat = True
.Rows(8).HeadingFormat = True
.Rows(9).HeadingFormat = True
.Rows(10).HeadingFormat = True
End With
' finally, put the cursor after the table
oTbl.Range.Select
Selection.Collapse wdCollapseEnd
End Sub

[thedark123]

Fumei when I run the above coding, it canot be run in excel?
Because I need to integrate this table formatting code with my current code, but when I paste this piece of coding to my current code, it does not print anything at all

Or else can I create a new word document just for the template and paste the table creating macro there and start to populate the table using that new word document as a reference to create my table later on, anyway to go around it?

[fumei]

Please read the posts. Make the table in a template file. Format it the way you want it. Have your code in Excel make a new document from the template. The table will be there - properly formatted.

Put the data in the cells.

Sorry...but if you are persisting in creating and formatting the table dynamically...then, go right ahead.

How do I create the position or to say the space to place all these 2 values there?I have not any odea at all what you are asking here.
Because I need to integrate this table formatting code with my current code,No...actually you do not. Use a template.
when I paste this piece of coding to my current code, it does not print anything at allWhy would it? Is it supposed to read your mind? There is no print instruction in the code.

[thedark123]

Have your code in Excel make a new document from the template.

How to do that?

[thedark123]

I run this code and it shows me this table, why for the second table the cells are blank?

Can I use mailmerge instead to do all this? I have create a new word template for the table and I am going to get the values from excel and populate the table in it

Sub ExampleInsertTable()
Dim oTbl As Table

' in case the selection is extended,
' collapse it
Selection.Collapse wdCollapseStart

Set oTbl = ActiveDocument.Tables.Add( _
Range:=Selection.Range, _
NumRows:=9, NumColumns:=2)
'oTbl.Split(BeforeRow:=oTbl.Rows(11)).Shading _
.Texture = wdTextureNone

ActiveDocument.Tables(1).Rows(6).Select
Selection.SplitTable

' put text in the table (in this example,
' just the header row)
With oTbl
.Cell(1, 1).Range.Text = "Test Data ID:"
.Cell(2, 1).Range.Text = "Scenario ID:"
.Cell(3, 1).Range.Text = "Tester:"
.Cell(4, 1).Range.Text = "Date(DD/MM/YYYY):"
.Cell(5, 1).Range.Text = "Results:"
.Cell(6, 1).Range.Text = "Trouble Ticket No:"
.Cell(8, 1).Range.Text = "Test Condition:"
.Cell(9, 1).Range.Text = "Rule ID:"
.Cell(10, 1).Range.Text = "Rule Description:"
.Cell(1, 2).Range.Text = "Value"
.Cell(2, 2).Range.Text = "Value"
.Cell(3, 2).Range.Text = "Value"
.Cell(4, 2).Range.Text = "Value"
.Cell(5, 2).Range.Text = "Value"
.Cell(6, 2).Range.Text = "Value"
.Cell(8, 2).Range.Text = "Value"
.Cell(9, 2).Range.Text = "Value"
.Cell(10, 2).Range.Text = "Value"
End With
' you can do formatting, too
With oTbl
.Rows(1).Range.Bold = True
.Rows(2).Range.Bold = True
.Rows(3).Range.Bold = True
.Rows(4).Range.Bold = True
.Rows(5).Range.Bold = True
.Rows(6).Range.Bold = True
.Rows(8).Range.Bold = True
.Rows(9).Range.Bold = True
.Rows(10).Range.Bold = True
.Cell(1, 1).Shading.BackgroundPatternColor = wdColorGray15
.Cell(2, 1).Shading.BackgroundPatternColor = wdColorGray15
.Cell(3, 1).Shading.BackgroundPatternColor = wdColorGray15
.Cell(4, 1).Shading.BackgroundPatternColor = wdColorGray15
.Cell(5, 1).Shading.BackgroundPatternColor = wdColorGray15
.Cell(6, 1).Shading.BackgroundPatternColor = wdColorGray15
.Cell(8, 1).Shading.BackgroundPatternColor = wdColorGray15
.Cell(9, 1).Shading.BackgroundPatternColor = wdColorGray15
.Cell(10, 1).Shading.BackgroundPatternColor = wdColorGray15
.Rows(1).HeadingFormat = True
.Rows(2).HeadingFormat = True
.Rows(3).HeadingFormat = True
.Rows(4).HeadingFormat = True
.Rows(5).HeadingFormat = True
.Rows(6).HeadingFormat = True
.Rows(8).HeadingFormat = True
.Rows(9).HeadingFormat = True
.Rows(10).HeadingFormat = True
End With
' finally, put the cursor after the table
oTbl.Range.Select
Selection.Collapse wdCollapseEnd
End Sub

[fumei]

You are still creating a table. So...since that is not what I recommended, I really have nothing more to say. This is willfullness on your part. I have no idea why you want to do this.

I will say it again. But for the last time.

Make a document template with whatever tables you want, formatted whatever way you want.

Leave the tables empty.

Use your code to create a new document from the template. There is your empty table. Put the information into the cells you want.

Period. Done. No code to create a table. No splitting of tables (ugh yuck). Blah blah blah.

Other comments:

You use Set oTbl. If you set an object always destroy it when you are done. ALWAYS. ALWAYS. You are not doing so.

In fact, I would strongly suggest doing a little bit of research into objects. What they are. How do they work. You ask:
I run this code and it shows me this table, why for the second table the cells are blank?Why would the cells NOT be blank? You are doing nothing to them. You have ZERO code that address that second table. None. You are doing things with oTbl...which is the first table. How can oTbl be both the first table and the second table. Hmmmmm????? Ever think about that? Well gosh...it can't. You split the table - and I have no idea why you do. Seems kind of dumb to me. You set oTbl to be a table object...and it is still a table object. When you split it...well gee...there is a new table isn't there? Did you think that oTbl somehow becomes two objects? Either make oTbl the second table - which could be done, or have another table object.

Better yet - don't make any bloody table objects at all, and don't do this. Make a template and use it.

You still persist on putting extraneous stuff in your posts.

I am finding this far too boring now, and I certainly do not feel you are listening at all. So I am no longer going to look at this thread. Good luck.

[thedark123]

fumei I now doing your way of doing things now

Dim wdApp As Word.Application, wdDoc As Word.Document, ws As Worksheet
Application.ScreenUpdating = False
Application.StatusBar = "Creating new document..."

Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Add
Dim totalcolumn As Integer
Dim MyFol As String
Dim newfol As String
newfol = Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) - 4)
MkDir (newfol)
MyFol = newfol & "\"

On Error Resume Next

I have just created a word template call table.dot with all my tables and formatting inside.
I need to add in this code right?

Set myDoc = wdApp.Documents.Add(Template:="T:\Templates\test\table.dot")

so that it will create a new word document base on the template and populate its table contents later on?


Sorry for not listening to your suggestion in the start..
But it is a good start..

[thedark123]

This will be the last coding...

[fumei]

Again - I have no idea what you are really doing; You have that first bunch of code - and I don't know why you posted it. is it relevant? In you have:Set wdDoc = wdApp.Documents.Add
Then you have:Set myDoc = wdApp.Documents.Add(Template:="T:\Templates\test\table.dot") So now you have TWO document objects. What is going on with these. Why the first one?

[thedark123]

Going to replace this


Set wdDoc = wdApp.Documents.Add


with this:


Set myDoc = wdApp.Documents.Add(Template:="T:\Templates\test\table.dot")


ok i am using bookmark to place the data to its correct position is it ok?
possible to loop the table in the word template coz I got specfiy bookmark in the table already...if i loop the table will the bookmark be gone?

[fumei]

OK inthedark, you have posted enough on your little project to get yourself finished...if you really paid attention.

However, since you do not pay attention, I am really going to ignore your threads from now on. I will leave you with this.

1. your posts are unclear and disjointed. If you want to get some support from people here you must be clear, and keep your posts to ONE solveable subject.

2. listen to what people are suggesting. Ignoring people and doing what ever you think is what you want to do will simply get what you are now going to get from me. Ignoring you.

3. because I can't help myself, I will answer your question. Although I must say this first.

Why are you using bookmarks? Did I suggest bookmarks. Your question (original question...sort of) was a table and how to get information into cells. As I posted a number of times, you can simply put the information right directly into the cells. There is no need for bookmarks.

However, you CAN use bookmarks. And yes, normally using the standard way of putting stuff into bookmarks, the bookmarks commonly will be gone.

a) there is a easy way around that. Guess what? I am not going to say what it is.

b) it is only a real problem, if you intend to repeat it - that is, put the information into the bookmarks more than once. If you are just putting it in once - who cares if the bookmark is gone?

It is only a real problem if you are not using templates correctly. Considering your posts, I would conjecture that it is likely you are not going to be using templates correctly.

Bye-bye. I am not even going to wish you good luck.

[thedark123]

Solved thanks ^^

[mdmackillop]

The purpose of this site is not just to assist the questioner, but to provide information for others who may have a similar problem. If you have a solution to your question, it would be appropriate to post it here for the benefit of all.
Regards
MD