PDA

View Full Version : Excel Macro - to import Word form data (xml)



rainbowtuner
12-11-2012, 11:46 AM
Dear m8s,

Grettings from the snowy hills in Innsbruck - Austria!

Glad to found this forum - with a lot of help in it. :help

Always found help here when I searched for something specific for my work - others, too, had the same problems or I found a much easier way to do it.

Actually I am working on an Excel macro - and I am going crazy... :banghead: I have created a Word.docx formular and I connected every Tagged Content Field with an .xml root knot. Sorry for my English - it's hard to finde the special expressions. However, instead of opening every .docx and save the form field as .txt I'd like to have this macro working - but i cannot find my mistake why its not running. :dunno

So maybe one of you guys is much smater than me - and could help me to get it running. I' ve already tried it in an German forum - although I do not like the Germans very much - but, it seems that none of them is working with .docx, formulars, tagged content, xml and analysing everything in excel. So, worse a try - first, i have found an article helping me in some kinds maybe it will help you or anybody else experiencing the same problems
and solving questions (although it is in german: BUT I CANNOT POST LINKS untill now).

And now the mysterious vba:

Public gstrheutigesDatum As String
Public gstrVorname As String
Public gstrNachname As String
Public gstrOrganisationseinheit As String
Public gstrMitarbeiter As String
Public gstrTitel As String
Public gstrZustand As String
Public gstrUmsetzung As String
Public gstrNutzen As String
Public gstrEuro As String
Public gstrStunden As String
Public gstrbetrifft As String
Public gstrdurch As String


Sub Test()

Set wrdWordApplication = CreateObject("Word.Application")
Set ofdDateiDialog = Application.FileDialog(msoFileDialogFilePicker)
With ofdDateiDialog
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
Next vrtSelectedItem
Else
End If
End With

For Each cxmlCustomXML In docDocument.CustomXMLParts
If cxmlCustomXML.BuiltIn = False Then
Set nxmlXMLNode = cxmlCustomXML.SelectSingleNode(cXMLKnotenRoot)
If Not nxmlXMLNode Is Nothing Then
Exit For
End If
End If

Next cxmlCustomXML

If Not cxmlCustomXML Is Nothing Then
pDatenAusXMLStrukturAuslesen
cxmlvCustomXML = cxmlCustomXML
pDatenInExcelEinfügen
End If

wrdWordApplication.Quit
Set wrdWordApplication = Nothing

This Workbook.Save

End Sub

Sub pDatenAusXMLStrukturAuslesen()
gstrheutigesDatum = cxmlvCustomXML.SelectSingleNode(cXMLKnotenRoot & "/heutigesDatum").Result
gstrVorname = cxmlvCustomXML.SelectSingleNode(cXMLKnotenRoot & "/Vorname").Result
gstrNachname = cxmlvCustomXML.SelectSingleNode(cXMLKnotenRoot & "/Nachname").Result
gstrOrganisationseinheit = cxmlvCustomXML.SelectSingleNode(cXMLKnotenRoot & "/Organisationseinheit").Result
gstrMitarbeiter = cxmlvCustomXML.SelectSingleNode(cXMLKnotenRoot & "/Mitarbeiter").Result
gstrTitel = cxmlvCustomXML.SelectSingleNode(cXMLKnotenRoot & "/Titel").Result
gstrZustand = cxmlvCustomXML.SelectSingleNode(cXMLKnotenRoot & "/Zustand").Result
gstrUmsetzung = cxmlvCustomXML.SelectSingleNode(cXMLKnotenRoot & "/Umsetzung").Result
gstrNutzen = cxmlvCustomXML.SelectSingleNode(cXMLKnotenRoot & "/Nutzen").Result
gstrEuro = cxmlvCustomXML.SelectSingleNode(cXMLKnotenRoot & "/Euro").Result
gstrStunden = cxmlvCustomXML.SelectSingleNode(cXMLKnotenRoot & "/Stunden").Result
gstrbetrifft = cxmlvCustomXML.SelectSingleNode(cXMLKnotenRoot & "/betrifft").Result
gstrdurch = cxmlvCustomXML.SelectSingleNode(cXMLKnotenRoot & "/durch").Result
End Sub

Sub pDatenInExcelEinfügen()
Sheets("Tabelle1").Select
Range("A2").Select
If Trim(ActiveCell.Value) <> "" Then
Range("A1").Select
Selection.End(xlDown).Select
Else
Range("A1").Select
End If
End Sub

So, m8s, I stronly hope that anybody can help me to get this little code running :bow: - i do not have any advisse how to go on without help and it is really important for me. Otherwise I will have a lot of work to do. And maybe there is a way to send u some beer from here :beerchug:. Let me know if u have any questions concerning this little work!

Have a nice evening. Hope to hear some help from u and i will post a link a little bit later - after 4 more posts.

Chears

Chris

Kenneth Hobs
12-11-2012, 12:10 PM
Maybe posting example files would be help us help you.

For form or content fields see:
'FormFields
' http://www.mrexcel.com/forum/showthread.php?p=1639696
' http://www.mrexcel.com/forum/showthread.php?t=333200
' http://www.excelforum.com/excel-programming/799070-import-text-fields-from-word.html
' Content Controls
' http://www.vbaexpress.com/forum/showthread.php?t=39654

rainbowtuner
12-11-2012, 12:47 PM
Thanks Kenneth for this fast as hell help - but: all examples you placed work do NOT work with the xml based content tagged fields. They just submit the result and furthermore on a document basis, whereas i am into a macro with the power of excerpting all results of all documents at once to excel document. However, as you asked for it here is the document; by the way it is a template containing macros for (1) save as... and (2) Send as Email via Outlook - here all people using Outlook on their pcs for work. So the file extension is .dotm which is a invalid file attachement and made me compressing it a s a .zip for upload. Moreover, do to the nature of formulars it is protected by a password (i usually use 08154711) for my formular docs.

So hope it helps you to help me! I am leaving for today, because here it is already 9 pm and i have to go home now from work! Hope to hear smthg soon!

godd night!

Kenneth Hobs
12-12-2012, 10:33 AM
Please tell me how to create the tagged xml fields. I have not done that and MSWord is no help. I don't see a place to enter a password.

rainbowtuner
12-13-2012, 10:49 AM
First, thx for your help. You are asked for the password if u remoove the protection from the formular in the tools registertab. If you have consistent problems with that let me know...:think:

Second, yes it is true, MSWord is no help with that but there is a a little, fine workaround to help out. I'll tell you how to manage that:

If there is the finished form, it continues with the creation of the XML data source in the Word file, and the connection of the content controls to the XML node. The DOCX, DOCM, dotx or dotm are basically ZIP files in the document data is stored in a specified file and folder structure. If you temporarily change, for example, a Word docx file the file extension DOCX in ZIP, you can open it by double-clicking with a zip program and take a look at the file. For the XML data source in the Word file, the folder customXml is provided to see the root of the zip file. Due to the need to manually create the folder, the associated reference files and the XML file itself use simply the free program Word 2007 Content Control Toolkit (which works not only with Word nor 2007), to find on google or dbeDOTcodeplexDOTcom (still not able to post a link - but i hope you'll find this little tool). Start the toolkit and open there on File / Open your Word document. Once the file is loaded, on the left shows the existing content controls. Now place by clicking on the Create a new custom XML part to an XML file. Now on the right side in the drop-down list namespace contains the entry (no namespace) (1) and on the underlying Bind View tab is a folder icon and the root entry visible. Now switch back to the Edit View tab. Here in the text box, the two XML tags and displays <root> </ root> - the XML file is accordingly empty. There then create for each content control their own node. Assign this now the same names as the tags. Each node starts a new line with the less-than symbol <followed by the node name and the two characters />, for example, or <TravellerFirstnameSurname /> <Destination />. After creating for all content controls a node, go to Bind View. In the XML structure, now every XML tag is displayed as a separate node. Finally, you need to connect the XML node with the content controls. To do this in the XML tree to the desired node. Then the knot again and drag it to the line with the desired content control.

You will see in the column XPath of the content controls collection the file path, such as / root [1] / traveler first name-last name [1] or / root [1] / -> Assigned to each content control is a node - save the changes on File / Save and exit the Content Control Toolkit. The Word document is now ready for use.

All entered into the content controls data Word saves the previously created links directly in the internal XML file in the appropriate node. This can be controlled any time by opening a completed formula in the Content Control Toolkit on the Edit View tab.:thumb

Finished TUTORIAL: How to teach MSWord xml

Easy as that! isn't it?

Kenneth Hobs
12-13-2012, 03:33 PM
Looks a little complicated for me. I have not been a big fan of xml.

Does content control not suffice?

Option Explicit

' http://www.vbaexpress.com/forum/showthread.php?t=39654
Sub AddContentControlValues()
' Add Tools > References: Microsoft Word and Microsoft Scripting Runtime
Dim vField As ContentControl
Dim fso As Scripting.FileSystemObject
Dim fsDir As Scripting.Folder
Dim fsFile As Scripting.File
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim vColumn As Integer
Dim vLastRow As Integer
Dim i As Integer
Dim vValue As Variant
Dim vFileName As String
Dim cell As Excel.Range
Dim inPath As String, outPath As String


inPath = ThisWorkbook.Path

vLastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
vColumn = 1

Set fso = New Scripting.FileSystemObject
Set fsDir = fso.GetFolder(inPath)

Set wdApp = New Word.Application
wdApp.Visible = False 'True

For Each fsFile In fsDir.Files
If Split(fsFile.Type)(1) <> "Word" Then GoTo tend
wdApp.Documents.Open (fsFile)
Set myDoc = wdApp.ActiveDocument
Debug.Print wdApp.Documents(myDoc).ContentControls.Count
For Each vField In wdApp.Documents(myDoc).ContentControls
vValue = "Text: " & vField.Range.Text & vbLf & _
"Tag: " & vField.Tag & vbLf & _
"Type: " & vField.Type
Set cell = Cells(vLastRow, vColumn)
cell.Value = vValue
vColumn = vColumn + 1
Next vField

vColumn = 1
vLastRow = vLastRow + 1
vFileName = wdApp.ActiveDocument.Name
wdApp.ActiveDocument.Close
tend:
Next fsFile

wdApp.Quit

ActiveSheet.UsedRange.Columns.ColumnWidth = 45
End Sub

rainbowtuner
12-14-2012, 01:16 AM
But it does not work for me here -:dunno - although i have added the right tools and saved the Excel file into the right(same) directory. All i get are empty collums and the row A with 45 width. However, if you could get this code working I'll of course prefer this one - if it has the same funtionability as mine (actually none 'cause not working properly). I have tried it out with some testfiles. I've attached them beneth. Maybe you can find my error!

I suspect it has to do with the proctection of the forms could that be right?
Then this will maybe help to get the values!

'Unprotect the file
If ActiveDocument.ProtectionType <> wdNoProtection Then
ActiveDocument.Unprotect Password:="08154711"
End If

'Reprotect the document.
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="08154711"
End If

snb
12-14-2012, 05:33 AM
Meine Vorschlag:
No password necessary.


Sub M_snb()
c00 = "G:\OF\"
c01 = Dir(c00 & "*.docx")

Do Until c01 = ""
With GetObject(c00 & c01)
For Each ct In .Contentcontrols
c02 = c02 & ";Text: " & ct.Range.Text & " Tag: " & ct.Tag & " Title: " & ct.title
Next
.Close 0
End With
c02 = c02 & vbCr

c01 = Dir
Loop

sn = Filter(Split(c02, vbCr), ";")
For j = 0 To UBound(sn)
sp = Split(sn(j), ";")
thisworkbook.sheets(1).Cells(j + 1, 1).Resize(, UBound(sp)+1) = sp
Next
End Sub

Kenneth Hobs
12-14-2012, 06:10 AM
Seemed to work ok for me. I suggest running it from a folder where only it and the DOCM file that you posted is located.

Tommy
12-14-2012, 03:10 PM
rainbowtuner,

look for a download for notepad xml, (http://www.microsoft.com/en-us/download/details.aspx?id=7973) this will enable you to look at and edit an xml file with little effort. There are several out there pick the one you feel the best about. :)

I would suggest that since you are looking for xml programming that you look into OpenXml SDK 2.0. This is a set of programming interfaces to allow you to edit you docs with your program, there are several tutorials on it.

The major drawback is that you can't do anything with formfields. Whoever wrote the interface didn't include them. Another one is you have to install it on all of the PC's that will be using this "macro".

Another issue you will have is checking or unchecking checkboxes, if you do not have these be very thankful! I have code for this but it is not pretty.

rainbowtuner
12-21-2012, 07:02 AM
m8s thx for your help so far.

I have tried all your findings but with no success!

But first, sorry for the late response of mine, i had been skiing, winter vacation, holidays and had a lot to do besides.

Second, there seems to be a misunderstanding. The sense of the .dotm (template with macro) is to send this file via Email (outlook -> thats why it contains a macro for replying) and recieve the formulars - filled out, e.g. with data in the content field - as a .docx file.

Third, recieving serveral of that, now filled with data, formulas (.docx's) as replies in Outlook and saving them all in one folder makes much more sense, when consoldidating the data of ALL formulars into one Excel file afterwards; for a better overview or whatever.
Means: in each line the contente of one formular together, with field by field the content of each seperate content field of the one formular and line by line one formular each.

To cut a long story short: the macro of the excel file should read the contents of each formular of that folder, containing all recieved formulars, transfer it to the workbook/spreadsheet, one formular each line, in each field per line the input from each content field; choosing always the last line to fill and run through the whole folder extracting the content of every formular based on: LINE = one formular(.docx) & each ROW in this line =the content field content.

And, m8s i am sorry to say, but in that need none of your sugestions helped me so far with that and i do not know how to come even close to that!