PROBLEM WITH METHOD 03: It works with only 1 cell, but as soon as I attempt to get more than one cell copied and pasted over, the result is the same data over and over again across multiple cells. 'In Excel, Save changes to cell and move 1 cell to the right 'Select the appropriate cell in the active Excel worksheet (based on the For Loop)

'for some reason Application.caption works for me. 'Note: I'm not sure why AppActivate ('excel') does not work, but 'Select the appropriate cell in the Word table (based on the For Loop)ĪctiveDocument.Tables(1).Cell(r, c).Select 'Loop through the 3x3 Source table and simulate sending keys to copy and paste, cell by cell METHOD 03: Use Application.Sendkeys to simulate the process of manually going to one cell of the Word table, copying it, going to the corresponding cell in the Excel table, pasting it, pressing to go to the next cell, and then repeating this process for the total number of cells in the table. Specifically, the bullet points do not appear in the Excel table. PROBLEM WITH METHOD 02: The formatting is not preserved. 'Paste the array values into the activesheet starting at cell(1,1)Ĭells(r, c) = WorksheetFunction.Clean(AryTblData(r, c)) 'Loop through the 10x10 Source table and assign all values to 2 dimensional arrayĪryTblData(r, c) = ActiveDocument.Tables(1).Cell(r + 1, c) Sub Method02_Array()ĭim AryTblData(1 To 10, 1 To 10) As Variant Then loop through the Excel table and assign the values from the Array into the table. METHOD 02: Loop through the Word table, and assign all the contents to a Array (variant).

PROBLEM WITH METHOD 01: Excel creates multiple rows and merged cells whenever there are multiple paragraphs in the Word Table. METHOD 01: Select the entire table in Word, select a single cell in Excel, and paste. Solutions I've already tried and specifically where I got stuck:

More specifically, I need to include all that formatted text from a cell from the Word table inside a single cell in the corresponding Excel table. The challenge: Maintain the formatting of the data from Word, which includes bulleted lists and multiple paragraphs. The task is simple - Copy data from a table from Microsoft Word to Microsoft Excel. I've been coding VBA for many years but this one is a real head-scratcher.

Copy word table to excel