replacing multiple ranges in excel with tables in word from top to bottom

modiria50989 · #1 08-31-2017, 08:56 AM

I have a code that pastes word tables from a word file into the sheet1 in Excel. Then I go to the sheet1 and without changing header names and data's location, I revise the original data to new data. Now, here is my question: how to return those revised data back to the Word document. In other words I want to see all tables into the word file with updated information.

My question in different way:
If you have a word document with a few tables in it, then if you go to Excel and run the bellow code from Excel VBA module? You will see how a browser opens to select a word file, then each table will pasted from row 1 and it goes all the way down and there would be 1 empty row between each table-range data. So i can revise all values I want.
Now my question is: how to replace word tables with those individual ranges (table ranges) in excel?
Note that in the excel we can recognize boundaries of each table since there is a empty row between them.
Please let me know if you need more explanation.

Just in case: my word file includes a lot of lines, paragraphs, and a few tables.

Here is my code that I paste word-tables into the excel sheet (how to return it back?):

Code:

Option Explicit
Sub ImportWordTable()
Dim wdDoc AsObject
Dim wdFileName AsVariant
Dim tableNo AsInteger'table number in Word
Dim iRow AsLong'row index in Excel
Dim iCol AsInteger'column index in Excel
Dim resultRow AsLong
Dim tableStart AsInteger
Dim tableTot AsInteger
OnErrorResumeNext
ActiveSheet.Range("A:AZ").ClearContents
wdFileName = Application.GetOpenFilename("Word files (*.doc),*.doc", , _
"Browse for file containing table to be imported")
If wdFileName = FalseThenExitSub'(user cancelled import file browser)
Set wdDoc = GetObject(wdFileName) 'open Word file
With wdDoc
tableNo = wdDoc.tables.Count
tableTot = wdDoc.tables.Count
If tableNo = 0Then
MsgBox "This document contains no tables", _
vbExclamation, "Import Word Table"
ElseIf tableNo > 1Then
tableNo = InputBox("This Word document contains " & tableNo & " tables." & vbCrLf & _
"Enter the table to start from", "Import Word Table", "1")
EndIf
resultRow = 1
For tableStart = 1To tableTot
With .tables(tableStart)
'copy cell contents from Word table cells to Excel cells
For iRow = 1To .Rows.Count
For iCol = 1To .Columns.Count
Cells(resultRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
Next iCol
resultRow = resultRow + 1
Next iRow
EndWith
resultRow = resultRow + 1
Next tableStart
EndWith
EndSub

macropod · #2 08-31-2017, 03:13 PM

That's a bit like trying to unscramble an omelette. All you're outputting to Excel is the text of each cell, with none of its formatting. And, if a cell in Word contains a paragraph break or manual line break, any content after it will be output with those breaks removed. Consequently, anything you write back to the tables from Excel cannot be guaranteed to restore either the original formatting or content. If that's not a concern, I suggest you spend a little time studying how the code you posted works - there's only one line in it you'd need to change to reverse the process:
Cells(resultRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
should become:
.Cell(iRow, iCol).Range.Text = Cells(resultRow, iCol)

Finally, kindly don't start multiple threads on the same topic; I've deleted the other one.

PS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.

modiria50989 · #3 08-31-2017, 03:38 PM

Can you please make your own word document with a few tables in it, then go to Excel and run it from Excel VBA module? You will see how each table is pasted from row 1 and it goes all the way down and there would be 1 empty row between each table-range data.
Now my question is: how to replace word tables with those individual ranges (table ranges) in excel?
Note that in the excel we can recognize boundaries of each table since there is a empty row between them.
Please let me know if you need more explanation.

macropod · #4 08-31-2017, 03:44 PM

No, I am not going to waste my time creating documents, tables and workbooks when, if you wanted someone to see what you're working with, you could attach your own. Besides which, replacing the Word table from your Excel workbook almost certainly would result in layout changes in addition to the data and formatting changes I already mentioned.

Plus, aside from the fact only one line of your code needs changing to update the existing tables (and I've already posted that), a simple web search would turn up lots of code for exporting content from Excel to Word tables.

Furthermore, you still haven't added code tags to your original post...

macropod · #5 09-12-2017, 03:13 PM

Cross-posted at: http://www.tek-tips.com/viewthread.cfm?qid=1780295
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

macropod · #6 09-13-2017, 04:01 PM

This is the third consecutive thread in which you have cross-posted without providing links. As per the warning you were given in: https://www.msofficeforums.com/word-...tml#post118947 I have now terminated your account here.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Take String of numbers, expand ranges, sort, then compress back into ranges	AustinBrister	Word VBA	19	08-22-2016 05:18 PM
Creating multiple tables VBA from Excel to Word	francesco	Word VBA	1	01-07-2016 04:08 AM
using if and compare with multiple criteria and ranges of values	bill3kradio	Mail Merge	7	11-17-2014 01:06 PM
Replacing multiple words at once in excel document	Legaly	Excel	2	05-23-2014 08:30 AM
Copying Multiple tables from excel into a single word document	dineshtgs	Word Tables	1	04-07-2011 01:27 AM