Consulting

Results 1 to 6 of 6

Thread: Pulling data from word control drop boxes into specific excel sheets

  1. #1

    Pulling data from word control drop boxes into specific excel sheets

    Hi all,

    I hoping you can help me ... I'm a primary school teacher who wants to automate my planning as much as possible.

    I've found out how to do drop box selections and filled them with all the standards I need in each area, e.g. oracy, reading, writing etc. I've even found code on here (Post#4 of showthread.php?40406-Extracting-Word-form-Data-and-exporting-to-Excel-spreadsheet, to pull all of the choices made into excel and got it to work. However, they all come over in a big stream from column AA to ES so it doesn't do exactly what I need.

    What I'm attempting at the moment is each week of planning is saved in the file then I go into excel and run the code that pulls over the drop box selections and puts them in separate sheets for each standard type e.g. all the 'Oracy' choices together on one sheet, all the 'DCF' choices together on another sheet. In my planning document I choose from the drop boxes each day and in the Excel 'log' I'd need the date we covered it.

    However, the dream scenario would be the other way around where by I have the list of statements for each aspect (oracy, reading, writing etc) on different sheets in excel or word and at a click it cross references with the daily dates pulling these from the control boxes (e.g. 'MonDate' etc) and creating the log. In my head Column A would have the Date (e.g. 'MonDate' Control box, 'TuesDate' Control Box etc) and Column B would have the selected text, this would be the same for each sheet. This would be super! as it would be easier to read.

    The overall idea being that at the end of the year when I need to justify that pupil A has achieved standard "R.Oracy.5" I can go to the log and filter/search for "R.Oracy.5" and see a list of dates when we covered that standard and I can look in their books and easily find the evidence.

    To be honest the more I think about this and struggle trying different things the more of a muddle I'd getting myself into - I have no coding background but know that there must be a way to do this.

    Please find attached the planning document so you can see the controls I need to combine/log and pull over

    Please, please any help you can give me would be hugely appreciated.
    Attached Files Attached Files

  2. #2
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    You've probably lost hope.
    And here in the meantime...


    First, prepare the abc2.docx file properly. Each control, starting with MonDate, should be identifiable. I use the Tag property for this purpose. The structure of the tag looks like this: ControlTitle_WeekDay_SessionNo, e.g. LiteracyOracy_Mon_1, DCFDropDown_Wed_3, Donaldson_Tue_2, etc.
    By selecting the [Change Tags] button, the macro will do it for you, adding the correct Tag to all needed controls and some will complete the Title (Not all Donaldson controls have titles!). Because the CSCWelshContinuum control "does not match" to previous ones, the code was programmed in a different way.When it is found, please specify the day of the week yourself and Session No. If there is a need to manually change the Tag, select the control and select the Developer / Controls / Properties from the ribbon and enter the appropriate description in the "Tag" field.


    To import data from * .docx files, you also need to prepare an Excel workbook. It contains sheets with the same names as the Control Titles. All you need to do is click [Download log data], point to the WinWord file and the rest will do it yourself. You can add additional files to the list, which will be saved under the last entry in each sheet. You do not need to download data chronologically. After importing enough to sort all the tables in relation to the date.


    The attached abc2 (mod).docx file is the result of the macro "Change Tags".


    Have fun.


    Artik
    Attached Files Attached Files

  3. #3
    This is amazing - exactly what I needed...but I can't get it to work. The 'Change Tags' Macro works great (I think) but I'm being directed to an error in the macro after I've selected the file as prompted by the 'download log data' button.

    Thank you so so much for your help - this really does mean so much.

    See code below: (I've put in bold and red the sections that are being highlighted in the code)




    Sub AAA()


    Dim wdApp As Object 'New Word.Application
    Dim boolIsWdRun As Boolean
    Dim wdDoc As Object 'Word.Document
    Dim CCtrl As Object 'Word.ContentControl
    Dim varDocFile As Variant
    Dim WkSht As Worksheet
    Dim i As Long
    Dim dDate As Date
    Dim varDaysWeek As Variant
    Dim strDay As String
    Dim varTag As Variant
    Dim strSubject As String
    Dim lSession As Long
    Dim strDescript As String
    Dim lLstRow As Long

    Const wdContentControlCheckBox As Long = 8
    Const wdContentControlDate As Long = 6
    Const wdContentControlDropdownList As Long = 4
    Const wdContentControlRichText As Long = 0
    Const wdContentControlText As Long = 1




    varDocFile = Application.GetOpenFilename("WinWord Files (*.doc*), *.doc*")
    If TypeName(varDocFile) = "Boolean" Then Exit Sub


    Application.ScreenUpdating = False


    On Error Resume Next
    boolIsWdRun = True
    Set wdApp = GetObject(, "Word.Application")


    If Err.Number <> 0 Then
    Err.Clear


    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = True
    boolIsWdRun = False
    End If
    On Error GoTo 0


    Set wdDoc = wdApp.Documents.Open(Filename:=varDocFile, AddToRecentFiles:=False, Visible:=False)




    varDaysWeek = Array("Mon", "Tue", "Wed", "Thu", "Fri")
    i = 1


    With wdDoc
    For i = 0 To UBound(varDaysWeek)

    For Each CCtrl In .ContentControls
    If CCtrl.Tag = "Date_" & varDaysWeek(i) Then
    Exit For
    End If
    Next CCtrl


    If CCtrl Is Nothing Then
    Stop
    End If


    strDay = varDaysWeek(i)
    dDate = CDate(Split(CCtrl.Range.Text, ",")(1))




    For Each CCtrl In .ContentControls
    With CCtrl
    If Len(.Title) > 0 Then
    If Not .Title Like "Date_*" Then
    Select Case .Type
    Case Is = wdContentControlCheckBox
    Stop
    Case wdContentControlDropdownList, wdContentControlRichText, wdContentControlText 'wdContentControlDate,
    varTag = Empty
    varTag = Split(.Tag, "_")


    If UBound(varTag) = 2 Then
    If varTag(1) = strDay Then
    strSubject = varTag(0)
    lSession = CLng(varTag(2))


    strDescript = .Range.Text

    If LCase(strDescript) Like LCase("Choose an item*") Then
    strDescript = vbNullString
    End If


    On Error Resume Next
    Set WkSht = ThisWorkbook.Worksheets(strSubject)
    On Error GoTo 0


    If WkSht Is Nothing Then
    Stop
    End If

    lLstRow = WkSht.Cells(Rows.Count, "A").End(xlUp).Row + 1


    WkSht.Cells(lLstRow, "A").Value = dDate
    WkSht.Cells(lLstRow, "B").Value = lSession
    WkSht.Cells(lLstRow, "C").Value = strDescript
    End If
    End If
    Case Else
    End Select
    End If
    End If
    End With
    Next CCtrl


    Next i


    .Close SaveChanges:=False
    End With


    If Not boolIsWdRun Then
    wdApp.Quit
    End If


    Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing


    MsgBox "Done"
    End Sub

  4. #4
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Someone's gone crazy! I do not know if it's my computer, Excel or Word. In any case, the Change Tags procedure does not save the file! I do not know why.
    To take it a step further, change the end of the BBB procedure so that the file and the Word application are not closed. After the macro completes, save the file manually and close it.
    Change in the BBB procedure to
    (...)
            Next CCtrl
            
            '.Close True
        End With
    
    
            'If Not boolIsWdRun Then
            '    wdApp.Quit
            'End If
            
            Set wdDoc = Nothing
            Set wdApp = Nothing
    
    
            MsgBox "Done"
    End Sub
    After changing the Tags, you can run [Download log data].


    The attachment contains the correct file after changing the Tags.

    It can not be ruled out that you will receive an error on line:
    dDate = CDate(Split(CCtrl.Range.Text, ",")(1))
    This may be due to the fact that Excel can not convert text in Date_Mon, Date_Tue, Date_Wed controls etc. If you will to receive an error in this line, change the date format in all controls on the first one in the list (see picture, sorry in Polish )

    Artik
    Attached Images Attached Images
    Attached Files Attached Files

  5. #5
    It may be even more to do with the fact that there isn't a content control with the tag like "Date_*" in the original document. The Date controls there are tagged

    MonDate
    TuesDate
    WedDate
    ThursDate
    FriDate

    Note that this does not correspond with the array either.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  6. #6
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Quote Originally Posted by gmayor View Post
    It may be even more to do with the fact that there isn't a content control with the tag like "Date_*" in the original document.
    That is the task for macro BBB. My fault is that I have not checked the contents of the abc2 (mod).docx file before posting it on the forum. I trusted that after modification with the [Change Tags] button (macro BBB) the file was saved. But it did not happen. I fixed this error by adding the abc2_2 (mod).docx attachment.
    In general, I'm shocked. The BBB macro did not save the file after the Tags changes. He does not report any error. But to make it even more scary, I could not save the modified file manually to save changes! I had to save under a different name. My guess is that this may be due to the fact that the file was created from the abc.dotm template.

    Can you explain it?

    Artik

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •