MS Office Forum / Word / Programming / November 2006
Code that will delete certain tables in a document, and SUM 3 columns in the remaining Tables
|
|
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
|
|
|