Code that will delete certain tables in a document, and SUM 3 columns in the remaining Tables

[Doctorjones_md]

What I'd done is this -- I have several forms which allow the user to make
selections, and the underlying code locates the data in a separate Word
Document and inserts that Row of data into a selected table in Document1
(Active Document). There are numerous Header Fields for the data a user my
select, so I opted to create all (13) tables in Document1, with the (2)
Header Rows and (1) blank Row. I need the following:

#1. I need to write the code that will examine the (13) Tables in
Document1, and Delete all tables without Data/Rows extending beyond
Row(3) -- how could I achieve this?

#2. I need to SUM columns 3-5 of all remaining Tables and show the values
in a Summary Table -- I went here for help:
http://word.mvps.org/FAQs/TblsFldsFms/TotalColumn.htm. I set my Bookmarks
in each of the Tables with Data to sum, and entered this code in the Summary
Table ({ SUM(Table1 F:F) \# "#,##0.00;- #,##0.00;''" }) -- for some reason,
I can't get this to work -- has anyone done something similar (ie, summing
columns from multiple tables into a Summary Row in a separate table)?


NOTE: The reason I opted for this method (over the
{ =SUM(ABOVE) } formula is because there may be blank, or Text-Filled cells
in these tables

Has anyone had any luck/experience doing something like this (ie, summing
columns of data from multiple tables into a Summary Row in a separate
table)?

Here's my Code: -- Much Thanks in Advance


Option Explicit

Dim pTable1 As Table
Dim pTable2 As Table
Dim pIndex As Long
Dim pRange As Word.Range
Dim ExportDoc As Word.Document
==================================
Private Sub cbxLinen_Click()

Set ExportDoc = Documents.Open("E:\Products\Linen.doc")
Set pTable1 = ExportDoc.Tables(1)
Set pTable2 = Documents("Document1").Tables(12)

If Me.cbxLinen.Value = True Then

Me.cbxNotIncluded.Value = False

pTable1.Rows.Add BeforeRow:=pTable2.Rows(3) 'Adds a NEW row in the
destination table -- active document

For pIndex = 1 To pTable1.Columns.Count
Set pRange = pTable1.Cell(7, pIndex).Range 'The row in the Table you
want to import
pRange.End = pRange.End - 1
pRange.Copy
pTable2.Cell(3, pIndex).Range.Paste

Next

Me.cmdOK.Enabled = True

End If

ExportDoc.Close
Set ExportDoc = Nothing

End Sub

 

[Jean-Guy Marcil]

Doctorjones_md was telling us:
Doctorjones_md nous racontait que :

What I'd done is this -- I have several forms which allow the user to
make selections, and the underlying code locates the data in a
separate Word Document and inserts that Row of data into a selected
table in Document1 (Active Document). There are numerous Header
Fields for the data a user my select, so I opted to create all (13)
tables in Document1, with the (2) Header Rows and (1) blank Row. I
need the following:
#1. I need to write the code that will examine the (13) Tables in
Document1, and Delete all tables without Data/Rows extending beyond
Row(3) -- how could I achieve this?

Something like:
'_______________________________________
Dim i As Long
Dim tblsDocument As Tables

Set tblsDocument = ActiveDocument.Tables

For i = tblsDocument.Count To 1 Step -1
If tblsDocument(i).Rows.Count < 4 Then
tblsDocument(i).Delete
End If
Next
'_______________________________________

#2. I need to SUM columns 3-5 of all remaining Tables and show the
values in a Summary Table -- I went here for help:
http://word.mvps.org/FAQs/TblsFldsFms/TotalColumn.htm. I set my
Bookmarks in each of the Tables with Data to sum, and entered this
code in the Summary Table ({ SUM(Table1 F:F) \# "#,##0.00;-
#,##0.00;''" }) -- for some reason, I can't get this to work -- has
anyone done something similar (ie, summing columns from multiple
tables into a Summary Row in a separate table)?

You need to use something like:
{=SUM(Table1 D, Table2 D) \# "#,##0.00;- #,##0.00;''"}
Note that "Table1" and "Table2" are bookmark assigned to the table, not some
internal table name you can use. You have to explicitly assign the bookmark.
Now, if you delete tables in step 1, you are going to get an error if you
tried to used a predefined field code. You will have to insert that one
withy VBA as well.

So, you could have code to assign the bookmark to all relevant tables
("Tablen, where n = 1 to the total number of tables), and then insert the
appropriate field code in the summary table based on the number of bookmarks
that were inserted.

Something like this (combining the code from above):
'_______________________________________
Sub DelTablesAndAddSum()

Const strBookRoot As String = "Table"
Const strCode1 As String = "=SUM("
Const strCode2 As String = ") \# ""#,##0.00;- #,##0.00;''"""
Dim strCodeInsert As String
Dim rgeCell As Range

Dim i As Long
Dim tblsDocument As Tables

Set tblsDocument = ActiveDocument.Tables

'Here we take for granted that the summary table has more than 3 rows
For i = tblsDocument.Count To 1 Step -1
If tblsDocument(i).Rows.Count < 4 Then
tblsDocument(i).Delete
End If
Next

'If only one table left, it is the Summary table, no need to continue
If tblsDocument.Count = 1 Then Exit Sub

'Skip summary table...
For i = 1 To tblsDocument.Count - 1
ActiveDocument.Bookmarks.Add strBookRoot & CStr(i),
tblsDocument(i).Range
'Building string to add all cells in the fourth column of all tables
strCodeInsert = strCodeInsert & strBookRoot & CStr(i) & " D, "
Next

'remove extra ", " from string
strCodeInsert = Left(strCodeInsert, Len(strCodeInsert) - 2)

'Set range where to insert field code
Set rgeCell = tblsDocument(i).Cell(4, 4).Range
rgeCell.Collapse wdCollapseStart

'Insert field code in forth cell of fourth row of last table in document
'Presumed to be the summary table
ActiveDocument.Fields.Add rgeCell, wdFieldEmpty, _
strCode1 & strCodeInsert & strCode2, False

End Sub
'_______________________________________

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[doctorjones_md]

Jean,

Thank you for the code -- it work GREAT! I have a couple of additional
questions:

#1 -- If the Summary table wasn't the last Table in the Active Document,
would could you designate the specific table?

#2 -- Could I use this same method (or something similar) to copy data from
certain cells in the tables we summed? (for example --in Tables 1-13 I have
the following fields [Product Name], [Description], [Price], [Quantity],
[Delivery Cost] and I'd like to exclude the multi-lined description and
insert only the Product Name, in Cell(1,1) (Set rgeCell =
tblsDocument(i).Cell(1, 1).Range) -- Additionally, if the Quantity >1, then
I need the product of Price x Quantity

Much Thanks in Advance for all your help

Shane

 

[Tony Jollans]

Hi Shane,

Could I ask you to refrain from posting all your questions to multiple
groups. It isn't generally necessary and it just adds to the background
noise of the newsgroups. If you choose one appropriate group, anybody likely
to be able to help will see it there.

--
Enjoy,
Tony

Jean,

Thank you for the code -- it work GREAT! I have a couple of additional
questions:

#1 -- If the Summary table wasn't the last Table in the Active Document,
would could you designate the specific table?

#2 -- Could I use this same method (or something similar) to copy data
from certain cells in the tables we summed? (for example --in Tables 1-13
I have the following fields [Product Name], [Description], [Price],
[Quantity], [Delivery Cost] and I'd like to exclude the multi-lined
description and insert only the Product Name, in Cell(1,1) (Set rgeCell =
tblsDocument(i).Cell(1, 1).Range) -- Additionally, if the Quantity >1,
then I need the product of Price x Quantity

Much Thanks in Advance for all your help

Shane

Doctorjones_md was telling us:
Doctorjones_md nous racontait que :


Something like:
'_______________________________________
Dim i As Long
Dim tblsDocument As Tables

Set tblsDocument = ActiveDocument.Tables

For i = tblsDocument.Count To 1 Step -1
If tblsDocument(i).Rows.Count < 4 Then
tblsDocument(i).Delete
End If
Next
'_______________________________________


You need to use something like:
{=SUM(Table1 D, Table2 D) \# "#,##0.00;- #,##0.00;''"}
Note that "Table1" and "Table2" are bookmark assigned to the table, not
some internal table name you can use. You have to explicitly assign the
bookmark. Now, if you delete tables in step 1, you are going to get an
error if you tried to used a predefined field code. You will have to
insert that one withy VBA as well.

So, you could have code to assign the bookmark to all relevant tables
("Tablen, where n = 1 to the total number of tables), and then insert the
appropriate field code in the summary table based on the number of
bookmarks that were inserted.

Something like this (combining the code from above):
'_______________________________________
Sub DelTablesAndAddSum()

Const strBookRoot As String = "Table"
Const strCode1 As String = "=SUM("
Const strCode2 As String = ") \# ""#,##0.00;- #,##0.00;''"""
Dim strCodeInsert As String
Dim rgeCell As Range

Dim i As Long
Dim tblsDocument As Tables

Set tblsDocument = ActiveDocument.Tables

'Here we take for granted that the summary table has more than 3 rows
For i = tblsDocument.Count To 1 Step -1
If tblsDocument(i).Rows.Count < 4 Then
tblsDocument(i).Delete
End If
Next

'If only one table left, it is the Summary table, no need to continue
If tblsDocument.Count = 1 Then Exit Sub

'Skip summary table...
For i = 1 To tblsDocument.Count - 1
ActiveDocument.Bookmarks.Add strBookRoot & CStr(i),
tblsDocument(i).Range
'Building string to add all cells in the fourth column of all tables
strCodeInsert = strCodeInsert & strBookRoot & CStr(i) & " D, "
Next

'remove extra ", " from string
strCodeInsert = Left(strCodeInsert, Len(strCodeInsert) - 2)

'Set range where to insert field code
Set rgeCell = tblsDocument(i).Cell(4, 4).Range
rgeCell.Collapse wdCollapseStart

'Insert field code in forth cell of fourth row of last table in document
'Presumed to be the summary table
ActiveDocument.Fields.Add rgeCell, wdFieldEmpty, _
strCode1 & strCodeInsert & strCode2, False

End Sub
'_______________________________________

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Doctorjones_md]

Tony,

I understand what you're saying, but I'm (somewhat confused by the
directions/recommendations) I got from several MVPs in the past. Initially
(about a year ago when I was new to the groups) when I posted identical
messages to several groups, I was told by many to Cross-Post (rather than
create NEW messages in several groups) that way the replies/threads entered
in one group could be seen by all -- I've done this for the last year, and
your comment/request is the 1st I've heard against this practice since.

As I'm sure you know -- it's not easy to categorize these post -- exactly
which group they belong to, or (more importantly) which groups get the most
traffic, so it would seem to me that Cross-Posting might be beneficial in
helping folks view threads that might pertain to them.

Hey, I'm not here to make waves, only to help others, and if others can gain
from the questions I ask/responses, then I think that's the goal.

Since this is a shared environment (and majority rules in most democratic
socities), does anyone else have any comments on the BEST practice?

Shane
===================

Hi Shane,

Could I ask you to refrain from posting all your questions to multiple
groups. It isn't generally necessary and it just adds to the background
noise of the newsgroups. If you choose one appropriate group, anybody
likely to be able to help will see it there.

--
Enjoy,
Tony

Jean,

Thank you for the code -- it work GREAT! I have a couple of additional
questions:

#1 -- If the Summary table wasn't the last Table in the Active Document,
would could you designate the specific table?

#2 -- Could I use this same method (or something similar) to copy data
from certain cells in the tables we summed? (for example --in Tables
1-13 I have the following fields [Product Name], [Description], [Price],
[Quantity], [Delivery Cost] and I'd like to exclude the multi-lined
description and insert only the Product Name, in Cell(1,1) (Set rgeCell =
tblsDocument(i).Cell(1, 1).Range) -- Additionally, if the Quantity >1,
then I need the product of Price x Quantity

Much Thanks in Advance for all your help

Shane

Doctorjones_md was telling us:
Doctorjones_md nous racontait que :

What I'd done is this -- I have several forms which allow the user to
make selections, and the underlying code locates the data in a
separate Word Document and inserts that Row of data into a selected
table in Document1 (Active Document). There are numerous Header
Fields for the data a user my select, so I opted to create all (13)
tables in Document1, with the (2) Header Rows and (1) blank Row. I
need the following:
#1. I need to write the code that will examine the (13) Tables in
Document1, and Delete all tables without Data/Rows extending beyond
Row(3) -- how could I achieve this?

Something like:
'_______________________________________
Dim i As Long
Dim tblsDocument As Tables

Set tblsDocument = ActiveDocument.Tables

For i = tblsDocument.Count To 1 Step -1
If tblsDocument(i).Rows.Count < 4 Then
tblsDocument(i).Delete
End If
Next
'_______________________________________

#2. I need to SUM columns 3-5 of all remaining Tables and show the
values in a Summary Table -- I went here for help:
http://word.mvps.org/FAQs/TblsFldsFms/TotalColumn.htm. I set my
Bookmarks in each of the Tables with Data to sum, and entered this
code in the Summary Table ({ SUM(Table1 F:F) \# "#,##0.00;-
#,##0.00;''" }) -- for some reason, I can't get this to work -- has
anyone done something similar (ie, summing columns from multiple
tables into a Summary Row in a separate table)?

You need to use something like:
{=SUM(Table1 D, Table2 D) \# "#,##0.00;- #,##0.00;''"}
Note that "Table1" and "Table2" are bookmark assigned to the table, not
some internal table name you can use. You have to explicitly assign the
bookmark. Now, if you delete tables in step 1, you are going to get an
error if you tried to used a predefined field code. You will have to
insert that one withy VBA as well.

So, you could have code to assign the bookmark to all relevant tables
("Tablen, where n = 1 to the total number of tables), and then insert
the appropriate field code in the summary table based on the number of
bookmarks that were inserted.

Something like this (combining the code from above):
'_______________________________________
Sub DelTablesAndAddSum()

Const strBookRoot As String = "Table"
Const strCode1 As String = "=SUM("
Const strCode2 As String = ") \# ""#,##0.00;- #,##0.00;''"""
Dim strCodeInsert As String
Dim rgeCell As Range

Dim i As Long
Dim tblsDocument As Tables

Set tblsDocument = ActiveDocument.Tables

'Here we take for granted that the summary table has more than 3 rows
For i = tblsDocument.Count To 1 Step -1
If tblsDocument(i).Rows.Count < 4 Then
tblsDocument(i).Delete
End If
Next

'If only one table left, it is the Summary table, no need to continue
If tblsDocument.Count = 1 Then Exit Sub

'Skip summary table...
For i = 1 To tblsDocument.Count - 1
ActiveDocument.Bookmarks.Add strBookRoot & CStr(i),
tblsDocument(i).Range
'Building string to add all cells in the fourth column of all tables
strCodeInsert = strCodeInsert & strBookRoot & CStr(i) & " D, "
Next

'remove extra ", " from string
strCodeInsert = Left(strCodeInsert, Len(strCodeInsert) - 2)

'Set range where to insert field code
Set rgeCell = tblsDocument(i).Cell(4, 4).Range
rgeCell.Collapse wdCollapseStart

'Insert field code in forth cell of fourth row of last table in document
'Presumed to be the summary table
ActiveDocument.Fields.Add rgeCell, wdFieldEmpty, _
strCode1 & strCodeInsert & strCode2, False

End Sub
'_______________________________________

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Jean-Guy Marcil]

doctorjones_md was telling us:
doctorjones_md nous racontait que :

Jean,

Thank you for the code -- it work GREAT! I have a couple of
additional questions:

#1 -- If the Summary table wasn't the last Table in the Active
Document, would could you designate the specific table?

It is much easier if this summary table is last.

If not, two ways to go:
Bookmark your Summary table, and when you do the loop, check if the table
being processed by the loop is the bookmarked one, if so, ignore it.
Instead of a bookmark, you could put that summary table in a section of its
own, so when processing the tables all you would have to do is check if you
are in section "x", if so, skip the table.

But, of course, it the summary able has more than 3 rows, the loop can still
process it without any problems.

You would still need to use the bookmark or the section trick to set a
reference to the appropriate table at the end to copy the field codes tough.
Dim myTable As Table
Set myTable = ActiveDocument.Bookmark("Smmary").Range.Tables(1)
or
Set myTable = ActiveDocument.Sections(2).Range.Tables(1)

Set rgeCell = myTable.Cell(4, 4).Range

etc.

Of course, whatever you do, you will have to modify the code I posted.

#2 -- Could I use this same method (or something similar) to copy
data from certain cells in the tables we summed? (for example --in
Tables 1-13 I have the following fields [Product Name],
[Description], [Price], [Quantity], [Delivery Cost] and I'd like to
exclude the multi-lined description and insert only the Product Name,
in Cell(1,1) (Set rgeCell = tblsDocument(i).Cell(1, 1).Range) --
Additionally, if the Quantity >1, then I need the product of Price x
Quantity

I do not understand this second point.


Regarding Tony's comment.

I had not noticed the cross-posting.
You re right that we often tell people that it is better to cross-post then
to multi-post.
However, most of the time, cross-posting is not necessary.
In this case, since you re dealing with different aspects of VBA, the
vba.general group would have been enough.
(For example, this has nothing to do with userforms and these are no
beginners' questions...)

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Tony Jollans]

Thank you for the reply. Certainly cross-posting is better than
multi-posting and it can be useful to try to reach knowledgeable people in
different areas, but I don't think you reach a wider audience by hitting all
the VBA groups. If you had a question about, say, tables in VBA there might
be merit in cross posting to the tables group as well as a VBA one but I
suspect that most people who check one VBA group check them all.

I don't know what other MVPs think, and would certainly bow to majority
opinion, but I posted because I kept finding myself re-reading the same
posts. Of course the problem may be mine - I'm not competent in Windows Mail
and it may be there is some setting somewhere I have missed.

--
Enjoy,
Tony

Tony,

I understand what you're saying, but I'm (somewhat confused by the
directions/recommendations) I got from several MVPs in the past.
Initially (about a year ago when I was new to the groups) when I posted
identical messages to several groups, I was told by many to Cross-Post
(rather than create NEW messages in several groups) that way the
replies/threads entered in one group could be seen by all -- I've done
this for the last year, and your comment/request is the 1st I've heard
against this practice since.

As I'm sure you know -- it's not easy to categorize these post -- exactly
which group they belong to, or (more importantly) which groups get the
most traffic, so it would seem to me that Cross-Posting might be
beneficial in helping folks view threads that might pertain to them.

Hey, I'm not here to make waves, only to help others, and if others can
gain from the questions I ask/responses, then I think that's the goal.

Since this is a shared environment (and majority rules in most democratic
socities), does anyone else have any comments on the BEST practice?

Shane
===================

Hi Shane,

Could I ask you to refrain from posting all your questions to multiple
groups. It isn't generally necessary and it just adds to the background
noise of the newsgroups. If you choose one appropriate group, anybody
likely to be able to help will see it there.

--
Enjoy,
Tony

Jean,

Thank you for the code -- it work GREAT! I have a couple of additional
questions:

#1 -- If the Summary table wasn't the last Table in the Active Document,
would could you designate the specific table?

#2 -- Could I use this same method (or something similar) to copy data
from certain cells in the tables we summed? (for example --in Tables
1-13 I have the following fields [Product Name], [Description], [Price],
[Quantity], [Delivery Cost] and I'd like to exclude the multi-lined
description and insert only the Product Name, in Cell(1,1) (Set rgeCell
= tblsDocument(i).Cell(1, 1).Range) -- Additionally, if the Quantity >1,
then I need the product of Price x Quantity

Much Thanks in Advance for all your help

Shane
Doctorjones_md was telling us:
Doctorjones_md nous racontait que :

What I'd done is this -- I have several forms which allow the user to
make selections, and the underlying code locates the data in a
separate Word Document and inserts that Row of data into a selected
table in Document1 (Active Document). There are numerous Header
Fields for the data a user my select, so I opted to create all (13)
tables in Document1, with the (2) Header Rows and (1) blank Row. I
need the following:
#1. I need to write the code that will examine the (13) Tables in
Document1, and Delete all tables without Data/Rows extending beyond
Row(3) -- how could I achieve this?

Something like:
'_______________________________________
Dim i As Long
Dim tblsDocument As Tables

Set tblsDocument = ActiveDocument.Tables

For i = tblsDocument.Count To 1 Step -1
If tblsDocument(i).Rows.Count < 4 Then
tblsDocument(i).Delete
End If
Next
'_______________________________________

#2. I need to SUM columns 3-5 of all remaining Tables and show the
values in a Summary Table -- I went here for help:
http://word.mvps.org/FAQs/TblsFldsFms/TotalColumn.htm. I set my
Bookmarks in each of the Tables with Data to sum, and entered this
code in the Summary Table ({ SUM(Table1 F:F) \# "#,##0.00;-
#,##0.00;''" }) -- for some reason, I can't get this to work -- has
anyone done something similar (ie, summing columns from multiple
tables into a Summary Row in a separate table)?

You need to use something like:
{=SUM(Table1 D, Table2 D) \# "#,##0.00;- #,##0.00;''"}
Note that "Table1" and "Table2" are bookmark assigned to the table, not
some internal table name you can use. You have to explicitly assign the
bookmark. Now, if you delete tables in step 1, you are going to get an
error if you tried to used a predefined field code. You will have to
insert that one withy VBA as well.

So, you could have code to assign the bookmark to all relevant tables
("Tablen, where n = 1 to the total number of tables), and then insert
the appropriate field code in the summary table based on the number of
bookmarks that were inserted.

Something like this (combining the code from above):
'_______________________________________
Sub DelTablesAndAddSum()

Const strBookRoot As String = "Table"
Const strCode1 As String = "=SUM("
Const strCode2 As String = ") \# ""#,##0.00;- #,##0.00;''"""
Dim strCodeInsert As String
Dim rgeCell As Range

Dim i As Long
Dim tblsDocument As Tables

Set tblsDocument = ActiveDocument.Tables

'Here we take for granted that the summary table has more than 3 rows
For i = tblsDocument.Count To 1 Step -1
If tblsDocument(i).Rows.Count < 4 Then
tblsDocument(i).Delete
End If
Next

'If only one table left, it is the Summary table, no need to continue
If tblsDocument.Count = 1 Then Exit Sub

'Skip summary table...
For i = 1 To tblsDocument.Count - 1
ActiveDocument.Bookmarks.Add strBookRoot & CStr(i),
tblsDocument(i).Range
'Building string to add all cells in the fourth column of all tables
strCodeInsert = strCodeInsert & strBookRoot & CStr(i) & " D, "
Next

'remove extra ", " from string
strCodeInsert = Left(strCodeInsert, Len(strCodeInsert) - 2)

'Set range where to insert field code
Set rgeCell = tblsDocument(i).Cell(4, 4).Range
rgeCell.Collapse wdCollapseStart

'Insert field code in forth cell of fourth row of last table in
document
'Presumed to be the summary table
ActiveDocument.Fields.Add rgeCell, wdFieldEmpty, _
strCode1 & strCodeInsert & strCode2, False

End Sub
'_______________________________________

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Doctorjones_md]

Sounds good Jean -- I'll heed your advice (and Tony's request).

On my 2nd point (from my ealier post) -- I'd like to include the Product
Name in the Summary Line as well, and if the Quantity is greater than 1, the
SUM that we did earlier would need to factor that in.

Say that for Linen in Row 4 of Table(12), if the Price is $35.00, and the
Quantity is 2, what is the best way to ensure that what gets summed for this
row is $70.00 (rather than $35.00)? Would it be best for me to Sum the
product of Price & Quantity on each Row separately, and then just use the
code you've given me to sum all tables? Is this any clearer -- your
thoughts on how to approach this?

Thanks again!

Shane

doctorjones_md was telling us:
doctorjones_md nous racontait que :

Jean,

Thank you for the code -- it work GREAT! I have a couple of
additional questions:

#1 -- If the Summary table wasn't the last Table in the Active
Document, would could you designate the specific table?

It is much easier if this summary table is last.

If not, two ways to go:
Bookmark your Summary table, and when you do the loop, check if the table
being processed by the loop is the bookmarked one, if so, ignore it.
Instead of a bookmark, you could put that summary table in a section of
its own, so when processing the tables all you would have to do is check
if you are in section "x", if so, skip the table.

But, of course, it the summary able has more than 3 rows, the loop can
still process it without any problems.

You would still need to use the bookmark or the section trick to set a
reference to the appropriate table at the end to copy the field codes
tough.
Dim myTable As Table
Set myTable = ActiveDocument.Bookmark("Smmary").Range.Tables(1)
or
Set myTable = ActiveDocument.Sections(2).Range.Tables(1)

Set rgeCell = myTable.Cell(4, 4).Range

etc.

Of course, whatever you do, you will have to modify the code I posted.

#2 -- Could I use this same method (or something similar) to copy
data from certain cells in the tables we summed? (for example --in
Tables 1-13 I have the following fields [Product Name],
[Description], [Price], [Quantity], [Delivery Cost] and I'd like to
exclude the multi-lined description and insert only the Product Name,
in Cell(1,1) (Set rgeCell = tblsDocument(i).Cell(1, 1).Range) --
Additionally, if the Quantity >1, then I need the product of Price x
Quantity

I do not understand this second point.


Regarding Tony's comment.

I had not noticed the cross-posting.
You re right that we often tell people that it is better to cross-post
then to multi-post.
However, most of the time, cross-posting is not necessary.
In this case, since you re dealing with different aspects of VBA, the
vba.general group would have been enough.
(For example, this has nothing to do with userforms and these are no
beginners' questions...)

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Doctorjones_md]

Point taken -- I'll try to limit my cross-posting to only the applicable
groups (based on the scope of the project) -- thanks for your input!

Shane

Thank you for the reply. Certainly cross-posting is better than
multi-posting and it can be useful to try to reach knowledgeable people in
different areas, but I don't think you reach a wider audience by hitting
all the VBA groups. If you had a question about, say, tables in VBA there
might be merit in cross posting to the tables group as well as a VBA one
but I suspect that most people who check one VBA group check them all.

I don't know what other MVPs think, and would certainly bow to majority
opinion, but I posted because I kept finding myself re-reading the same
posts. Of course the problem may be mine - I'm not competent in Windows
Mail and it may be there is some setting somewhere I have missed.

--
Enjoy,
Tony

Tony,

I understand what you're saying, but I'm (somewhat confused by the
directions/recommendations) I got from several MVPs in the past.
Initially (about a year ago when I was new to the groups) when I posted
identical messages to several groups, I was told by many to Cross-Post
(rather than create NEW messages in several groups) that way the
replies/threads entered in one group could be seen by all -- I've done
this for the last year, and your comment/request is the 1st I've heard
against this practice since.

As I'm sure you know -- it's not easy to categorize these post -- exactly
which group they belong to, or (more importantly) which groups get the
most traffic, so it would seem to me that Cross-Posting might be
beneficial in helping folks view threads that might pertain to them.

Hey, I'm not here to make waves, only to help others, and if others can
gain from the questions I ask/responses, then I think that's the goal.

Since this is a shared environment (and majority rules in most democratic
socities), does anyone else have any comments on the BEST practice?

Shane
===================

Hi Shane,

Could I ask you to refrain from posting all your questions to multiple
groups. It isn't generally necessary and it just adds to the background
noise of the newsgroups. If you choose one appropriate group, anybody
likely to be able to help will see it there.

--
Enjoy,
Tony

message Jean,

Thank you for the code -- it work GREAT! I have a couple of additional
questions:

#1 -- If the Summary table wasn't the last Table in the Active
Document, would could you designate the specific table?

#2 -- Could I use this same method (or something similar) to copy data
from certain cells in the tables we summed? (for example --in Tables
1-13 I have the following fields [Product Name], [Description],
[Price], [Quantity], [Delivery Cost] and I'd like to exclude the
multi-lined description and insert only the Product Name, in Cell(1,1)
(Set rgeCell = tblsDocument(i).Cell(1, 1).Range) -- Additionally, if
the Quantity >1, then I need the product of Price x Quantity

Much Thanks in Advance for all your help

Shane
Doctorjones_md was telling us:
Doctorjones_md nous racontait que :

What I'd done is this -- I have several forms which allow the user to
make selections, and the underlying code locates the data in a
separate Word Document and inserts that Row of data into a selected
table in Document1 (Active Document). There are numerous Header
Fields for the data a user my select, so I opted to create all (13)
tables in Document1, with the (2) Header Rows and (1) blank Row. I
need the following:
#1. I need to write the code that will examine the (13) Tables in
Document1, and Delete all tables without Data/Rows extending beyond
Row(3) -- how could I achieve this?

Something like:
'_______________________________________
Dim i As Long
Dim tblsDocument As Tables

Set tblsDocument = ActiveDocument.Tables

For i = tblsDocument.Count To 1 Step -1
If tblsDocument(i).Rows.Count < 4 Then
tblsDocument(i).Delete
End If
Next
'_______________________________________

#2. I need to SUM columns 3-5 of all remaining Tables and show the
values in a Summary Table -- I went here for help:
http://word.mvps.org/FAQs/TblsFldsFms/TotalColumn.htm. I set my
Bookmarks in each of the Tables with Data to sum, and entered this
code in the Summary Table ({ SUM(Table1 F:F) \# "#,##0.00;-
#,##0.00;''" }) -- for some reason, I can't get this to work -- has
anyone done something similar (ie, summing columns from multiple
tables into a Summary Row in a separate table)?

You need to use something like:
{=SUM(Table1 D, Table2 D) \# "#,##0.00;- #,##0.00;''"}
Note that "Table1" and "Table2" are bookmark assigned to the table,
not some internal table name you can use. You have to explicitly
assign the bookmark. Now, if you delete tables in step 1, you are
going to get an error if you tried to used a predefined field code.
You will have to insert that one withy VBA as well.

So, you could have code to assign the bookmark to all relevant tables
("Tablen, where n = 1 to the total number of tables), and then insert
the appropriate field code in the summary table based on the number of
bookmarks that were inserted.

Something like this (combining the code from above):
'_______________________________________
Sub DelTablesAndAddSum()

Const strBookRoot As String = "Table"
Const strCode1 As String = "=SUM("
Const strCode2 As String = ") \# ""#,##0.00;- #,##0.00;''"""
Dim strCodeInsert As String
Dim rgeCell As Range

Dim i As Long
Dim tblsDocument As Tables

Set tblsDocument = ActiveDocument.Tables

'Here we take for granted that the summary table has more than 3 rows
For i = tblsDocument.Count To 1 Step -1
If tblsDocument(i).Rows.Count < 4 Then
tblsDocument(i).Delete
End If
Next

'If only one table left, it is the Summary table, no need to continue
If tblsDocument.Count = 1 Then Exit Sub

'Skip summary table...
For i = 1 To tblsDocument.Count - 1
ActiveDocument.Bookmarks.Add strBookRoot & CStr(i),
tblsDocument(i).Range
'Building string to add all cells in the fourth column of all
tables
strCodeInsert = strCodeInsert & strBookRoot & CStr(i) & " D, "
Next

'remove extra ", " from string
strCodeInsert = Left(strCodeInsert, Len(strCodeInsert) - 2)

'Set range where to insert field code
Set rgeCell = tblsDocument(i).Cell(4, 4).Range
rgeCell.Collapse wdCollapseStart

'Insert field code in forth cell of fourth row of last table in
document
'Presumed to be the summary table
ActiveDocument.Fields.Add rgeCell, wdFieldEmpty, _
strCode1 & strCodeInsert & strCode2, False

End Sub
'_______________________________________

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Jean-Guy Marcil]

Doctorjones_md was telling us:
Doctorjones_md nous racontait que :

Sounds good Jean -- I'll heed your advice (and Tony's request).

On my 2nd point (from my ealier post) -- I'd like to include the
Product Name in the Summary Line as well, and if the Quantity is
greater than 1, the SUM that we did earlier would need to factor that
in.
Say that for Linen in Row 4 of Table(12), if the Price is $35.00, and
the Quantity is 2, what is the best way to ensure that what gets
summed for this row is $70.00 (rather than $35.00)? Would it be best
for me to Sum the product of Price & Quantity on each Row separately,
and then just use the code you've given me to sum all tables? Is
this any clearer -- your thoughts on how to approach this?

What I would do is have an extra column in each table to have the total for
each item and use that column with the code I first suggested.
A B C D
Linen 35.00 2 70.00
Wool 40.00 4 160.00
Silk 50.00 3 150.00

And use column D in the code.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org