I am calling a embedded word document from excel and populating it with tables from excel with financial data to produce a report.

When the tables are copied into word the numbers contain leading spaces that are spoiling the formatting. I have written the code to strip out the leading spaces however the data is still left justified rather than right justified.

I am trying to use the following code using wildcards to locate any financial numbers and left justify them however I am off the mark as the code 1) removes the financial numbers and 2) does not justify.

With wrdObj


.Content.SelectAll
With .Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "*.[0-9][0-9]"
.MatchWildcards = True
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
If .Found = True Then .Parent.Font.Alignment = wdAlignParagraphRight
End With
End with


Any help appreciated.

How are you updating the table? The insertion of leading spaces that aren't in the source data is not the normal behaviour. Furthermore, if the tables are formatted as right-aligned before updating, adding data to the cells, or changing what's already there, won't change the alignment.

There's a couple of problems with your code, but we'll address those if necessary after sorting out the data issues.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi macropod, thanks so much for the response. I will admit that I am the worst type of programmer (IT exec playing as required to get reports out) so I am happy to hear any feedback, good or bad.

To answer your question, the process is to select a range in excel that contains the table data and then do a search for a placeholder in the word document. For example:

Range("dep_comparison_table").Copy
wrdObj.Selection.Find.Text = "<dep_comparison_table>"
Call FormatPaste
wrdObj.Selection.MoveLeft Unit:=wdCharacter, Count:=20
wrdObj.Selection.Tables(1).AllowAutoFit = False
wrdObj.Selection.Tables(1).PreferredWidth = wrdObj.CentimetersToPoints(18.4)

Sub FormatPaste()

wdFind.Replacement.Text = ""
wdFind.Forward = True
wdFind.Wrap = wdFindContinue
wdFind.Execute
Call CheckClipBrd
wrdObj.Selection.Paste
CutCopyMode = False

End Sub

After all the tables are copied the tables are formatted with the following:

For Each Table In wrdDoc.Tables
Table.Select
For i = 1 To Table.Rows.Count
Table.Rows(i).Range.ParagraphFormat.KeepTogether = True
If i < Table.Rows.Count Then
Table.Rows(i).Range.ParagraphFormat.KeepWithNext = True
End If
Next i
Next Table

FWIW, I think the leading spaces is normal behaviour with Excel content pasted into Word depending on the cell formatting applied in Excel.

How are you finding the leading spaces to remove them? A quick fix to remove leading spaces is to centre-align so you could kill two birds with the one stone if you wanted by following that with a right-align.

__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia

A few questions:
1. Why an embedded Word document, rather than a Word template stored in the same folder as the workbook?
2. Does the Excel table always span the same range?
3. Why does your last code block loop through all tables in the document instead of processing just the one that's being inserted/updated?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

When I paste an Excel range containing both text numeric data into Word, the data retain the same formatting and alignments they had in Excel and there are no leading/trailing spaces. In this regard, I note the OP is just using an ordinary Paste, not Word's PasteExcelTable or PasteAndFormat methods, for example.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Paul
On my machine, cells formatted as 'Accounting' brings a leading space into Word with an ordinary paste.

I remember having seen other cell formats that also bring multiple leading spaces but I believe this didn't happen with left aligned text.

__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia

With the 'Accounting, format you'll get either a single space or - before the $ symbol, depending on whether the number is +ve or -ve. Plus you'll get however many spaces are needed to pad out the $ symbol from the first numeral to make the $ symbols line up. Either way, you'd need to allow the same amount of space (not number of spaces) in the cell, unless you have no -ve values; in which case a Find for ' $' replaced by '$' would delete that space. You'll also get a space after the last digit, which a wildcard Find/Replace could eliminate.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi macropod in answer to your questions:

1. We send the template out to our field staff. its designed in such a way they can download in anywhere onto their PC / tablet and can run the program. Basically saves us writing a separate install program.

2. There are approximately 20 tables ad all bar one span the same range

3. No particular reason... just the way the program was coded.

Thanks, I will try those methods and see how well they work.

Hi guessed,

I just checked, the current format is custom -> -* #,##0.00_-;-* #,##0.00_-;_-* "-"??_-;_-@_-

I'll try a few scenarios changing the format to see what happens.

You will get an almost identical visual result with:
#,##0.00_-;#,##0.00_-;"-"??_-;_-@_-
or, if you can do without the trailing space:
#,##0.00;#,##0.00;"-"??;_-@
Either of these will eliminate the space padding.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi guys, thanks for all your help. Reformatting the source cells in excel worked the best.