Selected Answer
Ghost
Given your Word document is a template used repeatedly, you can populate the CustomerName (from the non-table text) using the ducument's Paragraphs collection and its index number e.g. Customer Name: Billy Joe is paragraph 4.
In the revised macro below (changes in bold, in revised file attached), I've declared new String Variables (like CustName) and got the value from the associated paragraph index number in the template. CustDate is a bit different (since you need to convert the date format into a real date).
Sub GetDocument()
Dim worddoc As Object
Dim setFilename As Variant
Dim Table_ As Integer
Dim iRow As Long
Dim iCol As Integer
Dim NextRw As Integer
Dim CustName As String, CustOrder As String, CustBank As String, CustReason As String
Dim CustDate As Date
setFilename = Application.GetOpenFilename("Word files,*.doc;*.docx", , "Browse for the file")
If setFilename = False Then Exit Sub '##corrected
Set worddoc = GetObject(setFilename)
With worddoc
If worddoc.tables.Count <> 1 Then Exit Sub 'reduced to one line test
' get values from paragaphs
CustName = Replace(.Paragraphs(4).Range, "CustomerName: ", "")
CustDate = DateValue(Replace(.Paragraphs(5).Range, "Date Order: ", ""))
CustOrder = Replace(.Paragraphs(6).Range, "Ordernumber: ", "")
CustBank = Replace(.Paragraphs(7).Range, "Banknumber: ", "")
CustReason = Replace(.Paragraphs(8).Range, "Reason: ", "")
' Write to Sheet1
With Sheet1.Cells(Sheet1.Range("A" & Rows.Count).End(xlUp).Row + 1, 1)
.Value = CustName
.Offset(0, 1) = CustDate
.Offset(0, 2) = CustOrder
.Offset(0, 3) = CustBank
.Offset(0, 4) = CustReason
End With
With .tables(1) 'modified With (given reduced test above)
For iRow = 1 To .Rows.Count
NextRw = Sheet2.Range("A" & Rows.Count).End(xlUp).Row + 1
For iCol = 1 To .Columns.Count
Sheet2.Cells(NextRw, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
' add customer name to E from para 4, ignoring blank rows/columns
If Sheet2.Cells(NextRw, 1).Value <> "" Then Sheet2.Cells(NextRw, 5) = CustName
Next iCol
Next iRow
End With
End With
Set worddoc = Nothing
End Sub
You'll see that I changed your code:
Table_ = worddoc.tables.Count
If Table_ = 0 Then
Exit Sub
ElseIf Table_ > 1 Then
'more then 1 table
Exit Sub
End If
With .tables(Table_)
to become the simpler :
If worddoc.tables.Count<>1 Then Exit Sub
followed by (later):
With .tables(1)
Also, your template seems to have 8 rows in the table which might be unused (plus a blank column columns) so I added the test/ instruction:
If Sheet2.Cells(NextRw, 1).Value <> "" Then Sheet2.Cells(NextRw, 5) = CustName
If you want to modify your macro to loop through several Word files, see Don's tutorial Fast Search Multiple Workbooks in Excel with a File Picker - VBA Macro, especially the Sub Search_Separate_Workbooks() and put your word code after the line:
'Loop through selected files.
For Each fileSelected In filePicker.SelectedItems
modifying it to suit.
Hope this makes sense/ fixes your problem.
