I have a code for writing Word documents from Excel. Currently after each table pasted to Word from Excel empty line appears in Word document. I was thinking is it possible to remove one line after each table pasted or are there any better suggestions?
My current code:
I have tried to use `.Range.Paragraphs(-1).Range.Delete` (commented out in my code) without success. What is the correct command for that kind of operation?
Even solution for removing previous paragraph would help. I can create own case for it and perform this command after each table.
I have seen this answer by widely respected Macropod for default solution. I am hoping there are might be something that can be done with VBA
Here is full code if somebody want to test:
Asked also here: Remove one line above after each table pasted from Excel to Word
My current code:
VBA Code:
Set xlRng = ThisWorkbook.Sheets("Document").Range("G3", ThisWorkbook.Sheets("Document").Range("G" & Rows.Count).End(xlUp))
Set wdRng = .Range.Characters.Last
For Each Cell In xlRng
wdRng.InsertAfter vbCr & Cell.Offset(0, -5).Text
Select Case LCase(Cell.Value)
Case "table6"
ThisWorkbook.Sheets("Tables").Range("B817:C820").Copy
With wdRng
Set rngPara = .Paragraphs.Last.Range
rngPara.Style = "Data"
rngPara.PasteExcelTable False, False, False
.Tables(.Tables.Count).Range.Paragraphs.Indent
.Font.Hidden = 0
'.Range.Paragraphs(-1).Range.Delete
Set rngPara = Nothing
End With
End Select
Next Cell
I have tried to use `.Range.Paragraphs(-1).Range.Delete` (commented out in my code) without success. What is the correct command for that kind of operation?
Even solution for removing previous paragraph would help. I can create own case for it and perform this command after each table.
I have seen this answer by widely respected Macropod for default solution. I am hoping there are might be something that can be done with VBA
How to remove extra paragraph after table
When I use word 2013 to insert a table it always inserts a paragraph mark after the table. How can I remove this from table because this paragraph mark creates another page to my document. More
answers.microsoft.com
Here is full code if somebody want to test:
VBA Code:
Sub opentemplateWord()
Dim Paragraphe As Object, WordApp As Object, WordDoc As Object
Dim wSystem As Worksheet
Dim Cell As Range
Dim wdRng As Object 'Word.Range
Dim xlRng As Excel.Range
Dim tempFolderPath As String
Dim filePath As String
Dim fileTitle As String
Dim rngPara As Object
'Application.ScreenUpdating = False
On Error GoTo ErrorHandlerEndExecution
Set wSystem = ThisWorkbook.Sheets("Templates")
Dim File: File = Environ("Temp") & "\" & "Document_template" & ".docx"
'creationsession Word
Set WordApp = CreateObject("Word.Application")
'word ll be close to run
WordApp.Visible = False
'open the file .doc
Set WordDoc = WordApp.Documents.Open(File)
With WordDoc
Set xlRng = ThisWorkbook.Sheets("Document").Range("G3", ThisWorkbook.Sheets("Document").Range("G" & Rows.Count).End(xlUp))
Set wdRng = .Range.Characters.Last
For Each Cell In xlRng
wdRng.InsertAfter vbCr & Cell.Offset(0, -5).Text
Select Case LCase(Cell.Value)
Case "table6"
ThisWorkbook.Sheets("Tables").Range("B817:C820").Copy
With wdRng
Set rngPara = .Paragraphs.Last.Range
rngPara.Style = "Data"
rngPara.PasteExcelTable False, False, False
.Tables(.Tables.Count).Range.Paragraphs.Indent
.Font.Hidden = 0
'.Paragraphs.Last.Range.Delete
Set rngPara = Nothing
End With
End Select
Next Cell
WordDoc.SaveAs2 Environ$("Temp") & "\" & _
"Test" & ".docx"
End With
WordDoc.Close
WordApp.Quit
Set WordDoc = Nothing
Set WordApp = Nothing
Exit Sub
ErrorHandlerEndExecution:
WordDoc.Close
WordApp.Quit
Set WordDoc = Nothing
Set WordApp = Nothing
'Application.ScreenUpdating = True
End Sub
Asked also here: Remove one line above after each table pasted from Excel to Word