Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)

Import Word file into Excel sheet

0

Hello,

I want to import data text and table from a word document and if possible multi files

I have only the Table vba part but not for the text part :(

In the first sheet i want the text above the table in this sheet (I have added the sample text on this sheet1)

In second sheet i want to add the table (I have added the sample text on this sheet2)

I only gets the Table in my sheet 2 but i also need to get the customer name on each row where the data is on this sheet2 and if possible i need to get the text from the document above the  table on sheet 1 aswell

I'm afraid i am stuck with this part :(

Any help with this please

and its really slow to copy the wordfile to the sheets?

Thank you

Answer
Discuss

Discussion

Ghost

For privacy reasons I don't plan to request access to your Google Drive so please modify your xlsm to embed the sample Word document (as an icon, on sheet) so I can understand your question and provide an error.

p.s. your code has an error that stops it running, should read:
setFilename = Application.GetOpenFilename("Word files,*.doc;*.docx", , "Browse for the file")
 
    If setFilename = False Then Exit Sub '##corrected
John_Ru (rep: 2857) Jan 15, '22 at 5:12 am
Hi John_Ru
Oops my mistake sometimes i type to fast :)
ok, i will change my file and delete the google file
I understand.
i have added the word document in the sample -- see sheet word File
Didn't know you can do that :D thanks for the suggestion.
GhostofWanted (rep: 42) Jan 15, '22 at 5:18 am
Thanks. Will all your Word "Payment" documents take this form (i.e. is the Payment from, CustomerName etc. always in the same place)?
John_Ru (rep: 2857) Jan 15, '22 at 6:00 am
Yes they do
It is a template we always use over and over again
GhostofWanted (rep: 42) Jan 15, '22 at 7:23 am
Add to Discussion

Answers

0
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.

Discuss

Discussion

wow you are so amazing John_Ru
Yes this is what i needed and working faster then mine aswell
Also, any idea's about the multi imports with more documents at once?

Thanks
GhostofWanted (rep: 42) Jan 15, '22 at 7:52 am
I don't have time to do it but see my modified Answer (and the tutorial) for a big clue in "multi-imports". Don't forget to mark the Answer as Selected please.
John_Ru (rep: 2857) Jan 15, '22 at 8:02 am
Thanks so much John_Ru
I will get into your tutorial

Thanks alot ;)
GhostofWanted (rep: 42) Jan 15, '22 at 8:05 am
Okay but Don deserves the credit for all the tutorials (and this site./ Premium Courses), not me. Have a good weekend.
John_Ru (rep: 2857) Jan 15, '22 at 8:06 am
Thanks for you to John_Ru
:D yeah Don is amazing also with his great Tutorials
GhostofWanted (rep: 42) Jan 15, '22 at 8:22 am
Awseome, like you said John_Ru
I did use Don tutorial and i can add multi file to open them at once
Wow so cool :D
Now i can work on my design.
Thanks again
GhostofWanted (rep: 42) Jan 15, '22 at 8:28 am
Good stuff! Have fun
John_Ru (rep: 2857) Jan 15, '22 at 8:42 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login