Jumping Jack Flash weblog

How to paste Word tables into Excel preventing Excel from splitting cells

Posted in Programmazione by jumpjack on 24 aprile 2013

When you copy a table from Word to Excel, if a cell contains carriage returns it will be splitted into two or more cells when pasting into Excel.

Following macros prevent such behaviour, by temporary replacing carriage returns into clipboard by specific strings and then restoring them once table is copied into Excel.

==== Insert into Word Module =====

Public Sub CopyTableForExcel()
' Select whole table in Word and start this macro to copy table in clipboard.
' Then switch to Excel and run PasteTableFromWord macro

Dim Newdoc As Document
If Selection.Information(wdWithInTable) = False Then
   MsgBox "Selection is not in a table.  Exiting macro."
   Exit Sub
End If
Selection.Tables(1).Select ' Select whole table
Selection.Copy
Set Newdoc = Documents.Add(, , , True) ' Create a temporary document to store modded table.
Newdoc.Activate
Selection.Paste ' Paste the table in the new document.
 
   ' Replace all carriage returns (ascii 13) by "[exVbLF]" string:
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = Chr(13)
        .Replacement.Text = "[exVbLF]"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    
   ' Replace all carriage returns (VbCR) by "[exVbLF]" string:
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = vbCr
        .Replacement.Text = "[exVbLF]"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    
' Copy modded table into clipboard:
    ActiveDocument.Tables(1).Select
    Selection.Copy
 
    ActiveDocument.Close (wdDoNotSaveChanges) ' Close temporary document
    MsgBox ("Table copied into clipboard. Start Excel macro to paste the table into Excel.")
End Sub
  

 

==== Insert into Excel Module ====

 
Sub PasteTableFromWord()
' Use in Excel after using CopyTableForExcel() in Word
    ActiveSheet.Paste ' Paste modded table
    Cells.Select ' select whole sheet (necessary?)
 
' Restore carriage returns inside cells:
    Selection.Replace What:="[exVbLf]", Replacement:=vbLf, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 
' Fix the table
    Selection.EntireColumn.AutoFit
    Rows("1:1").EntireRow.AutoFit
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlTop
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

Installation:

– Copy first macro into a Word module in NORMAL.DOT (common to all documents)

– Copy second macro into an Excel module in PERSONAL.XLS (common to all documents)

Usage:

– Select table in Word

– Start CopyTableForExcel macro

– Switch to Excel destination sheet

– Start PasteTableFromWord macro

Following version of the Macros also turns all “.” (dots)  in the Word table into “,” (commas) in Excel table: this is needed because my Italian version of Excel uses commas to separate decimals and I often have to copy english-formatted tables (with dots for decimals) into italian-formatted Excel sheets (with commas for decimals).

==== Insert into Word Module =====

Public Sub CopyTableForExcel()
' Select whole table in Word and start macro to copy table in clipboard.
' NOTE: macro also replaces "." (dots) by "," (commas), as it is used to paste
' tables into Italian version of Excel, which uses commas rather than dots
' to separate decimals; comment-out the related part of the code if your Excel
' version uses dots for decimals.
 
Dim Newdoc As Document
    If Selection.Information(wdWithInTable) = False Then
        MsgBox "Selection is not in a table.  Exiting macro."
        Exit Sub
    End If
    Selection.Tables(1).Select ' Select whole table
   Selection.Copy
Set Newdoc = Documents.Add(, , , True) ' Create a temporary document to store modded table.
Newdoc.Activate
Selection.Paste ' Paste the table in the new document.
 
   ' Replace all carriage returns (ascii 13) by "[exVbLF]" string:
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = Chr(13)
        .Replacement.Text = "[exVbLF]"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    
   ' Replace all carriage returns (VbCR) by "[exVbLF]" string:
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = vbCr
        .Replacement.Text = "[exVbLF]"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    
 ' Replace all dots by commas; needed to translate from UK decimal to Italian decimal numbers.:
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = "."
        .Replacement.Text = ","
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
 
 
' Copy modded table into clipboard:
    ActiveDocument.Tables(1).Select
    Selection.Copy
 
    ActiveDocument.Close (wdDoNotSaveChanges) ' Close temporary document
    MsgBox ("Table copied into clipboard. Start Excel macro to paste the table into Excel.")
End Sub
 
 
 
==== Insert into Excel Module ====
 
Sub PasteTableFromWord()
' Use in Excel after using CopyTableForExcel() in Word
    ActiveSheet.Paste ' Paste modded table
    Cells.Select ' select whole sheet (necessary?)
 
' Restore carriage returns inside cells:
    Selection.Replace What:="[exVbLf]", Replacement:=vbLf, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 
' Fix the table
    Selection.EntireColumn.AutoFit
    Rows("1:1").EntireRow.AutoFit
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlTop
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

2 Risposte

Subscribe to comments with RSS.

  1. juicing recipes For weight loss said, on 21 agosto 2014 at 20:02

    I drop a comment when I especially enjoy a article on a website or if I have something to valuable to contribute to the conversation. Usually it is caused by the fire communicated in the post I looked at.
    And after this article How to paste Word tables
    into Excel preventing Excel from splitting
    cells | Jumping Jack Flash weblog. I was actually excited enough
    to drop a thought😛 I do have a few questions for you
    if you tend not to mind. Is it simply me or do some of these
    responses come across like they are coming from brain dead folks?😛 And, if you are writing at additional places, I would like to follow you.
    Would you list all of all your communal pages like your linkedin profile,
    Facebook page or twitter feed?

    • jumpjack said, on 21 agosto 2014 at 22:41

      My congratulations, this is the most complex spam comment I ever received. Cool idea toautomatically include post title into the text to make it look real.
      I hope your mother will die soon, moron.


Puoi inserire un commento qui sotto; diventerà visibile dopo la moderazione dell'amministratore

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

%d blogger cliccano Mi Piace per questo: