none
Exporting to XML RRS feed

  • Question

  • Thanks in advance for the help.

    Here is what i need to do. I have a query pulling from tables giving me a parts list as follows:

    Parts List: [xmlcode] [Part number] [xmlcode] [Part Name] [xmlcode] [Qty] [xmlcode] [Group No] [xmlcode] [Group Name] [xmlcode] .

    I can do all of the above in the query. I can show you the code if you want but it is very long. The code works, my problem is this: i need to put code before parts list above and after it.  I will call it precode in front of the parts list above and post code after the parts list. I can show you this code also if you want to see it. It works. My problem is putting the 3 items together in VBA and exporting and saving as XML.  The programs that need to open the list will only read XML. So basiclly it will look like this:

    [Pre Code] [Parts List] [Post Code].  I have tried saving the pre and post code as strings and combining with the parts list but get errors saying there is a syntax error. I have also tryed saving the code in tables and pulling in with VBA and putting together with the parts list. Nothing i have tried works.  Any help would be very much appreciated.

    Monday, October 30, 2017 1:33 PM

Answers

  • Hi Jerry,

    It seems that your original issue has been resolved, I would suggest you mark useful reply to close this thread. For your new issue, you could post a new thread so that more new eyes will see your issue and give useful suggestion.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Jerry215 Thursday, November 16, 2017 9:20 AM
    Wednesday, November 15, 2017 2:52 AM

All replies

  • Hi,

    An XML file is simply a Text file. Have you tried just creating a text file using VBA and simply "write" the values from your code and query into this text file? You can either use the Open statement or a FileSystemObject to create a text file.

    Just my 2 cents...

    Monday, October 30, 2017 2:22 PM
  • Did you know there is a built-in way to export data from Access to an XML file? Check out Application.ExportXML in the help file.

    -Tom. Microsoft Access MVP

    Tuesday, October 31, 2017 2:06 AM
  • answer #1: I got it to export as a text file, but can't open it in the other 2 programs that need to open the data.

    Answer #2: Looking into this application.exportXML. Looks promising.

    Thank you both. I will report back after i figure out how to use option #2.

    Tuesday, October 31, 2017 1:11 PM
  • Hi Jerry,

    Thanks for the update. Good luck.

    Let us know if you get stuck.

    Tuesday, October 31, 2017 2:39 PM
  • Ok Export XML works great.  But still having issues putting the pre-code in front of the query information before exporting.  As i said before i have to put pre-code in front of the query parts list and post code after it before exporting to XML.  Otherwise the other programs used will not open the parts list.  I have tried adding SQL and combining before exporting. access gives a warning when compiling that there is a syntax error. Any ideas how to combine these before exporting?

    Thank you!

    Thursday, November 2, 2017 8:37 PM
  • Hi Jerry215,
    I would suggest use Export XML to export the XML file and then you could try to use VBA to edit the XML file to put the pre-code the query information.
    For editing XML file in VBA, you could refer to below link.
    A Beginner's Guide to the XML DOM
    Best Regards,
    Terry

    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 3, 2017 7:50 AM
  • Hi Jerry,

    >> As i said before i have to put pre-code in front of the query parts list and post code after it before exporting to XML.  Otherwise the other programs used will not open the parts list.

    What do you mean by this? What is pre-code?

    Your query might be complex, I would suggest you create a simple demo which could reproduce your issue.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 6, 2017 6:59 AM
  • Thanks, here is the "pre-code, parts list and post-code":

    Pre-code:  <?xml version="1.0" encoding="UTF-8"?><!DOCTYPE PARTSLIST [<!ELEMENT PARTSLIST (HEADER, PARTS*)><!ATTLIST PARTSLIST VERSIONNUMBER CDATA #REQUIRED><!ELEMENT HEADER (SERIALNO, ORDERID, WORKORDER, SEGMENT, JOBCODE, USERID, ADDITIONALNOTES)><!ELEMENT SERIALNO (#PCDATA)><!ELEMENT ORDERID (#PCDATA)><!ELEMENT WORKORDER (#PCDATA)><!ELEMENT SEGMENT (#PCDATA)><!ELEMENT JOBCODE (#PCDATA)><!ELEMENT USERID (#PCDATA)><!ELEMENT ADDITIONALNOTES (#PCDATA)><!ELEMENT PARTS (PART*)><!ELEMENT PART (PARTNO, PARTNAME, MODIFIER, QUANTITY, GROUPNO, GROUPNAME, USERNOTE, PARENTAGE?, REFERENCENO?, PARTNOTE, GRAPHICS?, SMCSCODE?, TYPE)><!ELEMENT PARTNO (#PCDATA)><!ELEMENT PARTNAME (#PCDATA)><!ELEMENT MODIFIER (#PCDATA)><!ELEMENT QUANTITY (#PCDATA)><!ELEMENT GROUPNO (#PCDATA)><!ELEMENT GROUPNAME (#PCDATA)><!ELEMENT USERNOTE (#PCDATA)><!ELEMENT PARENTAGE (#PCDATA)><!ELEMENT REFERENCENO (#PCDATA)><!ELEMENT PARTNOTE (#PCDATA)><!ELEMENT GRAPHICS (#PCDATA)><!ELEMENT SMCSCODE (#PCDATA)><!ELEMENT TYPE (#PCDATA)> ]><PARTSLIST VERSIONNUMBER="SISXML1.0"><HEADER><SERIALNO>000</SERIALNO><ORDERID><![CDATA[]]></ORDERID><WORKORDER/><SEGMENT/><JOBCODE/><USERID><![CDATA[]]></USERID><ADDITIONALNOTES/></HEADER>

    The parts list: <PARTS><PART><PARTNO><![CDATA[8T4896]]></PARTNO><PARTNAME><![CDATA[WASHER]]></PARTNAME><MODIFIER/><QUANTITY><![CDATA[2]]></QUANTITY><GROUPNO><![CDATA[]]></GROUPNO><GROUPNAME><![CDATA[]]></GROUPNAME><USERNOTE/><PARENTAGE><![CDATA[0]]></PARENTAGE><REFERENCENO><![CDATA[1]]></REFERENCENO><PARTNOTE/><SMCSCODE><![CDATA[]]></SMCSCODE><TYPE><![CDATA[AA]]></TYPE></PART><

    Post-code:    </PARTS></PARTSLIST>

    The pre-code and post-code are always the same, the size of the parts list will depend on what segment of the work order is selected. the code works. If i copy and past all together in notepad and save as .XML the other programs will open it.  Thank you for all the help.  Jerry215.

    Wednesday, November 8, 2017 12:02 PM
  • Hi Jerry,

    Earlier, I suggested you use FSO to create the XML file, but you said it didn't work. I am not sure what you did and why it didn't work, but I just gave it a try using the following code, and I didn't have any issues.

    Public Function CreateXML() As Variant
    '11/8/2017
    'thedbguy@gmail.com
    
    Dim strPreCode As String
    Dim strMiddleCode As String
    Dim strPostCode As String
    Dim fso As Object
    Dim f As Object
    
    'assign XML code in three variables
    strPreCode = "<?xml version=""1.0"" encoding=""UTF-8""?>..."
    strMiddleCode = "<PARTS><PART><PARTNO><![CDATA[8T4896]]>..."
    strPostCode = "</PARTS></PARTSLIST>"
    
    'create the XML file
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.createTextFile(CurrentProject.Path & "\test.xml")
    
    With f
        .WriteLine strPreCode
        .WriteLine strMiddleCode
        .WriteLine strPostCode
        .Close
    End With
    
    Set f = Nothing
    Set fso = Nothing
    
    End Function

    Could you give it a try to see if your application can open the resulting XML file? Make sure to assign the complete XML code in the variables. I only used a portion of your XML code to test if the text file will be created propertly.

    Hope it helps...


    • Edited by .theDBguy Wednesday, November 8, 2017 5:03 PM
    Wednesday, November 8, 2017 5:02 PM
  • Hi Jerry,

    Thanks for sharing more information, I could understand your issue now.

    >>My problem is putting the 3 items together in VBA and exporting and saving as XML.

    Could you share us the code related with these steps? The result could not be opened by your application, could you share us what is the application? I suggest you try other XML Validation tool to check whether it is valid.

    >>If i copy and past all together in notepad and save as .XML the other programs will open it.

    Since there are two XML files, one works and another not, I suggest you compare them by XMl comparing tool online.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 9, 2017 2:36 AM
  • Thank you DBguy and Edward.  I tried using an open statement, but i obviously i didn't set it up proppery. I was getting syntax errors even though it was setup and declaired as a string. I will try FSO like you have shown.  I will let you know how it works.

    Thank you both very much....Jerry215.

    Friday, November 10, 2017 7:20 PM
  • Hi Jerry,

    If you have any update, please feel free to let us know.

    We hope your issue could be resolved by our effort.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 13, 2017 2:10 AM
  • Thank you, the fso works great. now my problem is the parts list has to come from a query. I need to run this query and put the data from it into the strMiddleCode as a string without column headers. I have tried using the open DAO recordset method and it is not setup properly and i can't get it converted into a string.  Any ideas would help.  this is the sql from the query:

    SELECT "<PART><PARTNO><![CDATA[" AS Expr1, dbo_WOPPART0.PANO20 AS PN, "]]></PARTNO><PARTNAME><![CDATA[" AS Expr2, dbo_WOPPART0.DS18 AS [Desc], "]]></PARTNAME><MODIFIER/><QUANTITY><![CDATA[" AS Expr3, dbo_WOPPART0.TOIVQY AS TQY, "]]></QUANTITY><GROUPNO><![CDATA[" AS Expr4, "GroupNO" AS Expr5, "]]></GROUPNO><GROUPNAME><![CDATA[" AS Expr6, "GroupName" AS Expr7
    FROM (dbo_WOPPART0 INNER JOIN dbo_WOPHDRS0 ON dbo_WOPPART0.WONO = dbo_WOPHDRS0.WONO) INNER JOIN dbo_WOPSEGS0 ON (dbo_WOPHDRS0.WONO = dbo_WOPSEGS0.WONO) AND (dbo_WOPPART0.WONO = dbo_WOPSEGS0.WONO) AND (dbo_WOPPART0.WOSGNO = dbo_WOPSEGS0.WOSGNO)
    WHERE (((dbo_WOPPART0.TOIVQY)>0) AND ((dbo_WOPPART0.WONO)=[forms]![ConversionF]![WONO]) AND ((dbo_WOPSEGS0.WOSGNO)=[Forms]![ConversionF]![SegNo]));

    Thank you for the help.

    Tuesday, November 14, 2017 6:04 PM
  • Hi,

    Have you tried the TransferText method?

    Or, perhaps, try using the Application.ExportXML against the query.

    Just a thought...

    • Edited by .theDBguy Tuesday, November 14, 2017 6:20 PM
    Tuesday, November 14, 2017 6:19 PM
  • By the way, if you wanted to read a text file into a variable, take a look at this article:

    How to read a Text file

    Hope it helps...

    Tuesday, November 14, 2017 6:29 PM
  • Hi Jerry,

    It seems that your original issue has been resolved, I would suggest you mark useful reply to close this thread. For your new issue, you could post a new thread so that more new eyes will see your issue and give useful suggestion.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support Please remember to click &amp;quot;Mark as Answer&amp;quot; the responses that resolved your issue, and to click &amp;quot;Unmark as Answer&amp;quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Jerry215 Thursday, November 16, 2017 9:20 AM
    Wednesday, November 15, 2017 2:52 AM