Microsoft Office Forums How to prevent a cell to disappear

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-08-2019, 11:07 PM
Dzib Dzib is offline How to prevent a cell to disappear Windows 10 How to prevent a cell to disappear Office 2019
Novice
How to prevent a cell to disappear
 
Join Date: Jul 2019
Posts: 29
Dzib is on a distinguished road
Default How to prevent a cell to disappear

Hi,




I have a macro that copy/paste a table from Excel to Word.

The table has 3 columns (last one can be empty) and a variable number of rows



Everything works fine except that when the the text in the 2nd column is longer than the size of the cell and the last cell of the row is empty, this cell dispappear and is replaced by the 2nd cell...


See attached picture:



How can I avoid this?


Thanks ;-)
Reply With Quote
  #2  
Old 08-13-2019, 02:31 PM
gmaxey gmaxey is offline How to prevent a cell to disappear Windows 10 How to prevent a cell to disappear Office 2016
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 967
gmaxey will become famous soon enoughgmaxey will become famous soon enough
Default

Where is your macro?
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #3  
Old 08-13-2019, 08:35 PM
eduzs eduzs is offline How to prevent a cell to disappear Windows 10 How to prevent a cell to disappear Office 2010 32bit
Competent Performer
 
Join Date: May 2017
Posts: 174
eduzs is on a distinguished road
Default

Select the column B of the used range, right click the selection and select Format Cells Menu, in Alingment enable "Wrap text" option, do this before copy to word.
To insert this procedure into your macro you can do something like this:
oRange.columns(2).wraptext=true
Backup before test.
Reply With Quote
  #4  
Old 08-28-2019, 08:55 AM
Dzib Dzib is offline How to prevent a cell to disappear Windows 10 How to prevent a cell to disappear Office 2019
Novice
How to prevent a cell to disappear
 
Join Date: Jul 2019
Posts: 29
Dzib is on a distinguished road
Default

The "wrap text" option works in Excel but once pasted in Word the cell disappear as you can see on these pictures:








Here's my code:


Code:
Sub ExportInvNL()

Dim wdApp As Object
Dim wd As Object
Dim xlSheet As Worksheet
Dim rng As Range
Dim LastRow As Long
Const wdReplaceAll As Long = 2
Const wdFindContinue As Long = 1

On Error Resume Next
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("C:\Users\laure\Documents\Boulot\TestInventarisNL.docx")
wdApp.Visible = True

Set xlSheet = ActiveWorkbook.Sheets("INV")
With xlSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set rng = .Range("A2:C" & LastRow)
    rng.Copy
    With wd.Range
        .Collapse Direction:=0
        .InsertParagraphAfter
        .PasteSpecial DataType:=1
        With .Find
            .ClearFormatting
            .Text = vbTab
            .Replacement.ClearFormatting
            .Replacement.Text = " "
            .Execute Replace:=wdReplaceAll, Forward:=True, Wrap:=wdFindContinue
        End With
    End With
End With

 End Sub

I could put a blank character in column C and that works but I thought maybe there's a more "clean" solution...
Reply With Quote
  #5  
Old 08-28-2019, 06:01 PM
eduzs eduzs is offline How to prevent a cell to disappear Windows 10 How to prevent a cell to disappear Office 2010 32bit
Competent Performer
 
Join Date: May 2017
Posts: 174
eduzs is on a distinguished road
Default

Try that (backup before doing any modification):
after Set rng statement:
Code:
rng.Cells.WrapText = True
Modify:
Code:
.PasteSpecial DataType:=1
To:
Code:
.Paste
Reply With Quote
  #6  
Old 08-28-2019, 10:51 PM
Dzib Dzib is offline How to prevent a cell to disappear Windows 10 How to prevent a cell to disappear Office 2019
Novice
How to prevent a cell to disappear
 
Join Date: Jul 2019
Posts: 29
Dzib is on a distinguished road
Default

The wraptext doesn't change anything and replacing pastespecial by paste gives this:

Reply With Quote
  #7  
Old 08-29-2019, 03:26 AM
eduzs eduzs is offline How to prevent a cell to disappear Windows 10 How to prevent a cell to disappear Office 2010 32bit
Competent Performer
 
Join Date: May 2017
Posts: 174
eduzs is on a distinguished road
Default

There's nothing wrong with the pasted table.
I think that you just need to adjust column width.
The last cell is empty so it's have a minimum width.
Try:
Quote:
.PasteAndFormat wdSingleCellTable
Reply With Quote
  #8  
Old 08-30-2019, 03:34 AM
Guessed's Avatar
Guessed Guessed is offline How to prevent a cell to disappear Windows 10 How to prevent a cell to disappear Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,376
Guessed is a jewel in the roughGuessed is a jewel in the roughGuessed is a jewel in the roughGuessed is a jewel in the rough
Default

The way I've done this in the past is to change the font size in Excel to something tiny eg 2pt before copying, then after pasting into Word, reset the font size to whatever you wanted to have.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #9  
Old 08-30-2019, 11:44 PM
Dzib Dzib is offline How to prevent a cell to disappear Windows 10 How to prevent a cell to disappear Office 2019
Novice
How to prevent a cell to disappear
 
Join Date: Jul 2019
Posts: 29
Dzib is on a distinguished road
Default

Thanks eduzs and guessed, I'll try both solutions when I'll be back at work.
I'll let you know...
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to prevent a cell to disappear Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow FUGMAN Excel Programming 7 02-05-2017 08:37 AM
Prevent cell from expanding vertically. snowboarder2 Word Tables 1 08-24-2016 02:52 PM
How to prevent a cell to disappear If value of cell A Matches a value in a Range of cells (column) then add value of cell A to cell C rick10r Excel 1 07-05-2016 12:07 PM
Data Validation - Prevent blank cell based on a condition dawd Excel 2 08-12-2015 05:44 AM
Word tables: prevent word from highlighting whole cell when I highlight text skylark53 Word 2 08-04-2015 08:12 AM


All times are GMT -7. The time now is 11:31 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft