create xml files RRS feed

  • Question

  • Hi,

    I have some data in an excel sheet(Sheet1). I want that data to be saved in xml format. Each cell starting from A1 to C5 contains some data.Now I have a command button in Sheet2 and I want when a user clicks on the button system will ask for a path where all the data will be saved and then the data that we have in each cell should get saved individually in that path with their cell name. 

    Please see the example below:

    cellA1->Save as "A1.xml", cellA2->save as "A2.xml", ...... and so on.


    Saturday, November 29, 2014 7:52 PM

All replies

  • Hi Deb_chatt

    See below code, might help

    Public Sub WriteIndividualCellsAsXMLFile()
    Dim strPath, strNewPath As String
    Dim sXMLStart, sXMLEnd As String
    Dim fso As Object

    Dim oWorkSheet As Worksheet
    Dim lCols As Long, lRows As Long
    Dim oFile As Object
    Dim strText As String

    Set oWorkSheet = ThisWorkbook.Worksheets(1) 'Guess, you require firstSheet

    lCols = 3 'Till column C
    lRows = 5 ' Till row 5

    sXMLStart = "<?xml version=" & Q & """1.0""" & Q & " encoding=" & Q & """UTF-8""" & Q & "?><Data>"
    sXMLEnd = "</Data>"

    strPath = "C:\someFolder\" 'you may need to search "BrowseForFolder"

    For j = 1 To lRows 'Each Rows
        For i = 0 To lCols - 1 'Each columns
            'Assumes no blank column names
            If Trim(Cells(j, i + 1).Value) = "" Then Exit For
            Set fso = CreateObject("Scripting.FileSystemObject")
            strNewPath = strPath & Cells(j, i + 1).FormulaR1C1 & ".xml" 'FileName
            strText = sXMLStart & Cells(j, i + 1).Value & sXMLEnd 'XML Data
            Set oFile = fso.CreateTextFile(strNewPath) 'Create file
            oFile.WriteLine strText
            Set fso = Nothing
            Set oFile = Nothing
        Next i
    Next j

    End Sub

    Monday, December 1, 2014 6:57 AM
  • Hi Karthick.kb

    Thanks for your help. I have tried your solution but after clicking on the button nothing is happening. :( 

    Can you please modify your code so that it can perform the desire  action.

    Saturday, December 6, 2014 8:30 PM
  • Hi,

    Let me tell you the error that I am getting at time of executing the code.

    Bad File Name or Number in the below mentioned line:

    Set oFile = fso.CreateTextFile(strNewPath) 


    Saturday, December 6, 2014 8:42 PM
  • Please create a folder in c drive with the name "someFolder".
    Monday, December 8, 2014 5:24 AM
  • I have created but still getting the same error..
    Saturday, December 13, 2014 7:09 PM
  • Hi,

    I want to save the file name as cell # like cellA1->Save as "A1.xml", cellA2->save as "A2.xml", ...... and so on. but it is saving the file as the content of the cell.

    Also, if any blank columns are appearing on the sheet within the given range then the script is ignoring the entire row in which the blank column belongs.

    I want that wherever it gets a value within the given range(irrespective of blank cell) it should create a xml file with the cell name. Please let me know what changes are needed in order to achieve the desire result.


    Sunday, December 14, 2014 7:11 PM