Pulling data from word control drop boxes into specific excel sheets

[jenhudson]

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.

[Artik]

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

[jenhudson]

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

[Artik]

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

[gmayor]

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.

[Artik]

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