Consulting

Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 21 to 40 of 41

Thread: Solved: Excel macro to extract Word form data into Excel

  1. #21
    VBAX Regular
    Joined
    Feb 2013
    Posts
    8
    Location

    Document won't open

    Macropod,

    I was able to figure out the problem, but now the excelsheet doesn't load the data in the word doc. The word doc appears for few seconds and closes.

    Quote Originally Posted by Bill70
    Hello Macropod,
    I am new to macro and I am using you code to extract data from a word document. But, "wdApp As New Word.Application" is highlighted" and I get the following error: "User-defined type not defined"
    Any idea what the problem is?

    I am using MS Office Pro 2010

  2. #22
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    The macro is for running from Excel. In Excel's vbe you need to set a reference to the Word Object library, which you do via Tools|References.

    As for the document, the macro opens it, reads in the formfield data, then closes the document. There is no reason to keep it open.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #23
    VBAX Regular
    Joined
    Feb 2013
    Posts
    8
    Location
    Thanks for the reply.
    The other issue is that it doesn't pull the data from the word document.
    Any suggestion?
    Quote Originally Posted by macropod
    The macro is for running from Excel. In Excel's vbe you need to set a reference to the Word Object library, which you do via Tools|References.

    As for the document, the macro opens it, reads in the formfield data, then closes the document. There is no reason to keep it open.

  4. #24
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Have you replaced "Path & Filename" with the real Path & Filename for your document? Does your document actually contain any formfields?
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #25
    VBAX Regular
    Joined
    Feb 2013
    Posts
    8
    Location
    Yes, I replaced "Path & Filename" with the actual path and filename.
    No, the ducument doesn't contain any form field

    Quote Originally Posted by macropod
    Have you replaced "Path & Filename" with the real Path & Filename for your document? Does your document actually contain any formfields?

  6. #26
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    @Bill70
    You need to add a reference to the Microsoft word library

  7. #27
    VBAX Regular
    Joined
    Feb 2013
    Posts
    8
    Location
    Thank you, but that was done already. The problem now is that the macro doesn't pull data from my word document.

  8. #28
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by Bill70
    No, the ducument doesn't contain any form field
    So why are you using this macro? All of the discussion in this thread has concerned the extraction of formfield data. No more, no less.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  9. #29
    VBAX Regular
    Joined
    Feb 2013
    Posts
    8
    Location
    Oops, I guess I misread the original post.
    I actually need a macro that will pull data from a word document.

  10. #30
    VBAX Regular
    Joined
    Feb 2013
    Posts
    8
    Location
    Any help will be appreciated.

  11. #31
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    In that case, find some code in a different thread that does what you're after* or, if you can't find one, start a new thread setting out exactly what it is you need to do - saying something as vague as "I actually need a macro that will pull data from a word document" won't do. You need to say exactly how a macro is to identify that data.

    * See, for example, http://www.vbaexpress.com/forum/showthread.php?t=42850. You may be able to tweak the code there to suit your needs
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  12. #32
    VBAX Regular
    Joined
    Feb 2013
    Posts
    8
    Location
    Thanks macropod,
    I will look at the example.

  13. #33
    Sorry for bumping an old thread but this seems to do close to what I am looking for with only a couple changes.

    Instead of using only one file, I am trying to get a file dialog box to open allowing me to choose which word file I want to extract data from.

    My document template has 8-10 content control text fields but I only want to import the data from 4 of them (tagged: Mar, Jun, Sept, Dec). The data from each content control text should go into its own cell starting at whichever cell is selected and going across the row.

    I posted this question in a new thread here although in a much more confusing manner. I found this thread during a search on another board.

  14. #34
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    You could try something like:
    [VBA]Sub InsertFormfieldResults()
    Application.ScreenUpdating = False
    Dim lRow As Long, i As Long, j As Long, StrFlNm As String
    With Application.Dialogs(xlDialogFindFile)
    SendKeys "%n *.doc ~"
    If .Show = True Then
    StrFlNm = .Name
    Else
    Exit Sub
    End If
    End With
    Dim xlWkSht As Worksheet
    Set xlWkSht = ActiveSheet
    lRow = xlWkSht.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
    Dim wdApp As New Word.Application
    Dim wdDoc As Word.Document
    wdApp.Visible = True
    Set wdDoc = wdApp.Documents.Open(Filename:=StrFlNm, AddToRecentFiles:=False)
    With wdDoc
    For i = 1 To .FormFields.Count
    xlWkSht.Cells(lRow + Int((i - 1) / 5), ((i - 1) Mod 5) + 1).Value = .FormFields(i).Result
    Next
    .Close SaveChanges:=False
    End With
    wdApp.Quit
    Set wdDoc = Nothing: Set wdApp = Nothing
    Application.ScreenUpdating = True
    End Sub[/VBA]
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  15. #35
    I was getting the 400 error so I added some code to describe the error I am getting and it is a "file format is not valid" error. This occurs after I select the document I want to open. Any ideas of what I might change?

    Edit:
    The code I added was:
    On Error GoTo Errorcatch
    
    etc.
     
    exit sub
     
    Errorcatch:
        MsgBox Err.Description

  16. #36
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Sorry, I chose the wrong dialogue. Change:
    With Application.Dialogs(xlDialogFindFile)
    to:
    With Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)
    and change:
    StrFlNm = .Name
    to:
    StrFlNm = .SelectedItems(1)
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  17. #37
    The code is running fine now, the document I choose is opening and closing however no text is being transferred to the excel spreadsheet. I assume I would need to define the tags for the fields I wish to copy but I am not sure where to do that.

    Thanks for all your help so far!!

    Sub InsertFormfieldResults()
        Application.ScreenUpdating = False
        Dim lRow As Long, i As Long, j As Long, StrFlNm As String
        With Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)
            SendKeys "%n *.doc ~"
            If .Show = True Then
                StrFlNm = .SelectedItems(1)
            Else
                Exit Sub
            End If
        End With
        Dim xlWkSht As Worksheet
        Set xlWkSht = ActiveSheet
        lRow = xlWkSht.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
        Dim wdApp As New Word.Application
        Dim wdDoc As Word.Document
        wdApp.Visible = True
        Set wdDoc = wdApp.Documents.Open(Filename:=StrFlNm, AddToRecentFiles:=False)
        With wdDoc
            For i = 1 To .FormFields.Count
                xlWkSht.Cells(lRow + Int((i - 1) / 5), ((i - 1) Mod 5) + 1).Value = .FormFields(i).Result
            Next
            .Close SaveChanges:=False
        End With
        wdApp.Quit
        Set wdDoc = Nothing: Set wdApp = Nothing
        Application.ScreenUpdating = True
    End Sub

  18. #38
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    On re-reading your initial post, I see that you are using content controls, whereas as explained previously (most recently in post #28), the code is for extracting data from formfields.

    For your purposes, you will need to change:
    [vba]For i = 1 To .FormFields.Count
    xlWkSht.Cells(lRow + Int((i - 1) / 5), ((i - 1) Mod 5) + 1).Value = .FormFields(i).Result
    Next[/vba]
    to:
    [vba]For i = 1 To .ContentControls.Count
    xlWkSht.Cells(lRow + Int((i - 1) / 5), ((i - 1) Mod 5) + 1).Value = .ContentControls(i).Range.Text
    Next[/vba]
    Last edited by macropod; 03-18-2013 at 03:18 PM.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  19. #39
    This is SUPER close, the only change I'm looking for is that the current code grabs the input from every content control in the document. I wish to only choose a few of the content controls to gather and keep them on the same row in the sheet.

    These content control tags will never change (I will always only want the info from content control text tagged (Mar, Jun, Sept, Dec)) whereas the document has multiple untagged content control text that the user fills in which for my report are irrelevant.

    I can't thank you enough for getting me this far!

  20. #40
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Try something along the lines of:
    [VBA]For i = 1 To .ContentControls.Count
    Select Case .ContentControls(i).Tag
    Case "Mar": xlWkSht.Cells(lRow, 1).Value = .ContentControls(i).Range.Text
    Case "Jun": xlWkSht.Cells(lRow, 2).Value = .ContentControls(i).Range.Text
    Case "Sept": xlWkSht.Cells(lRow, 3).Value = .ContentControls(i).Range.Text
    Case "Dec": xlWkSht.Cells(lRow, 4).Value = .ContentControls(i).Range.Text
    End Select
    Next[/VBA]
    where 1, 2, 3 & 4 represent the destination column #s.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •