Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    May 2013
    Posts
    46
    Thanks
    29
    Thanked 4 Times in 4 Posts

    Creating an XML file modified by data in spreadsheet

    One of the final tasks of the project I am working on is to take a 3378 line imported XML file and modify it line by line with residing data in my spreadsheet. The code I have writen works fine but now I have to export as an XML file and name it in the format of EMP concatenated by an employee ID and save it to a folder on my desktop. Later, I will upload the XML files into another program. Any ideas on how to best achieve this?

    Thank you in advance
    Alexandra

  2. #2
    Platinum Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,745
    Thanks
    7
    Thanked 242 Times in 230 Posts
    The XML export will have to be done in code with tags as required. I'd base it on the original import. When doing this it's always useful to have column headings to use as tag names.

    cheers, Paul

  3. The Following User Says Thank You to Paul T For This Useful Post:

    Alouso (2013-07-28)

  4. #3
    Lounger
    Join Date
    May 2013
    Posts
    46
    Thanks
    29
    Thanked 4 Times in 4 Posts
    Thanks Paul for your reply. Using some code I found, the XML file is copied to lines within the spreadsheet which contains all the tags included with the original file. I need to make changes on many of the lines then rebuild it back into an XML file again. Any ideas?

    XML.jpg

    Al
    Last edited by Alouso; 2013-07-28 at 10:32.

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,410
    Thanks
    208
    Thanked 834 Times in 767 Posts
    Al,

    A small sample file with some of the lines you need to export would be very helpful.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #5
    Lounger
    Join Date
    May 2013
    Posts
    46
    Thanks
    29
    Thanked 4 Times in 4 Posts
    RG,

    Because of confidentiality, I cannot post the file but please see th image in the previous post for a sample of the data.

    Thanks,
    Al

  7. #6
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,293
    Thanks
    47
    Thanked 257 Times in 237 Posts
    Alexandra,

    The following code will create an .xml file line-by-line while making some assumptions:

    1. The xml code is contained within one column. Change the 1 in the line Cells(i, 1).value it to whatever column the code may reside.

    2. You did not mention as to whether or not you are using a header. Change the 1 in the for/next loop to the first line of .xml code.

    3. This example creates a file in a folder I created called Employees, Change the path to where you are saving the files

    4. A variable ID holds the employee ID number. Change to the variable or cell value that you need to reference

    Note: To make this work, you will need to add the reference Microsoft Scripting Runtime. Alt-11 to open the VB editor> Tools >References...> Scroll to the Microsoft Scripting Runtime and check the box to the left of it then click OK

    HTH,
    Maud

    Script.png

    Code:
    Public Sub Write2xml(ID As String)
    'CREATE THE XML FILE AND WRITE TO IT
    Dim fso As New FileSystemObject
    Dim stream As TextStream  'DECLARE A TEXT STREAM
    
    'CREATE A TEXT STREAM
    Set stream = fso.CreateTextFile("C:\Users\Maudibe\Desktop\Employees\EMP" & ID & ".xml", True)
    For i = 1 To 3378
        stream.Write Cells(i, 1).value & vbNewLine  'WRITE TO FILE
    Next i
    
    stream.Close
    End Sub

  8. The Following User Says Thank You to Maudibe For This Useful Post:

    Alouso (2013-07-28)

  9. #7
    Lounger
    Join Date
    May 2013
    Posts
    46
    Thanks
    29
    Thanked 4 Times in 4 Posts
    Thank you everyone for your quick responses. Maudibe, I will try your code at work tomorrow and thanks again for your help.

    Al

  10. #8
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,293
    Thanks
    47
    Thanked 257 Times in 237 Posts
    Alexandra,

    I set up the routine as being called with the ID parameter be passed to it. Remove the parameter in the sub header if you will be using a public variable or a cell value for the ID. If you will be creating multiple .xml files with additional looping, consider that it may be a lengthy process.

    Maud

  11. The Following User Says Thank You to Maudibe For This Useful Post:

    Alouso (2013-07-29)

  12. #9
    Lounger
    Join Date
    May 2013
    Posts
    46
    Thanks
    29
    Thanked 4 Times in 4 Posts
    Maudibe,

    My biggest obstacle was to get IT to allow the reference the Microsoft Scripting Runtime. The XML file is in one column. I had modified the code slightly to pull the Employee ID directly from the active cell and the code worked perfectly. It took about 3 seconds to create the file but I will be doing one at a time so it is not really a problem. What an awesome solution. Thank you again
    A.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •