Excel macro - Copy word to excel

[jimmy11]

Hi,
I am trying to copy a data from word to excel ( multiple files)…Now we are able to copy the data from word to excel, but would like to add simple two steps:
1. Transpose (the rightmost cells only) and copy/paste row one in the result sheet. Then the second file will be pasted row 2 an so an.
2.Since we are copying many files( 100 files) from word to excel, would like to delete the previous file from the sheet1 and also get prompt to select the second file ( the new file to copy/paste)

Sample excel file attached..The Result tab is the desired result

Below is the code we are now using..
Code:

Sub CopyWordTable()
'requires a reference to Word Object Library (Tools - References)
Dim appWord As Word.Application
Dim docWord As Word.Document
Dim strDoc As String
Dim tableWord As Word.Table

'Word document and this Excel workbook are in the same folder
'Table.doc contains a table formatted with various colours, fonts, borders and bullets

strDoc = ThisWorkbook.Path & "\" & "file one.doc"

Set appWord = New Word.Application
appWord.Visible = True

Set docWord = appWord.Documents.Open(strDoc)

'copy first table in document to clipboard
Set tableWord = docWord.Tables(1)
tableWord.Range.Copy

'paste table on worksheet
ActiveSheet.Paste Destination:=ActiveSheet.Range("A2")

docWord.Close
appWord.Quit

Set tableWord = Nothing
Set docWord = Nothing
Set appWord = Nothing

End Sub

Thanks in advance...

Jimmy

[Kenneth Hobs]

Welcome to the forum!

Because your request is a bit involved, you have not received any responses until now most likely.

I understand what you want by your clear example but I wonder if it is the best approach. If I were doing your project, I would iterate through your MSWord table cells and copy to the next empty row in the current sheet or another.

Also, rather than selecting one file at a time, you could speed your macro up by processing all DOC files in a specific folder or files based on some file naming convention like Inventory001.doc, Inventory002.doc, etc. Or, you could use a dialog to select multiple files in a listbox. Of course order would not be as you might want but a sort should restore some order if needed.

So, ponder these issues and post back so that the best solution can be pursued.