#1
|
|||
|
|||
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. |
#2
|
||||
|
||||
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 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] |
#3
|
|||
|
|||
Thank you so much. That worked perfectly!!! What a great Macro!!!!
|
#4
|
|||
|
|||
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 |
#5
|
||||
|
||||
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] |
#6
|
|||
|
|||
Thanks so much. You are a genius!!!
|
#7
|
|||
|
|||
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?
|
#8
|
||||
|
||||
The modified code runs fine for me. Are you actually selecting the folder to process & clicking OK?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
Do not pull duplicates if already in file?
Any way to re-write as to not pull in duplicate word docs?
|
#11
|
||||
|
||||
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] |
#12
|
|||
|
|||
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?
|
#13
|
||||
|
||||
That really depends on which entry you want to keep.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#14
|
|||
|
|||
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? |
#15
|
||||
|
||||
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] |
|
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 |
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 |
Link data from embedded excel sheet | rwbarrett | Word | 1 | 05-27-2011 02:05 AM |
Compare data in different columns in an Excel sheet | kgfendi | Excel | 5 | 05-16-2009 05:42 PM |