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

perola.rike

Board Regular
Joined
Nov 10, 2011
Messages
151
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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!
 
Upvote 0
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:)
 
Upvote 0
Try,

Code:
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top