#1
|
|||
|
|||
Returns in Word table text become multiple rows when pasted in Excel
I'm working on improving invoicing for a company I work with, and I'm trying to make their Word tables more practical for my use in Excel. I want to create a template in Word that will be easily pasted into Excel (and then importable into my own database).
Below is what I've created in Word: Which works unless someone uses returns/paragraph breaks (which they are wont to do, especially with addresses). Below is what it looks like when I paste the above into Excel: This makes the table totally unsortable without a lot of processing. I'd love to be able to sort by name. Is there a way to prevent cells from creating new rows in a Word table when there are line breaks? Maybe a way to force only one line of text per cell (that would be wrapped within the cell)? Thanks! |
#2
|
||||
|
||||
The behaviour is normal and cannot be changed. That said, you can sort a table in Word.
In any event, if you want to import the data into a database, you'll need separate fields for the address, locality, state and postcode.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Hmm. That may just have to do. Thanks!
|
#4
|
||||
|
||||
If you're wedded to having the data in Excel, you could use a macro like:
Code:
Sub Demo() Application.ScreenUpdating = False Dim xlObj As Object, xlWkBkObj As Object With Selection.Tables(1).Range 'Pre-format the table With .Find .ClearFormatting .Replacement.ClearFormatting .Text = "[^13^l]" .Replacement.Text = Chr(182) .Forward = True .Wrap = wdFindStop .Format = False .MatchWildcards = True .Execute Replace:=wdReplaceAll End With 'Copy the table .Copy End With ActiveDocument.Undo 'Start Excel if not running On Error Resume Next Set xlObj = GetObject(, "Excel.Application") ' Start Excel if it isn't running If xlObj Is Nothing Then Set xlObj = CreateObject("Excel.Application") With xlObj 'Add a workbook Set xlWkBkObj = .Workbooks.Add With xlWkBkObj.Sheets(1) 'Paste the data .Paste '.Range("A1") 'Post-format the data With .UsedRange .HorizontalAlignment = 1 'xlGeneral .WrapText = False .Columns.AutoFit .Replace Chr(182), Chr(10) .Columns.AutoFit .Rows.AutoFit End With End With .CutCopyMode = False .Visible = True End With Application.ScreenUpdating = True End Sub For Mac macro installation & usage instructions, see: https://wordmvp.com/Mac/InstallMacro.html
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Forgot to say this code works like a dream! Thanks so much.
|
#6
|
|||
|
|||
Could a macro insert a column that contains the filename of the document in every cell?
I have invoices sent to me in the form of Word tables, each with the filename as the date of the invoice. The table itself does not contain the date, so I am wondering if there is a way to insert a column (preferably on the left) where the cells contain the filename (the date) in every row. Eventually I combine each file to one spreadsheet, so having the date in the first column would be fantastic.
I receive 7 invoices at once each week, so any "today" functions aren't really useful. Any thoughts? |
#7
|
||||
|
||||
Is this related to: https://www.msofficeforums.com/word-...iple-rows.html ?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#8
|
|||
|
|||
It is, although it doesn't have to be done in conjunction with the Word-to-Excel task we discussed there. Apologies if I shouldn't have made a new thread, it just seemed like a different topic to me.
|
#9
|
||||
|
||||
Threads merged.
It would have been helpful if you'd said up-front what the full requirements are. In addition to needing the additional column, you're evidently pasting multiple tables into the same Excel workbook, so pasting into a new, empty, workbook isn't the end of the matter. Presumably you also need to add the data to below the existing data. At which point it becomes better to automate this from Excel so multiple documents can be processed instead of doing things piecemeal. Accordingly, please provide the full details of what you're trying to achieve.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Tags |
enter, filename as document text, insert column, paste, rows, vba in word |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
a table pasted in word from excel is not similar to the original table in excel | Mukapa | Word Tables | 1 | 01-18-2016 07:31 AM |
Modify a just-pasted Excel table | p45cal | Word VBA | 2 | 09-26-2015 01:02 PM |
Excel Multiple Rows Merged into Word (Vertical Format) | ats1025 | Mail Merge | 3 | 11-19-2013 02:21 PM |
Add multiple rows to Excel 2010 table at cursor position | C J Squibb | Excel Programming | 12 | 11-07-2013 07:35 AM |
connectors between cells in pasted Excel table | eNGiNe | Word | 7 | 10-16-2013 11:58 PM |