Code to "Remove Space after Paragraph" in a Word documents generates by Excel

[perola.rike]

I have a Workbook that generates a Word report. It work fine except I cannot figure out how to write a code in my Excel module that executes the "Remove Space after Paragraph" in the Word document ...

Any solutions?



This is the code:


Sub wordexport()


'PART 1 EXCEL

Dim i As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Sheets("wordexport").Range("A1:A500").ClearContents
Sheets("wordgenerator").Range("$C$1:$C$229").AutoFilter Field:=1, Criteria1:="<>" 'skjuler blanke rader
Sheets("wordgenerator").Select
Range("D1:D180").Select
Selection.Copy
Sheets("wordexport").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False


'PART 2 WORD

Dim wdApp As Object
Dim wd As Object
Dim myCells As Range
On Error Resume Next
Dim rng As Range

Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
Set wd = wdApp.Documents.Add
wdApp.Visible = True
Set rng = Sheets("wordexport").Range("A1:A300")
rng.Copy
With wd.Range
.Collapse Direction:=0
.Collapse Direction:=0
.PasteSpecial False, False, True
.tables(1).Select
wd.tables(1).ConvertToText Separator:=0, NestedTables:=True
End With

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic



End Sub

 

[InaCell]

The question begs. What is the problem with a space at the end of a paragraph? It's a space, it won't be read by anyone!

 

[perola.rike]

There are a lot of space between the textlines in the Word document. In Word, when I select all text in the word document and manually hit the button "Remove Space After Paragraph" this space decreases. Thus, I need to decrease these spaces between the textlines with a VBA code in my Excel module. These spaces are visible to the reader, yes

 

[InaCell]

Try,

Sub TrimALL()'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
End Sub

InaCell

 

[perola.rike]

Thanks for the code, but I didnt get it working. Where should I put it?

 

[InaCell]

Hi perola.rike,

Select the cells you want the spaces removed from. Copy to a module. Run macro.

InaCell.

 

[perola.rike]

I ran the code on this range: Sheets("wordexport").Range("A1:A300")

But still, when these data are copied to the word document, still a lot of spaces. I tend to believe that the "error" lies within this code part below. E.g. that the pastespecial function somehow produces this space in the text document....(?)

With wd.Range
.Collapse Direction:=0
.Collapse Direction:=0
.PasteSpecial False, False, True
.tables(1).Select
wd.tables(1).ConvertToText Separator:=0, NestedTables:=True
End With

 

[InaCell]

Spaces might be some other character resembling a space. Find what it is and you might be able to adapt the code for whatever character it is.

Or there could be an answer within Word.

InaCell.

 

[sijpie]

Just for reference:
What is meant with "remove space after paragraph" is to remove the extra line spacing that Word inserts when a new paragraph starts. This has nothing to do with space characters.

See ParagraphFormat.SpaceAfter property (Word) for code examples