Macro for setting decimal tabs in tables

Ulodesk · #1 03-15-2022, 07:25 AM

At work we sometimes get pricing tables -- sometimes quite extensive -- in Word that have originated in Excel, in which the numbers are right aligned but by means of a number of preceding spaces. I think these spaces must be an artifact of some conversion process (I can’t imagine anyone creating a giant table in Excel and trying to align the numbers to the right this way), but I'm unable to recreate it. As shown in the attachment, the spaces in Word then push the longer numbers over onto the next line.

My purpose is to try to create a macro that can be applied to the Word table to replace the spaces with a decimal tab that will align the numbers properly. I don't know how to code, but I can record a basic macro. (Thank you for suppressing your mirth.).

Left-aligning the column content I can do, and in the find&replace function with wildcards, ( ){2,} will select the spaces, but I can't find the VBA for a decimal tab to use in the replace field.

Once the tab is "installed", perhaps it can be specified as a distance from the right cell border or inside margin, since table content, layout, and column widths vary. This could then be easily edited if needed.

Help is much appreciated, as always.

(If you know what typically used format in Excel creates these spaces in Word, I'd love to know.)

gmayor · #2 03-15-2022, 08:03 AM

The following should work

Code:

Sub Macro1()
Dim oTable As Table
Dim i As Integer
Dim oCell As Cell
Dim oRng As Range
    Set oTable = Selection.Tables(1)
    With oTable
        For i = 2 To .Rows.Count
            For Each oCell In .Rows(i).Cells
                Set oRng = oCell.Range
                oRng.Collapse 1
                oRng.MoveEndWhile Chr(32)
                oRng.Text = ""
                oCell.Range.ParagraphFormat.TabStops.Add _
                        Position:=InchesToPoints(1.41), _
                        Alignment:=wdAlignTabDecimal, _
                        Leader:=wdTabLeaderSpaces
            Next oCell
        Next i
    End With
    Set oTable = Nothing
    Set oCell = Nothing
    Set oRng = Nothing
End Sub

Ulodesk · #3 03-15-2022, 12:21 PM

Thank you very much, Graham. Pigs would fly before I figured that one out on my own.

May I ask, what does the "Position:=InchesToPoints(1.41), " instruct?

Thanks again!

Guessed · #4 03-15-2022, 03:51 PM

There are easier ways to do this cleanup. The spaces appear automagically when Excel cells are right aligned and the content is then copied into Word.

Easy non-macro fix is to select table, Centre Align (Ctrl-E) which removes the spaces [Optionally, Left Align (Ctrl-L) if you want to reset alignment but the next step make it superfluous]. Then apply a style (which has the decimal tab already set) to the numbered cells. You could use this same approach in a macro to avoid looping through the cells.

gmayor · #5 03-15-2022, 09:47 PM

Quote:

Originally Posted by Ulodesk

May I ask, what does the "Position:=InchesToPoints(1.41), " instruct?

It sets the position of the decimal tab in the cell, however on returning to the question, I note that the column widths may be variable, in which case the following may work better.

Code:

Sub Macro1()
Dim oTable As Table
Dim i As Integer
Dim oCell As Cell
Dim oRng As Range
Dim iPos As Integer
    Set oTable = Selection.Tables(1)
    With oTable
        For i = 2 To .Rows.Count
            For Each oCell In .Rows(i).Cells
                iPos = oCell.Width - oCell.LeftPadding - oCell.RightPadding - 20
                Set oRng = oCell.Range
                oRng.Collapse 1
                oRng.MoveEndWhile Chr(32)
                oRng.Text = ""
                oCell.Range.ParagraphFormat.TabStops.Add _
                        Position:=iPos, _
                        Alignment:=wdAlignTabDecimal, _
                        Leader:=wdTabLeaderSpaces
            Next oCell
        Next i
    End With
    Set oTable = Nothing
    Set oCell = Nothing
    Set oRng = Nothing
End Sub

Ulodesk · #6 03-16-2022, 07:15 AM

[QUOTE=Guessed;166485]There are easier ways to do this cleanup. The spaces appear automagically when Excel cells are right aligned and the content is then copied into Word.

Hi, Andrew. Thanks for the proposal, but I don't get the spaces when I copy/paste right-aligned cells from Excel to Word; they just come in as right-aligned. There must be some setting, either in Excel or Word, that has eluded me. Ideas?

Thanks.

Ulodesk · #7 03-16-2022, 07:54 AM

Hi, Graham. Thank you for the info and update. I'm seeing an anomaly when running the new macro; perhaps I have misunderstood something.

I have provided an attachment with a 4-cell table, cells left-aligned, with spaces preceding the numbers. With my cursor in the top left cell, or with all cells' content selected, only the second row is affected.

Also, two other things:

The macro seems to indicate the creation of a decimal tab, but since I don't know VBA, perhaps that's my misreading. It doesn't create one in any case, perhaps due to the variable column-width issue being addressed.

And this, just my ignorance, out of curiosity I tried changing the 20 you used for right cell pad to 80, but it appeared to have no effect.

Again, I genuinely appreciate the top-level expertise I receive here. I don;t mean to take undue advantage of it with these questions.

gmayor · #8 03-16-2022, 10:50 PM

The code assumed a header row in the table. As your new example doesn't have a header row the first row is ignored. Change the loop to start from 1 rather than 2.
Also in your new example there are tabs already set in the second row, which conflict with the tabs set by the macro so you need to clear those tabs first. These issues are why the macro appears not to change anything.

As there may be a header row or text in the table, I have added a check function so that cells without numbers are ignored. If you don't need that you can omit the function and its call.

Code:

Sub Macro1()
Dim oTable As Table
Dim i As Integer
Dim oCell As Cell
Dim oRng As Range
Dim iPos As Integer
    If Selection.Information(wdWithInTable) = False Then
        MsgBox "Put the cursor in the table to process first!", vbCritical
        Exit Sub
    End If
    Set oTable = Selection.Tables(1)
    With oTable
        For i = 1 To .Rows.Count
            For Each oCell In .Rows(i).Cells
                iPos = oCell.Width - oCell.LeftPadding - oCell.RightPadding - 20
                Set oRng = oCell.Range
                If IsNumbered(oCell) = True Then
                    oRng.ParagraphFormat.TabStops.ClearAll
                    oRng.Collapse 1
                    oRng.MoveEndWhile Chr(32)
                    oRng.Text = ""
                    oCell.Range.ParagraphFormat.TabStops.Add _
                            Position:=iPos, _
                            Alignment:=wdAlignTabDecimal, _
                            Leader:=wdTabLeaderSpaces
                End If
            Next oCell
        Next i
    End With
    Set oTable = Nothing
    Set oCell = Nothing
    Set oRng = Nothing
End Sub

Private Function IsNumbered(oTableCell As Cell) As Boolean
Dim sValue As String
Dim oCellRng As Range
    Set oCellRng = oTableCell.Range
    oCellRng.End = oCellRng.End - 1
    sValue = Trim(oCellRng.Text)
    IsNumbered = IsNumeric(sValue)
End Function

Ulodesk · #9 03-17-2022, 05:38 AM

Thank you, Graham. Sorry for my oversight of the tabs. I very much appreciate the explanation and extra flexibility.

Ulodesk · #10 03-17-2022, 05:53 AM

Sorry to bother you again, Graham. I guess the part from "Private" on down is the check function and should be placed just before End Sub if I want to use it? VBA is telling me it can't come after the End Sub.

Also, the 1 that I can change to 2: Is that the 1 in "1 To .Rows.Count" or "oRng.Collapse 1"? There is also a 1 in the Function part, but I'm guessing that's something else.

Thank you.

gmayor · #11 03-17-2022, 07:11 AM

The function starting 'Private' goes in the same module after End Sub, exactly as I have posted it. The '1' is in the '1' in 1 To .Rows.Count again already changed in the last version of the code.

Ulodesk · #12 03-18-2022, 09:53 AM

Excellent.

Thank you once again.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Decimal tabs with space as thousands separator	harry	Word	1	12-22-2023 02:59 PM
Macro to delete tabs in Word	Shelley Lou	Word VBA	16	12-21-2020 06:34 AM
I need a macro to format images and remove tabs. help	LuisXVI	Word VBA	4	11-12-2018 03:12 PM
Setting tabs relative to block of text	kirkm	Word	8	09-06-2016 07:54 PM
Pulling 2 digits before a decimal point from adjoining cell then zeros after decimal	jadess916	Excel	1	06-26-2014 03:48 AM