Macro for selecting pages and formatting of tables

macropod · #16 04-30-2012, 11:00 PM

Hi umesh,

This is the first time you've mentioned anything about the bottom borders. Obviously, if you delete the bottom row, the bottom border gets deleted too. To restore the borders, you need to:
• Dimension three more variables:
Dim BdrClr As Long, BdrWdth As Long, BdrSty As Long
• Insert the following code after 'If UCase(Rng.Text) <> "DATE" And UCase(Rng.Text) <> "PERIOD" Then GoTo NextTable':

Code:

      With .Range.Rows.Last.Borders(wdBorderBottom)
        BdrClr = .Color
        BdrWdth = .LineWidth
        BdrSty = .LineStyle
      End With

• Insert the following code between 'Next' and 'Set Rng = .Range':

Code:

      With .Range.Rows.Last.Borders(wdBorderBottom)
        .LineStyle = BdrSty
        .LineWidth = BdrWdth
        .Color = BdrClr
      End With

If you put the 'shading' code where I suggested, it does remove all the shading from the document. I suspect you have put the code before the final 'Next' statement.

ubns · #17 05-01-2012, 03:48 PM

Thanks Paul. It all works.

With regards to the shadinbg code, I guess its would be better if there is an independent, standalone code for that since there are sometimes documents without the tables and in that case it would be unneccesary to run this long code.

so it would be better for us to have a standalone code.

Regards

Umesh Banga

macropod · #18 05-01-2012, 03:57 PM

Hi umesh,

That's pretty trivial. I'm surprised you couldm't work this out:

Code:

Sub CleanShading()
With ActiveDocument.Range.Font.Shading
  .ForegroundPatternColor = wdColorAutomatic
  .BackgroundPatternColor = wdColorAutomatic
End With
End Sub

ubns · #19 05-01-2012, 04:01 PM

Thanks again.

ubns · #20 05-15-2012, 11:20 PM

Hi Peter,

I have been applying the macros on my document.

Few issues:

1) With the shading macro - it actually changes the shade from what ever the shade is to white shading. can we apply no shading.

2) I have shown this in the attached document.

3) in the attached document - i have also tried to ask how we can left align all the tables in the document in one hit.

Regards

Umesh Banga

macropod · #21 05-15-2012, 11:33 PM

Hi Umesh,

Who is Peter?

Quote:

1) With the shading macro - it actually changes the shade from what ever the shade is to white shading. can we apply no shading.

No, it doesn't - it removes shading, returning it to Word's default state and, even if it applied white shading, you could then apply a different shade.

Quote:

how we can left align all the tables in the document in one hit.

After:
With oTbl
add:
.Rows.LeftIndent = 0
Note that this still processes tables one-at-a-time.

ubns · #22 05-17-2012, 12:10 AM

thanks it works, now how can do the same from right side -- i wanna squeeze in little bit from right, so that fits between the normal page border.

macropod · #23 05-17-2012, 01:45 AM

It would be helpful if you said from the outset what you want to do. I shouldn't have to keep revisiting the project over and over.

There is no right indent property for table rows. You could use:
.AutoFitBehavior wdAutoFitWindow
after:
.Rows.LeftIndent = 0
This forces the width of the text boundaries for the table to match the text boundaries of the page. Do note, though, that neither of these settings takes account of what's defined by the LeftPadding or RightPadding values (which might be the same for the whole table or might be different in different cells). Consequently, some rows, at least are likely to have cell boundaries that are outside the normal text boundaries (as defined by your margin settings). If you don't want that, you have to decide what you want to do about the left & right padding (which affects how far away from the text the cell borders are) and/or the left indent and how the table width is calculated.

ubns · #24 08-14-2012, 11:53 PM

Hi Paul,

There is one row in table -- Tax payable. We have to include that even all the rows in that contains "0".

The following code delete all the rows with "0", and ofcourse it was what I requested.

How can we change it

Code:

Sub FormatTables()
Application.ScreenUpdating = False
Dim oTbl As table, oCel As Cell, Rng As Range, i As Long, bDel As Boolean
Dim BdrClr As Long, BdrWdth As Long, BdrSty As Long
With ActiveDocument
  For Each oTbl In .Tables
    With oTbl
      .Rows.LeftIndent = 6
      
      '.Rows.DistanceLeft = 5
      Set Rng = .Cell(1, 1).Range
      Rng.End = Rng.End - 1
      If UCase(Rng.Text) <> "DATE" And UCase(Rng.Text) <> "PERIOD" Then GoTo NextTable
      With .Range.Rows.Last.Borders(wdBorderBottom)
      BdrClr = .Color
      BdrWdth = .LineWidth
      BdrSty = .LineStyle
      End With
      
      For i = .Rows.Count To 2 Step -1
        With .Rows(i)
          If .Cells.Count > 1 Then
            Set Rng = .Range
            bDel = False
            If bDel = False Then bDel = FindText(Rng, "<[Aa][Gg][Ee]>")
            If bDel = False Then bDel = FindText(Rng, "%")
            If bDel = False Then bDel = FindText(Rng, "<[0-9]{1,2} [JFMASOND][anebrpyulgctov]{2} [0-9]{2}>")
            If bDel = False Then bDel = Not FindText(Rng, "[A-Za-z0-9\>]")
            If bDel = False Then
              Set Rng = oTbl.Rows(i).Range
              Rng.Start = Rng.Cells(2).Range.Start
              If Len(Rng.Text) > (Rng.Cells.Count + 1) * 2 Then
                bDel = Not FindText(Rng, "[A-Za-z1-9\>]")
              End If
            End If
          End If
          If bDel = True And i > 1 Then .Delete
        End With
      Next
      With .Range.Rows.Last.Borders(wdBorderBottom)
        .LineStyle = BdrSty
        .LineWidth = BdrWdth
        .Color = BdrClr
      End With
      Set Rng = .Range
      Rng.Start = Rng.Rows(2).Range.Start
      With Rng.find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Forward = True
        .Wrap = wdFindStop
        .Format = False
        .MatchAllWordForms = False
        .MatchSoundsLike = False
        .MatchWildcards = True
        .Text = "([0-9,.]{1,})"
        .Replacement.Text = "$\1"
        .Execute Replace:=wdReplaceAll
        .Text = "[$]{2,}"
        .Replacement.Text = "$"
        .Execute Replace:=wdReplaceAll
        .Text = "\>[ ]@"
        .Replacement.Text = ">"
        .Execute Replace:=wdReplaceAll
        .Text = "[ ]@\>"
        .Replacement.Text = ">"
        .Execute Replace:=wdReplaceAll
        .Text = "\>"
        .Replacement.Text = "     "
        .Execute Replace:=wdReplaceAll
      End With
      .Range.Font.Size = 9
      .TopPadding = 5
      .BottomPadding = 2
      .AutoFitBehavior wdAutoFitWindow
    End With
NextTable:
  Next
End With
Set Rng = Nothing
Application.ScreenUpdating = True
End Sub
 
Function FindText(Rng As Range, StrFnd As String) As Boolean
With Rng.find
  .ClearFormatting
  .Replacement.ClearFormatting
  .Forward = True
  .Wrap = wdFindStop
  .Format = False
  .MatchAllWordForms = False
  .MatchSoundsLike = False
  .MatchWildcards = True
  .Text = StrFnd
  .Replacement.Text = ""
  .Execute
End With
FindText = Rng.find.Found
End Function

macropod · #25 08-15-2012, 09:25 PM

Hi Umesh,

Insert:
If bDel = True Then bDel = Not FindText(.Range, "Tax Payable")
before:
If bDel = True And i > 1 Then .Delete

ubns · #26 08-15-2012, 11:48 PM

Thanks Paul.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Another Case of Automatic/Zombie Formatting of Tables	MKummerfeldt	Word Tables	0	10-31-2011 10:40 AM
Splitting multiple pages using macro	F5JASON	Excel Programming	0	07-27-2011 08:22 AM
Selecting the macro document	lars	Word VBA	0	08-19-2010 06:06 AM
Whacked formatting in tables (2007)	Roscoe	Word Tables	6	06-11-2010 02:48 PM
Keeping Tables from Splitting Between Pages	AlexPaoletti	Word Tables	2	05-18-2010 01:17 AM