Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / November 2006

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Doctorjones_md - 14 Nov 2006 14:39 GMT
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 - 14 Nov 2006 16:21 GMT
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
[quoted text clipped - 6 lines]
> 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:
[quoted text clipped - 4 lines]
> 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:D, Table2 D: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: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
'_______________________________________

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

doctorjones_md - 15 Nov 2006 01:22 GMT
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 :
[quoted text clipped - 93 lines]
> End Sub
> '_______________________________________
Tony Jollans - 15 Nov 2006 10:08 GMT
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.

Signature

Enjoy,
Tony

> Jean,
>
[quoted text clipped - 112 lines]
>> End Sub
>> '_______________________________________
Doctorjones_md - 15 Nov 2006 14:05 GMT
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,
>
[quoted text clipped - 119 lines]
>>> End Sub
>>> '_______________________________________
Tony Jollans - 15 Nov 2006 18:13 GMT
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.

Signature

Enjoy,
Tony

> Tony,
>
[quoted text clipped - 144 lines]
>>>> End Sub
>>>> '_______________________________________
Doctorjones_md - 15 Nov 2006 18:33 GMT
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
[quoted text clipped - 157 lines]
>>>>> End Sub
>>>>> '_______________________________________
Jean-Guy Marcil - 15 Nov 2006 16:22 GMT
doctorjones_md was telling us:
doctorjones_md nous racontait que :

> Jean,
>
[quoted text clipped - 3 lines]
> #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
[quoted text clipped - 4 lines]
> 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...)

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

Doctorjones_md - 15 Nov 2006 18:28 GMT
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 :
[quoted text clipped - 54 lines]
> (For example, this has nothing to do with userforms and these are no
> beginners' questions...)
Jean-Guy Marcil - 16 Nov 2006 02:41 GMT
Doctorjones_md was telling us:
Doctorjones_md nous racontait que :

> Sounds good Jean -- I'll heed your advice (and Tony's request).
>
[quoted text clipped - 8 lines]
> 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.

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org


Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.