Hi,

I am trying to extract all tables from multiple word documents in one excel sheet. I can do it with the code below, but i need that every word document content (tables) to be on one line in excel not multiple lines. Also the name of the document is important to be at the beggining of the line and i can't find a solution for that. Can anyone help me? Thank you!

Option Explicit
 
Sub test()
 
Dim oWord As Word.Application
Dim oDoc As Word.Document
Dim oCell As Word.Cell
Dim tbl
Dim sPath As String
Dim sFile As String
Dim r As Long
Dim c As Long
Dim Cnt As Long
 
Application.ScreenUpdating = False
 
Set oWord = CreateObject("Word.Application")
 
sPath = "c:\Desktop\doc\" 'change the path accordingly
 
If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
 
sFile = Dir(sPath & "*.doc")
 
r = 2 'starting row
c = 1 'starting column
Cnt = 0
Do While Len(sFile) > 0
    Cnt = Cnt + 1
    Set oDoc = oWord.Documents.Open(sPath & sFile)
For Each tbl In oDoc.Tables
    For Each oCell In tbl.Range.Cells
        Cells(r, c).Value = Replace(oCell.Range.Text, Chr(13) & Chr(7), "")
        c = c + 1
    Next oCell
    r = r + 1 'couple of blank rows between tables
    c = 1
Next tbl
    oDoc.Close savechanges:=False
    r = r + 1
    c = 1
    sFile = Dir
Loop
 
Application.ScreenUpdating = True
 
If Cnt = 0 Then
    MsgBox "No Word documents were found...", vbExclamation
End If
 
End Sub