CODEWITHSUNDEEP
excelvbams-word
Gen Tan
Gen Tan

Reputation: 918

Populate an excel sheet with data from Word

I have a word document, which looks like this:

Customer name: _____

Customer ID: _____

[Button] "Click here to add another customer"

Users will fill in the customer name and customer ID.

I have a separate excel workbook. It looks like this:

Customer Name:

Customer ID:

I want to add a button on the Excel sheet: When I press the button, a pop up box will appear, asking me From which file I want to extract data from. I will select the word document, and the Excel sheet will be auto populated from the data in the Word document.

I have tried experimenting with Word's Quickparts -> fields -> database. But I only managed to extract data from excel into the word document.

How should I approach this problem? Any functions I am missing?

Upvotes: 0

Views: 2462

Answers (1)

MarcinSzaleniec
MarcinSzaleniec

Reputation: 2256

If you want to take values from Word to Excel using the button in the Excel spreadsheet, you need to control Word from Excel. There are two ways, early and late binding. Early binding is easier, so let's begin with it. In Excel VBA editor, click Tools menu, then open References. Find Microsoft Word XX Object Library and tick it. You can now use Word controls in Excel VBA Editor.

Declare variables:

Sub DateFromWord()

Dim appW As Word.Application
Dim ADoc As Document
Dim ph As Paragraph
Dim strR(1) As String
Dim i As Integer

and then set ADoc as your opened Word document.

Set appW = GetObject(, "Word.Application")
Set ADoc = ActiveDocument

This is very simple way. I have assumed that you have only one Word document and you have it opened. It would be more sophisticated if you wanted to choose docs from file or something. But you have asked how to approach the problem, not the tailored solution, haven't you?

Then you may loop through paragraphs in your document. 

For Each ph In ADoc.Paragraphs
  'Do something
Next ph

What you should to inside the loop? My suggestion is to check, if there is ":" in the paragraph. If it is, then before it you have either "custoemr id" or "customer name" and after it you have actual name or id. The loop should be like this:

For Each ph In ADoc.Paragraphs
    If InStr(ph.Range, ":") Then
        For i = 0 To 1
            strR(i) = Split(ph.Range, ":")(i)
        Next i
        Debug.Print strR(0) & "    " & strR(1)
    End If
Next ph

Instead of debug.print you should put values in cells of the Excel or do whatever you want with those string. Any questions?

Upvotes: 1

Related Questions