locked
New to InfoPath - Re-opening an altered XML file in InfoPath RRS feed

  • Question

  • Hello.

    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."

    followed by:

    "The form contains XML that cannot be parsed:
    Invalid xml declaration.

    Line 1, Position 5
      <?xml version="1.0" encoding="UTF-8" ?> "

    or

    "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:

    ORIGINAL:

    <?xml version="1.0" encoding="UTF-8"?><?mso-infoPathSolution PIVersion="1.0.0.0" href="LINKED SITE WAS HERE" name="urn:schemas-microsoft-com:office:infopath:ARMOR:-myXSD-2005-11-03T09-00-35" productVersion="12.0.0" solutionVersion="2.5.0.21"?><?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?><my:myFields

    NEW:

      <?xml version="1.0" encoding="UTF-8" ?>
      <?mso-infoPathSolution PIVersion="1.0.0.0" href="LINKED SITE WAS HERE" name="urn:schemas-microsoft-com:office:infopath:ARMOR:-myXSD-2005-11-03T09-00-35" productVersion="12.0.0" solutionVersion="2.3.0.16"?>
      <?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?>
    - <my:myFields

    Tuesday, April 24, 2012 1:14 PM

Answers

  • Hi Luther,

    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 code

    Sub 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
        oDoc.Load (strXMLFile)
        strNS = "xmlns:my='" & oDoc.DocumentElement.NamespaceURI & "'"
        
        oDoc.setProperty "SelectionNamespaces", strNS
        Set oNode = oDoc.SelectSingleNode("/my:myFields/my:field2")
        oNode.Text = strNewValue
        oDoc.Save strXMLFile
        MsgBox "Update complete!"
    End Sub

         - 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


    Scott Heim - Microsoft Office InfoPath and SharePoint Designer Online Community Support

    Tuesday, May 1, 2012 1:52 PM

All replies

  • 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.

    Regards,
    Ian Kavanagh
    Microsoft Online Community Support

    Thursday, April 26, 2012 5:32 PM
  • Hi Luther,

    Thank you for your question.
    I am trying to involve someone familiar with this topic to further look at this issue.

    Thanks,
    Lhan Han
    Friday, April 27, 2012 3:15 AM
    Moderator
  • Hi Luther,

    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.

    Thanks,

    Scott


    Scott Heim - Microsoft Office InfoPath and SharePoint Designer Online Community Support

    Friday, April 27, 2012 2:34 PM
  • "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.


    Sincerely yours.

    Tuesday, May 1, 2012 6:01 AM
  • Hello, Scott.

    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,

    Luther Blissett.


    Sincerely yours.

    Tuesday, May 1, 2012 6:08 AM
  • Hi Luther,

    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 code

    Sub 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
        oDoc.Load (strXMLFile)
        strNS = "xmlns:my='" & oDoc.DocumentElement.NamespaceURI & "'"
        
        oDoc.setProperty "SelectionNamespaces", strNS
        Set oNode = oDoc.SelectSingleNode("/my:myFields/my:field2")
        oNode.Text = strNewValue
        oDoc.Save strXMLFile
        MsgBox "Update complete!"
    End Sub

         - 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


    Scott Heim - Microsoft Office InfoPath and SharePoint Designer Online Community Support

    Tuesday, May 1, 2012 1:52 PM
  • 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.

    Saturday, July 28, 2012 3:27 PM