Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-24-2014, 10:41 PM
statue.919 statue.919 is offline How to transferring Word data to an Excel sheet Windows 7 64bit How to transferring Word data to an Excel sheet Office 2013
Novice
How to transferring Word data to an Excel sheet
 
Join Date: Feb 2014
Posts: 5
statue.919 is on a distinguished road
Default How to transferring Word data to an Excel sheet

Hi,
I needed to know how to transferring Word data to an Excel sheet.


I have added the sample word document.
What i want to do is when someone enters data in this form i need to transfer that data into an excel sheet.
And can you guys tell me how to do it by adding a macro.
And i want the data to be sent when they hit a submit button.
Thank you. Pls ask if have any more questions.
Attached Files
File Type: docx Application Form.docx (19.3 KB, 39 views)
Reply With Quote
  #2  
Old 02-25-2014, 01:34 AM
macropod's Avatar
macropod macropod is online now How to transferring Word data to an Excel sheet Windows 7 32bit How to transferring Word data to an Excel sheet Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

If you add content controls or formfields to your document so that the users can input the data into them, you can use Word's 'save data for forms' option to extract the data for import into Excel. Alternatively, you could use an Excel macro like the following to read the data from the Word documents; this is better than trying to push the data to Excel from each Word document. Simply run the macro, which has a folder browser you use to select the folder to process, and it will add the form data from all documents in the selected folder to the active worksheet.
Code:
Sub GetFormData()
'Note: this code requires a reference to the Word object model.
'See under the VBE's Tools|References.
Application.ScreenUpdating = False
Dim strFolder As String, strFile As String
Dim WkSht As Worksheet, r As Long, c As Long
strFolder = GetFolder:If strFolder = "" Then Exit Sub
Dim wdApp As New Word.Application, wdDoc As Word.Document
Dim FmFld As Word.FormField, CCtrl As Word.ContentControl
Set WkSht = ActiveSheet
r = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
'Disable any auto macros in the documents being processed
wdApp.WordBasic.DisableAutoMacros
strFile = Dir(strFolder & "\*.doc", vbNormal)
While strFile <> ""
  r = r + 1
  Set wdDoc = wdApp.Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
  With wdDoc
    c = 0
    For Each FmFld In .FormFields
      c = c + 1
      With FmFld
        Select Case .Type
          Case Is = wdFieldFormCheckBox
            WkSht.Cells(r, c) = .CheckBox.Value
          Case Else
            If IsNumeric(FmFld.Result) Then
              If Len(FmFld.Result) > 15 Then
                WkSht.Cells(r, c) = "'" & FmFld.Result
              Else
                WkSht.Cells(r, c) = FmFld.Result
              End If
            Else
              WkSht.Cells(r, c) = FmFld.Result
            End If
        End Select
      End With
    Next
    For Each CCtrl In .ContentControls
      With CCtrl
        Select Case .Type
          Case Is = wdContentControlCheckBox
            c = c + 1
            WkSht.Cells(i, j) = .Checked
          Case wdContentControlDate, wdContentControlDropdownList, wdContentControlRichText, wdContentControlText
            c = c + 1
            If IsNumeric(.Range.Text) Then
              If Len(.Range.Text) > 15 Then
                WkSht.Cells(r, c).Value = "'" & .Range.Text
              Else
                WkSht.Cells(r, c).Value = .Range.Text
              End If
            Else
              WkSht.Cells(r, c) = .Range.Text
            End If
          Case Else
        End Select
      End With
    Next
    .Close SaveChanges:=False
  End With
  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
If you want to record the document's name as part of the data, change:
c = 0
to:
c = 1: WkSht.Cells(r, c) = strFile

For PC macro installation & usage instructions, see: Installing Macros
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 04-14-2016, 08:43 AM
spike56 spike56 is offline How to transferring Word data to an Excel sheet Windows 10 How to transferring Word data to an Excel sheet Office 2010 32bit
Novice
 
Join Date: Apr 2016
Posts: 5
spike56 is on a distinguished road
Default

Thank you so much. That worked perfectly!!! What a great Macro!!!!
Reply With Quote
  #4  
Old 04-15-2016, 07:03 AM
spike56 spike56 is offline How to transferring Word data to an Excel sheet Windows 10 How to transferring Word data to an Excel sheet Office 2010 32bit
Novice
 
Join Date: Apr 2016
Posts: 5
spike56 is on a distinguished road
Default Just Wondering

Macropod,

Just wondering if there is a way to tell the macro to ignore the first 5 fields on the page and then start a new row after each 12 fields. I think the imported data would have more meaning if I could get it into columns instead of one long row. Especially if I have 17 rooms registered on the form.

thanks
Reply With Quote
  #5  
Old 04-15-2016, 05:08 PM
macropod's Avatar
macropod macropod is online now How to transferring Word data to an Excel sheet Windows 7 64bit How to transferring Word data to an Excel sheet Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

You'd need a quite different approach for that. And, given that your document has formfields in different columns on each row, the output needs to reflect that. Try:
Code:
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 FmFld As Word.FormField
Dim strFolder As String, strFile As String
Dim WkSht As Worksheet, i As Long, r As Long, c As Long
strFolder = GetFolder
If strFolder = "" Then Exit Sub
Set WkSht = ActiveSheet
strFile = Dir(strFolder & "\*.doc", vbNormal)
While strFile <> ""
  i = WkSht.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row
  Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
  With wdDoc
    With .Tables(1)
      For r = 2 To .Rows.Count
        i = i + 1
        For c = 2 To .Columns.Count
          With .Cell(r, c).Range
            If .FormFields.Count = 1 Then
              With .FormFields(1)
                If IsNumeric(.Result) Then
                  If Len(.Result) > 15 Then
                    WkSht.Cells(i, c - 1) = "'" & .Result
                  Else
                    WkSht.Cells(i, c - 1) = .Result
                  End If
                Else
                  WkSht.Cells(i, c - 1) = .Result
                End If
              End With
            End If
          End With
        Next
      Next
    End With
    .Close SaveChanges:=False
  End With
  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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #6  
Old 04-15-2016, 06:38 PM
spike56 spike56 is offline How to transferring Word data to an Excel sheet Windows 10 How to transferring Word data to an Excel sheet Office 2010 32bit
Novice
 
Join Date: Apr 2016
Posts: 5
spike56 is on a distinguished road
Default

Thanks so much. You are a genius!!!
Reply With Quote
  #7  
Old 04-16-2016, 04:32 PM
spike56 spike56 is offline How to transferring Word data to an Excel sheet Windows 10 How to transferring Word data to an Excel sheet Office 2010 32bit
Novice
 
Join Date: Apr 2016
Posts: 5
spike56 is on a distinguished road
Default Help!!!

Now I don't get anything from the forms whether I run it with the updated macro or the old macro. I even generated new forms because I thought there maybe was something that was detecting that the forms ran through the macro already. i even put them into a new folder and copied the Excel file so it would start out new. What am I doing wrong?
Attached Files
File Type: doc HAR-094-ICCHousingFormBigees.doc (392.5 KB, 32 views)
File Type: doc HAR-094-ICCHousingFormGalsOfWI.doc (393.0 KB, 35 views)
File Type: xlsm WordFormFieldsNew.xlsm (18.7 KB, 45 views)
Reply With Quote
  #8  
Old 04-16-2016, 08:17 PM
macropod's Avatar
macropod macropod is online now How to transferring Word data to an Excel sheet Windows 7 64bit How to transferring Word data to an Excel sheet Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

The modified code runs fine for me. Are you actually selecting the folder to process & clicking OK?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 04-18-2016, 06:52 PM
spike56 spike56 is offline How to transferring Word data to an Excel sheet Windows 10 How to transferring Word data to an Excel sheet Office 2010 32bit
Novice
 
Join Date: Apr 2016
Posts: 5
spike56 is on a distinguished road
Thumbs up I figured it out.

Paul,

OMG I am such an idiot sometimes. It never occurred to me to look further down in the file. It is working perfectly and once again, I am so grateful for your expertise. What a great Macro.

Chris Hembel
Reply With Quote
  #10  
Old 06-04-2020, 09:36 AM
ldiggity1018 ldiggity1018 is offline How to transferring Word data to an Excel sheet Windows 10 How to transferring Word data to an Excel sheet Office 2013
Novice
 
Join Date: Jun 2020
Posts: 3
ldiggity1018 is on a distinguished road
Default Do not pull duplicates if already in file?

Any way to re-write as to not pull in duplicate word docs?
Reply With Quote
  #11  
Old 06-04-2020, 02:25 PM
macropod's Avatar
macropod macropod is online now How to transferring Word data to an Excel sheet Windows 7 64bit How to transferring Word data to an Excel sheet Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

That would require modifying the code to record the details of the filenames that had been processed so that those filenames could be compared against the found files. This could also add significantly to the processing time. The simple workaround is to keep unprocessed files in a separate folder from the processed one - or erase the previously-collected data each time.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #12  
Old 06-16-2020, 12:49 PM
ldiggity1018 ldiggity1018 is offline How to transferring Word data to an Excel sheet Windows 10 How to transferring Word data to an Excel sheet Office 2013
Novice
 
Join Date: Jun 2020
Posts: 3
ldiggity1018 is on a distinguished road
Default

So I re-wrote the code to include(in column 1) the document title, as reference to remove. What would the code look like to remove duplicate entries?
Reply With Quote
  #13  
Old 06-16-2020, 03:00 PM
macropod's Avatar
macropod macropod is online now How to transferring Word data to an Excel sheet Windows 7 64bit How to transferring Word data to an Excel sheet Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

That really depends on which entry you want to keep.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #14  
Old 06-18-2020, 08:25 AM
ldiggity1018 ldiggity1018 is offline How to transferring Word data to an Excel sheet Windows 10 How to transferring Word data to an Excel sheet Office 2013
Novice
 
Join Date: Jun 2020
Posts: 3
ldiggity1018 is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
That really depends on which entry you want to keep.

Would you have a perspective on how to map the form inputs to specific columns. Example if I wanted to make an adjustment to the word document and add a content control in the middle of 2 others, I would need to subsequently add a column in the excel sheet. Can you tell it where to input the information when extracted, that way you wouldn't have to update previous versions everytime you make an adjustment to the form?
Reply With Quote
  #15  
Old 06-18-2020, 03:43 PM
macropod's Avatar
macropod macropod is online now How to transferring Word data to an Excel sheet Windows 7 64bit How to transferring Word data to an Excel sheet Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

The way the macro works, you would need to add the new column to the workbook between the existing two columns for the content controls that are already being captured. And you'd have to do that before running the macro on those documents and after you have finished running it on all documents lacking the new content control.

That still doesn't address the issue of which 'duplicates' you want to keep/discard.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Copy data from Outlook mail and Paste it in a Excel sheet? padhu1989 Outlook 0 09-11-2012 04:07 AM
How to transferring Word data to an Excel sheet data linked from excel sheet megatronixs Word VBA 1 08-19-2012 11:09 PM
Print word form using excel data sheet LS1015 Office 1 07-16-2012 08:16 PM
How to transferring Word data to an Excel sheet Link data from embedded excel sheet rwbarrett Word 1 05-27-2011 02:05 AM
How to transferring Word data to an Excel sheet Compare data in different columns in an Excel sheet kgfendi Excel 5 05-16-2009 05:42 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:35 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft