Link to home
Create AccountLog in
Avatar of sierra810
sierra810Flag for United States of America

asked on

3 Word templates need to Export data to 3 Tabs in Excel

I have 3 word template forms that users send in to make requests (I receive multiples requests of each type daily). Everyday I must open each request and just copy/paste all of the responses into the excel sheet on the corresponding tab (3 tabs in the excel file one for each type of request). I add the date at the end of the row in excel so we know when the request came in.

Is there a easy way to create an excel macro to

open each word file found in the directory
The type of request is in the upper left of each request form
go to that tab in my excel template file and copy in each of the answers from the form into the excel line and add the date in the last field?
Move the word files as they are processed to folder called "processed-already"

I need to run this everyday sometimes multiple times and append each new line of data starting at the last row in the existing tab.

Any help you guys can give on this one is greatly appreciated. I have done some minor macros but never to this level of taking data out of word and copying it into an excel file.

Samples of the 3 different request forms and the single excel file that I copy the data into everyday is attached for your reference.
My----Asset-Request-Form.docx
MY---Script---Synopsis-Request-Form.docx
My--Studios-Content-Request-Form.docx
RequestTracker.xlsx
Avatar of Helen Feddema
Helen Feddema
Flag of United States of America image

Since the documents in question are Form Field docs (they are not templates -- those have the extension .dotx or .dotm), you may be able to modify the following code for your use (it is Access VBA that imports values from form field docs).

Private Sub cmdImportFromWord_Click()
'Created by Helen Feddema 25-Sep-2014
'Last modified by Helen Feddema 25-Sep-2014

On Error GoTo ErrorHandler

   Dim fd As Office.FileDialog
   Dim varSelectedItem As Variant
   Dim strFileNameAndPath As String
   Dim cctl As Word.ContentControl
   Dim strControlName As String
   Dim varControlValue As Variant
   
   'Select a filled-in Contact Response letter to process
   Set fd = Application.FileDialog(msoFileDialogFilePicker)
   
   With fd
      .AllowMultiSelect = False
      .Title = "Browse for Contact Response Letter"
      .ButtonName = "Select"
      .Filters.Clear
      .Filters.Add "Documents", "*.docx", 1
      .InitialView = msoFileDialogViewDetails
      If .Show = -1 Then
         For Each varSelectedItem In .SelectedItems
            strFileNameAndPath = CStr(varSelectedItem)
         Next varSelectedItem
      Else
         Debug.Print "User pressed Cancel"
         strFileNameAndPath = ""
      End If
   End With
   
   If InStr(strFileNameAndPath, "Contact Response Letter") = 0 Then
      strTitle = "Wrong letter"
      strPrompt = "Please select a Contact Response letter to process"
      MsgBox prompt:=strPrompt, _
         buttons:=vbExclamation + vbOKOnly, _
         Title:=strTitle
      GoTo ErrorHandlerExit
   End If
   
   Set appWord = GetObject(, "Word.Application")
   Set doc = appWord.Documents.Open(strFileNameAndPath)
   Set rst = CurrentDb.OpenRecordset("tblContactResponses")
   rst.AddNew
   rst![DocumentName] = strFileNameAndPath
   
   For Each cctl In doc.ContentControls
      Debug.Print "Control name: " & cctl.Tag _
         & "; value: " & cctl.Range
      strControlName = cctl.Tag
      varControlValue = cctl.Range
      rst.Fields(strControlName) = varControlValue
   Next cctl
   
   rst.Update
   rst.Close
   doc.Close savechanges:=wdDoNotSaveChanges
   
   DoCmd.OpenTable "tblContactResponses"
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   If Err = 429 Then
      'Word is not running; open Word with CreateObject
      Set appWord = CreateObject("Word.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number _
         & " in " & Me.ActiveControl.Name & " procedure; " _
         & "Description: " & Err.Description
      Resume ErrorHandlerExit
   End If
   
End Sub

Open in new window


(the code is from the sample database from my Working with Word ebook.)

Then, in order to get the data into Excel, you would need some code to match up the tabs with document names, and put the values into the appropriate cells in each sheet.  If I have time, I will see if I can work up something, but unfortunately your workbook wouldn't download.
Avatar of sierra810

ASKER

sorry, I tried and cannot get it to work. giving errors about the Me.ActiveControl.Name and I took that part out and it then runs but give me other errors like Error 424  :(

Attaching my tracker file again, perhaps if you have time you can take another peak at it.
thank you!
RequestTracker.xls
I got the workbook this time, and I am working on a procedure.  Some things to note:

The document names should match the sheet names exactly (except for the extension); I have modified them as needed.  You could modify either the doc names or the sheet names, just so they match.

In the documents, most of the data is not in form fields, but is just text in cells of a table.  It would be a lot easier if all data to export is in form fields (or content controls).  Is that possible?  Another possibility is to have all data in table cells, but in that case the tables should be strictly formatted so as to ensure that the right data is picked up (no split cells, for example).  Or thirdly, the data to export could be bookmarked.  Which of these alternatives would work best for your needs?
Hi Helen, unfortunately the 3 forms predate my working on them and have been in use so long that they cannot be altered at this time. They are to be in the format they are currently in :(  so everyday I copy/paste from them into the excel tabs of the workbook.
That could be a real problem.  I will do something that will give you a start, and you can do the fine-tuning.  FYI, I think the best method would be to go with all form fields (or all content controls).
ASKER CERTIFIED SOLUTION
Avatar of Helen Feddema
Helen Feddema
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you Helen, I will take it from here!
Thank you for all your help!