mardi 24 avril 2012 13:14
My office uses Infopath to fill out forms. However, these forms often have 100's or 1'000's of data fields.
What I am trying to do is to export the form as a raw .XML file, then use a VBA macro to instantly (well, almost) update the source code as if the data were entered into the form manually via InfoPath.
Currently, I have exported .XML files, opened the file as a .txt and imported and edited the text using Excel VBA, and re-exported the new data to a .txt file, which I then rename as .XML.
For all intents and purposes, the new .XML file seems identical (aside from some of the updated form's fields) to the old .XML file.
However, when attempting to open this .XML using InfoPath, I receive the following error:
"InfoPath cannot open the following file: C:\blah blah blah.XML.
The file is not a valid XML file."
"The form contains XML that cannot be parsed:
Invalid xml declaration.
Line 1, Position 5
<?xml version="1.0" encoding="UTF-8" ?> "
"A text/xml declaration may occur only at the very beginning of input.
Line 1, Position 41"
It seems odd to me that I can open an unaltered .XML file, but after changing some of the fields inside, I get error'd out on the very first line. Thanks for your help!
Should it matter that my version has returns, while the original is a continuous string? Here is some of the opening code from the original and the updated files:
<?xml version="1.0" encoding="UTF-8"?><?mso-infoPathSolution PIVersion="22.214.171.124" href="LINKED SITE WAS HERE" name="urn:schemas-microsoft-com:office:infopath:ARMOR:-myXSD-2005-11-03T09-00-35" productVersion="12.0.0" solutionVersion="126.96.36.199"?><?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?><my:myFields
<?xml version="1.0" encoding="UTF-8" ?>
<?mso-infoPathSolution PIVersion="188.8.131.52" href="LINKED SITE WAS HERE" name="urn:schemas-microsoft-com:office:infopath:ARMOR:-myXSD-2005-11-03T09-00-35" productVersion="12.0.0" solutionVersion="184.108.40.206"?>
<?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?>
Toutes les réponses
jeudi 26 avril 2012 17:32
Hi Luther Blissett,
So the ultimate goal of this is to take an already filled in form (*.xml) and using a VBA macro, update the data contained within the XML nodes and save it as a new file?
Where do you get the data you are using to populate the new XMLs? Depending on how you have this data stored, you might want to try code and rules behind your InfoPath template to pull this data in, and allow InfoPath to save the XML.
If you open one of the preexisting XMLs and manually modify the data within a few nodes and then save it, do you have the same problem?
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
Microsoft Online Community Support
vendredi 27 avril 2012 03:15ModérateurHi Luther,
Thank you for your question.
I am trying to involve someone familiar with this topic to further look at this issue.
vendredi 27 avril 2012 14:34
We will probably need to see your VBA code to delve into this but one thing I noticed is the "solutionVersion" of the updated XML file is older than the "solutionVersion" in the original file. Are you modifying this as well or just the data?
You should be able to update your XML file using code. You can either post what you have or I can work up a small sample for you if you prefer.
Let us know how you would like to proceed.
Scott Heim - Microsoft Office InfoPath and SharePoint Designer Online Community Support
mardi 1 mai 2012 06:01
"So the ultimate goal of this is to take an already filled in form (*.xml) and using a VBA macro, update the data contained within the XML nodes and save it as a new file?"
Correct - though I first change the .XML to a .txt. Upon your prompting, I did some more detail-oriented trouble-shooting and found that I cannot open a file using InfoPath even after just having changed the extention to .txt and back to .XML without altering its contents or evening opening as a .txt file for that matter.
"Where do you get the data you are using to populate the new XMLs? Depending on how you have this data stored, you might want to try code and rules behind your InfoPath template to pull this data in, and allow InfoPath to save the XML."
The data is stored in another software system that I pull into an excel file.
"If you open one of the preexisting XMLs and manually modify the data within a few nodes and then save it, do you have the same problem?"
By changing just the .XML file's contents, there is no problem.
mardi 1 mai 2012 06:08
It seems that I have a few options:
1) Familiarize myself with modifying a .XML file using VBA as opposed to converting and de-converting with a .txt extension.
2) Modify the InfoPath template itself to source the data directly from the above-mentioned excel file.
The only issue with option 2) is that there are many departments which use this InfoPath template for their forms. As such, I would need a way to be fully non-disruptive in implementing this. Any thoughts/suggestions? I'm not sure how far I might have to reach within my company in order to be able to alter the template directly, but perhaps it is possible to derive a template from a template? Again, I'm brand new to InfoPath as of this year.
Thanks, and Kind Regards,
mardi 1 mai 2012 13:52
If you could get the data directly from the other software system, that may be the best option. However, chances are you won't be able to directly connect InfoPath to that system unless it is either Microsoft Access or SQL Server. Now, InfoPath can also make use of web services so if the other system is not one of the above but exposes web services that will allow you to retrieve the data that may be an option to investigate. The reason is this: many times when software exposes web services to allow accessing data, those web methods (functions within the web service) take "parameters" (think filter fields here) that will allow you to specify just the data you want to return. If this is the case, it may be possible to use one data connection to the data but pass different parameters so you can return different data based on who opens the form (i.e. different departments.) There are a number of possibilities here but you would need to know more about the back end software that houses the data before I could comment any further on possible options (if any) to use InfoPath to interact with that data.
If you would prefer to just continue in your method of using VBA to update the XML files, the following is a sample. Please *do* create the InfoPath Form Template as I have described below so you can see this process work before trying to implement it in your own scenario.
1) Create the InfoPath Form Template:
- Launch InfoPath (if you are using 2010, launch the InfoPath Designer)
- Select the Controls Task Pane (on the right or if 2010, choose the Home tab) and add 2 textboxes to the form (these will show up in the Data Source Task Pane as: field1 and field2 - for this test, do not change the names)
- Save the Template in a location of your choice (this will be an XSN file)
2) Create a test XML file
- Navigate to where you saved the XSN file and double-click that file - this will launch a new instance of that template (this is the XML file)
- Enter your first name in one box and your last name in the other
- Save this XML file, again in a location of your choice, as: VBATest.xml
3) VBA code to update an XML file
- Open your VBA code editor and add a new module
- Add a reference to: msxml6.dll (this is in the Windows\System32 directory)
- Add this code to your module and enter the path to your saved XML file as noted in the codeSub UpdateXMLFile()
Dim oDoc As MSXML2.DOMDocument
Dim oNode As MSXML2.IXMLDOMNode
Dim strNS As String
Dim strNewValue As String
Dim strXMLFile As String
strNewValue = "New Text!!"
strXMLFile = "C:\VBATest.xml" ' --> THIS HAS TO BE THE PATH TO YOUR SAVED XML FILE!!!
Set oDoc = New DOMDocument60
oDoc.async = False
strNS = "xmlns:my='" & oDoc.DocumentElement.NamespaceURI & "'"
oDoc.setProperty "SelectionNamespaces", strNS
Set oNode = oDoc.SelectSingleNode("/my:myFields/my:field2")
oNode.Text = strNewValue
MsgBox "Update complete!"
- Save and run the code - assuming there were no errors, you should get the "complete" message box. If so, double-click the "VBATest.xml" file and confirm the text in field2 was indeed updated.
I hope this helps!
Scott Heim - Microsoft Office InfoPath and SharePoint Designer Online Community Support
samedi 28 juillet 2012 15:27
Your error message explains it: "A text/xml declaration may occur only at the very beginning of input.
Line 1, Position 41".
Your new XML file contains a leading space. Trim the leading space before saving and it will work.