Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 32 of 32

Thread: Extracting Word form Data and exporting to Excel spreadsheet

  1. #21
    Quote Originally Posted by gravenrj View Post
    This macro is brilliant macropod (Paul)! Is there an easy way to modify the code to choose an individual file rather than a file folder? I see that you used the shell.BrowseForFolder method in the UDF, is there an equivalent browing for files? I apologize if this is a basic question, I am a VBA neophyte and for the life of me cannot find an easy way to select and store an individual file path if the file is stored in a folder with other files.
    Nevermind, I was able to update this on my own by modifying the code that macropod provided and using Application.GetOpenFileName. I realize this isn't pretty and the while loop is redundant for the single file, but hey, it works! I have this as part of a userform, hence the private sub. Here it is:

    Private Sub File_Button_Click()
    Application.ScreenUpdating = False
    Dim wdApp As New Word.Application
    Dim wdDoc As Word.Document
    Dim CCtrl As Word.ContentControl
    Dim strFile As String
    Dim WkSht As Worksheet, i As Long, j As Long

    strFile = Application.GetOpenFilename
    If strFile = "" Or strFile = "False" Then Exit Sub
    Set WkSht = ActiveSheet
    i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
    While strFile <> ""
    i = i + 1
    Set wdDoc = wdApp.Documents.Open(Filename:=strFile, AddToRecentFiles:=False, Visible:=False)
    With wdDoc
    j = 0
    For Each CCtrl In .ContentControls
    j = j + 1
    WkSht.Cells(i, j) = CCtrl.Range.Text
    Next
    End With
    wdDoc.Close SaveChanges:=False
    strFile = ""
    Wend
    wdApp.Quit
    Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing 'T
    Application.ScreenUpdating = True
    Call Cancel_Click
    End Sub

  2. #22
    Quote Originally Posted by macropod View Post
    Hi candameron,

    The following Excel macro will pull the data from all content controls in all Word files in a specified folder into the active worksheet. To run it, simply press Alt-F8 and choose the 'GetFormData' macro. You'll be asked to navigate to the source folder. Once you've done that, the macro will populate the worksheet. If you want, you could create a keyboard shortcut or a button to trigger the macro.

    If you initially put just one document containing meaningful data in all of the content controls into the 'source' folder, the macro will go through through the content controls and output the data into the worksheet. you should then be able to marry-up the output with whatever column headings you'll want in the workbook.
    Sub GetFormData() 
         'Note: this code requires a reference to the Word object model
        Application.ScreenUpdating = False 
        Dim wdApp As New Word.Application 
        Dim wdDoc As Word.Document 
        Dim CCtrl As Word.ContentControl 
        Dim strFolder As String, strFile As String 
        Dim WkSht As Worksheet, i As Long, j As Long 
        strFolder = GetFolder 
        If strFolder = "" Then Exit Sub 
        Set WkSht = ActiveSheet 
        i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row 
        strFile = Dir(strFolder & "\*.docx", vbNormal) 
        While strFile <> "" 
            i = i + 1 
            Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False) 
            With wdDoc 
                j = 0 
                For Each CCtrl In .ContentControls 
                    j = j + 1 
                    WkSht.Cells(i, j) = CCtrl.Range.Text 
                Next 
            End With 
            wdDoc.Close SaveChanges:=False 
            strFile = Dir() 
        Wend 
        wdApp.Quit 
        Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing 
        Application.ScreenUpdating = True 
    End Sub 
     
    Function GetFolder() As String 
        Dim oFolder As Object 
        GetFolder = "" 
        Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0) 
        If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path 
        Set oFolder = Nothing 
    End Function 
    
    
    Formatting tags added by mark007
    Hi,

    i see that this code go run through each of the content control in the form and output them one by one into the excel. Is there any way such that i can select a few of the content controls in the form and output them into specific columns in the excel?
    Thanks for the help!

  3. #23
    Quote Originally Posted by macropod View Post
    Sub GetFormData() 
         'Note: this code requires a reference to the Word object model
        Application.ScreenUpdating = False 
        Dim wdApp As New Word.Application 
        Dim wdDoc As Word.Document 
        Dim CCtrl As Word.ContentControl 
        Dim strFolder As String, strFile As String 
        Dim WkSht As Worksheet, i As Long, j As Long 
        strFolder = GetFolder 
        If strFolder = "" Then Exit Sub 
        Set WkSht = ActiveSheet 
        i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row 
        strFile = Dir(strFolder & "\*.docx", vbNormal) 
        While strFile <> "" 
            i = i + 1 
            Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False) 
            With wdDoc 
                j = 0 
                For Each CCtrl In .ContentControls 
                    j = j + 1 
                    WkSht.Cells(i, j) = CCtrl.Range.Text 
                Next 
            End With 
            wdDoc.Close SaveChanges:=False 
            strFile = Dir() 
        Wend 
        wdApp.Quit 
        Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing 
        Application.ScreenUpdating = True 
    End Sub 
     
    Function GetFolder() As String 
        Dim oFolder As Object 
        GetFolder = "" 
        Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0) 
        If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path 
        Set oFolder = Nothing 
    End Function 
    
    
    Formatting tags added by mark007
    This looks like a really useful macro for me. However, I have used activeX controls (Check Boxes, Option Buttons, and Text Boxes) in my word form instead of content controls or form fields. Does anyone know how to change the macro to be able to work with these activeX controls?

  4. #24

    thanks

    Your programme helps a lots! thanks

  5. #25

    Is there a way of doing this with subfolders?

    Quote Originally Posted by macropod View Post
    Hi candameron,

    The following Excel macro will pull the data from all content controls in all Word files in a specified folder into the active worksheet. To run it, simply press Alt-F8 and choose the 'GetFormData' macro. You'll be asked to navigate to the source folder. Once you've done that, the macro will populate the worksheet. If you want, you could create a keyboard shortcut or a button to trigger the macro.

    If you initially put just one document containing meaningful data in all of the content controls into the 'source' folder, the macro will go through through the content controls and output the data into the worksheet. you should then be able to marry-up the output with whatever column headings you'll want in the workbook.
    Sub GetFormData() 
         'Note: this code requires a reference to the Word object model
        Application.ScreenUpdating = False 
        Dim wdApp As New Word.Application 
        Dim wdDoc As Word.Document 
        Dim CCtrl As Word.ContentControl 
        Dim strFolder As String, strFile As String 
        Dim WkSht As Worksheet, i As Long, j As Long 
        strFolder = GetFolder 
        If strFolder = "" Then Exit Sub 
        Set WkSht = ActiveSheet 
        i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row 
        strFile = Dir(strFolder & "\*.docx", vbNormal) 
        While strFile <> "" 
            i = i + 1 
            Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False) 
            With wdDoc 
                j = 0 
                For Each CCtrl In .ContentControls 
                    j = j + 1 
                    WkSht.Cells(i, j) = CCtrl.Range.Text 
                Next 
            End With 
            wdDoc.Close SaveChanges:=False 
            strFile = Dir() 
        Wend 
        wdApp.Quit 
        Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing 
        Application.ScreenUpdating = True 
    End Sub 
     
    Function GetFolder() As String 
        Dim oFolder As Object 
        GetFolder = "" 
        Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0) 
        If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path 
        Set oFolder = Nothing 
    End Function 
    
    
    Formatting tags added by mark007

    Hi,

    Sorry for coming back to an old thread, but I was wondering if there is a way this can be altered so you can select a higher folder and it looks in all subfolders? Thanks

  6. #26
    Thanks for the discussion

  7. #27
    HI,
    can you write me how I can modify this code to import checkbox (word content control) from word to excel.
    I am a beginner person in vba and I can’t find a solution to my problem.

  8. #28
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,083
    Location
    So what do you want the output to be for checkboxes?
    Cheers
    Paul Edstein
    [MS MVP - Word]

  9. #29
    Hey, so i got everything working fine, was wandering how i let it pull from a set folder location instead of having to navigating to it each time.

    Thanks!

  10. #30
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,083
    Location
    You could replace:
    strFolder = GetFolder
    with:
    strFolder = "C:\MyPath\MyFolder"
    where 'C:\MyPath\MyFolder' is the drive, path & folder name.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  11. #31
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    5,962
    Location
    Paul,would you close this 5yo thread when you're done with this poster?

    Thanks, SamT
    Please take the time to read the Forum FAQ

  12. #32
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,083
    Location
    Thread closed. And further discussion on issues related to the discussion in this thread should be started in a new thread - referencing this one if appropriate.
    Cheers
    Paul Edstein
    [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
  •