Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-22-2016, 02:04 AM
Grantsub Grantsub is offline Word table auto text alignment vs cell height Windows 10 Word table auto text alignment vs cell height Office 2013
Novice
Word table auto text alignment vs cell height
 
Join Date: Aug 2016
Posts: 6
Grantsub is on a distinguished road
Default Word table auto text alignment vs cell height

Word table auto text alignment vs. cell height



I would like to use a VBA to clean up a word document table by aligning text in the each by a set of criteria eg height of cell = 6pt use macro one and if greater than 6 points use marco 2
I have been able to write the script separately but can think on away to join the two Marcos together so I physical donít have to select the macro on each line or groups of line

At the moment I just selected Marco 1 or Macro2 depending on looking at each line this is very time consuming for 140 tables in a work doc

So the outcome I want is.
Run the Marco and it starts where the cursor is and continues through all the tables in the document highlighted with cursor the information on each line in the table is formatted and aligned determine to the height of the cell

Cell single height e.g. 6 point

Selection.SelectCell
Selection.Font.Name = "Arial"
Selection.Font.ColorIndex = wdBlack
Selection.Font.Size = 11
Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
Selection.Cells.VerticalAlignment = wdCellAlignVerticalTop
Options.DefaultHighlightColorIndex = wdYellow
Selection.Range.HighlightColorIndex = wdYellow
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.SelectCell


And if the cell is greater than 6 point < double height

Selection.SelectCell
Selection.Font.Name = "Arial"
Selection.Font.ColorIndex = wdBlack
Selection.Font.Size=11
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
Selection.Cells.VerticalAlignment = wdCellAlignVerticalTop
Options.DefaultHighlightColorIndex = wdYellow
Selection.Range.HighlightColorIndex = wdYellow
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.SelectCell

Can someone add the missing code for me so it auto formats the table and then I can just run on each word table or all table it will save hours

Thanks Grant
Reply With Quote
  #2  
Old 08-22-2016, 05:21 AM
gmayor's Avatar
gmayor gmayor is offline Word table auto text alignment vs cell height Windows 10 Word table auto text alignment vs cell height Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,075
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

In the absence of a sample table, your requirement doesn't appear to make sense.

What the code appears to suggest is that if the row height is 6 point (which seems improbable) then the row is formatted as with your first set of codes, whereas if it is greater than 6 point (which seems to be the more likely scenario) it is formatted with the second set.

The only difference between the two is that the second set has the text aligned to center, whereas in the first it is left aligned.

Code:
Sub FormatTables()
Dim oTable As Table
Dim oRow As Row
    For Each oTable In ActiveDocument.Tables
        For Each oRow In oTable.Rows
            With oRow.Range
                .Font.Name = "Arial"
                .Font.ColorIndex = wdBlack
                .Font.Size = 11
                .Cells.VerticalAlignment = wdCellAlignVerticalTop
                .HighlightColorIndex = wdYellow
            End With
            If oRow.Height <= 6 Then
                oRow.Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
            Else
                oRow.Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
            End If
        Next oRow
    Next oTable
lbl_Exit:
    Set oRow = Nothing
    Set oTable = Nothing
    Exit Sub
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 08-22-2016, 05:28 AM
macropod's Avatar
macropod macropod is offline Word table auto text alignment vs cell height Windows 7 64bit Word table auto text alignment vs cell height Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,932
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

It's not at all apparent how you expect to have 11pt text in tables with a 6pt row height, but they're your tables:
Code:
Sub FormatTables()
Application.ScreenUpdating = False
Dim oTbl As Table, oCel As Cell
For Each oTbl In ActiveDocument.Tables
  With oTbl.Range
    With .Font
      .Name = "Arial"
      .ColorIndex = wdBlack
      .Size = 11
    End With
    .HighlightColorIndex = wdYellow
    .ParagraphFormat.Alignment = wdAlignParagraphLeft
    .Cells.VerticalAlignment = wdCellAlignVerticalTop
    For Each oCel In .Cells
      If oCel.Height > 6 Then
        oCel.Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
      End If
    Next oCel
  End With
Next oTbl
Application.ScreenUpdating = True
End Sub
The above code processes all tables in the document. To process a particular set of tables, change ActiveDocument to Selection, then select the tables to process before running the macro.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #4  
Old 08-22-2016, 05:29 AM
macropod's Avatar
macropod macropod is offline Word table auto text alignment vs cell height Windows 7 64bit Word table auto text alignment vs cell height Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,932
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Graham,

I took the OP's specs at face value, allowing for the possibility of vertically-merged cells.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 08-22-2016, 06:39 AM
Grantsub Grantsub is offline Word table auto text alignment vs cell height Windows 10 Word table auto text alignment vs cell height Office 2013
Novice
Word table auto text alignment vs cell height
 
Join Date: Aug 2016
Posts: 6
Grantsub is on a distinguished road
Default

Thanks

The 6 point is actually the the table row height is .06cm can the code be changed to table rowhight < .06

I will try both of these expample tonight
Thanks you for assiting me , hopefullly saving hours of time ,
I will revert on out come

Regards

Last edited by Grantsub; 08-22-2016 at 10:45 AM.
Reply With Quote
  #6  
Old 08-22-2016, 10:22 AM
Grantsub Grantsub is offline Word table auto text alignment vs cell height Windows 10 Word table auto text alignment vs cell height Office 2013
Novice
Word table auto text alignment vs cell height
 
Join Date: Aug 2016
Posts: 6
Grantsub is on a distinguished road
Default

Paul can you allow me to contact your directly for a short time to work though this
Grant
Reply With Quote
  #7  
Old 08-22-2016, 03:05 PM
macropod's Avatar
macropod macropod is offline Word table auto text alignment vs cell height Windows 7 64bit Word table auto text alignment vs cell height Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,932
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Grantsub View Post
The 6 point is actually the the table row height is .06cm
which is even less than 6pt!!!. 1cm is 28.35pt, so .06cm is only 1.70pt. If you want to use centimetres instead of points for the row heights, change:
If oCel.Height > 6 Then
to:
If Round(oCel.Height, 1) > Round(CentimetersToPoints(0.06), 1) Then
using whatever the correct value is - I seriously doubt it's 0.06cm.

Please keep any discussions in the public forum.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #8  
Old 08-22-2016, 11:27 PM
Grantsub Grantsub is offline Word table auto text alignment vs cell height Windows 10 Word table auto text alignment vs cell height Office 2013
Novice
Word table auto text alignment vs cell height
 
Join Date: Aug 2016
Posts: 6
Grantsub is on a distinguished road
Default

Thank you Paul and Graham
I see I didnít describe the task sufficiently

I am looking for some code that will set alignment as the cell height but due to the the nature of the finished doc not all coloums and row alignment are the same , the script you wrote for me showed me the power of what it could do by some one more advanced than me ,

Below is a extract of a formatted word doc sample formatted correctly showing the final result required ,with the alignment of the txt in vaporous rows and columns different determined by the row height with the hacked up code i use at present one line at a time with me deciding which to use.

Below is sample of the different alignments in each row and column if different colors for the different alignments as example

So what I do now is select row and then press the recorded macro 1 or 2 which I determine by looking are the row height and pressing 1 or 2

What I want to do is select a single table on a page or multiple tables over many pages and press a macro to achieve the txt alignment
Attached Files
File Type: docm Sample.docm (68.3 KB, 10 views)
Reply With Quote
  #9  
Old 08-22-2016, 11:56 PM
macropod's Avatar
macropod macropod is offline Word table auto text alignment vs cell height Windows 7 64bit Word table auto text alignment vs cell height Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,932
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Your table's row heights are a mix of 0.6cm (not .06cm - an order of magnitude difference) and 0.9cm. It also seems unlikely you'd want to vary the alignment of all cells on a row just because one or more cells in that row have more than one line of text. Your document also refers to rows having an automatic height, but then defining them as .06cm or .09cm (sic). Automatic heights and defined heights are mutually exclusive. The row height does not set itself to 0.6cm or 0.9cm if you're using automatic height. Someone has changed the table's row heights to produce that effect.

I think you need to do more work to establish exactly what your requirements are. Correctly specifying dimensions (where used) and automatic vs fixed dimensions is essential.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #10  
Old 08-23-2016, 12:19 AM
Grantsub Grantsub is offline Word table auto text alignment vs cell height Windows 10 Word table auto text alignment vs cell height Office 2013
Novice
Word table auto text alignment vs cell height
 
Join Date: Aug 2016
Posts: 6
Grantsub is on a distinguished road
Default

Thank you Paul
I didnít see that when I copied over the extracts for the sample.

The heights are meant to set for at least .6cm so the cell height increases to take extra information , But once it crease the txt alignment needs to change for presentation purposes and printing purposes
The tables are populated manually from a another word document table via copy and pastes and sometimes the formatting goes weird ,
But that process is another task far beyond me .

But after checking each line I end up with the final sample shown below.

For now I just want to speed up the alignment process with a few simple clicks instead of hundreds

Can this long task be sampled from what I do now with my two recorded macros ?
It normally takes me four hours to copy of the information from the other table and then go though and align each tables contents row by row ,

Thanks for all you help and understanding
Reply With Quote
  #11  
Old 08-23-2016, 12:52 AM
macropod's Avatar
macropod macropod is offline Word table auto text alignment vs cell height Windows 7 64bit Word table auto text alignment vs cell height Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,932
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Perhaps, then you should be checking what copy/paste defaults you are using.

You mention rows heights being set with the 'at least' attribute. You should be aware that such rows will always report the default height regardless of the actual height. Rows without a row height set (i.e. a purely automatic row height) will likewise always return a value of 9999999 regardless of the actual height. Accordingly, a macro that works on the assumption that the auto-adjusted row heights represent the actual heights will fail under either scenario.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #12  
Old 08-23-2016, 02:06 AM
Grantsub Grantsub is offline Word table auto text alignment vs cell height Windows 10 Word table auto text alignment vs cell height Office 2013
Novice
Word table auto text alignment vs cell height
 
Join Date: Aug 2016
Posts: 6
Grantsub is on a distinguished road
Default

Thanks Paul

So if the at least .6cm is selected for each table there is no way or automating the auto alignment macro I am seeking

The reason why it is its set as at least we when we copy the data manually from the ď sample 1 : attached report document in portrait to the corrective actions word document in landscape the cell can changes from a single line to a double line thus making the cell txt alignment different , so then its needs to be adjusted as the width of the cell changes in landscape .
So the process I use is to select the contents from the Sample 1 table , rows seven onwards and paste in the other document table by adding lines below , Then I go through the table and align all the cells correctly for presentation and printing , thus the need for some sort of cell txt alignment macro.
.
The problems all ways occurs going from portrait to landscape as the cell for the information changes in width and then need to be aligned to suit .

I have been work this way for three years and I have tried other ways but theses steps are the quickest and then its just checking the alignment and txt positioning the cells .

As the amount of row each time change on each table and the paste location changes in the second document .
At the moment I just use the recorded macro row by row to check and correct if required in the second document

Thus I use the recorded macro to check the cell alignment light it checked so I know they have been checked and I move on

Can you advise on different settings some better way to carry out the massive task with the copied table data between tables and between
Thanks Grant
Attached Files
File Type: docx Sample Table.docx (25.0 KB, 8 views)
File Type: docx Sample 1.docx (56.1 KB, 7 views)
Reply With Quote
  #13  
Old 08-23-2016, 04:35 AM
gmayor's Avatar
gmayor gmayor is offline Word table auto text alignment vs cell height Windows 10 Word table auto text alignment vs cell height Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,075
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

Unless I am missing something fundamental that you haven't explained. The main issue here is that you have some rows vertically aligned to the top and some vertically aligned to the centre. Surely then all that needs be done is to fix that so that all the rows have the same vertical alignment?

Code:
Sub AlignTableCells()
Dim oTable As Table
    For Each oTable In ActiveDocument.Tables
        With oTable.Range
            .Font.Name = "Arial"
            .Font.ColorIndex = wdBlack
            .Font.Size = 11
            .Cells.VerticalAlignment = wdCellAlignVerticalTop 'or wdCellAlignVerticalCenter
        End With
    Next oTable
lbl_Exit:
    Set oTable = Nothing
    Exit Sub
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pasting text from Excel cell into word without creating a table, and keeping the in-cell formatting hanvyj Excel Programming 0 08-28-2015 01:15 AM
Word table auto text alignment vs cell height Row auto height markand_bhatt2008 Excel 1 04-29-2013 07:46 AM
Word table auto text alignment vs cell height table cell alignment issue gib65 Word 1 06-02-2012 03:03 PM
Word table auto text alignment vs cell height Table Text Alignment? Jazz43 Word Tables 2 08-19-2010 08:07 AM
Auto-populate an MS Word table cell with text from a diff cell? dreamrthts Word Tables 0 03-20-2009 01:49 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:41 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2023, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2023 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft