Consulting

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

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.[vba]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[/vba]
    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
    VBAX Newbie
    Joined
    Sep 2015
    Posts
    1
    Location
    Quote Originally Posted by macropod View Post
    [vba]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[/vba]
    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
    VBAX Newbie
    Joined
    Sep 2016
    Posts
    1
    Location

    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.[vba]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[/vba]

    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
    VBAX Newbie
    Joined
    Oct 2016
    Posts
    1
    Location
    Thanks for the discussion

  7. #27
    VBAX Newbie
    Joined
    Jun 2017
    Posts
    2
    Location
    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,300
    Location
    So what do you want the output to be for checkboxes?
    Cheers
    Paul Edstein
    [MS MVP - Word]

  9. #29
    VBAX Regular
    Joined
    Jun 2017
    Posts
    9
    Location
    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,300
    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
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,300
    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
  •