Create xml for each row for excel file

[JAVEDR]

Respected sir / mam, i am looking for automation or macro to generate xml for each row please find attached excel file where date is paste and seperate .xml file which need to be generated

seperate xml file & excel file path

thanking you for valuable time & feedback.

 

[juddaaaa]

Something like this...

Sub ExportToXML()

    Dim Filename As Range
    Dim FSO As Object
    Dim XML As Object
   
    Set FSO = CreateObject("Scripting.FileSystemObject")
   
    With ActiveSheet
        For Each Filename In .Range("B2:B" & GetLastRow("Sheet1"))
            Set XML = FSO.CreateTextFile( _
                        Filename:=ThisWorkbook.Path & "\" & Filename.Value & ".xml", _
                        Overwrite:=True)
           
            With Filename
                XML.WriteLine ("<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>")
                XML.WriteLine ("    <File>")
                XML.WriteLine ("        <Date>" & .Offset(0, -1).Value & "</Date>")
                XML.WriteLine ("        <FileName>" & .Value & "</FileName>")
                XML.WriteLine ("        <FileExtension>" & .Offset(0, 1).Value & "</FileExtension>")
                XML.WriteLine ("        <Title>" & .Offset(0, 2).Value & "</Title>")
                XML.WriteLine ("        <Mappings>")
                XML.WriteLine ("            <Mapping>")
                XML.WriteLine ("                <RICCode>" & .Offset(0, 3).Value & "</RICCode>")
                XML.WriteLine ("                <SEDOL>" & .Offset(0, 4).Value & "</SEDOL>")
                XML.WriteLine ("                <ISIN>" & .Offset(0, 5).Value & "</ISIN>")
                XML.WriteLine ("                <BBGTicker>" & .Offset(0, 6).Value & "</BBGTicker>")
                XML.WriteLine ("            </Mapping>")
                XML.WriteLine ("        </Mappings>")
                XML.WriteLine ("    </File>")
            End With
           
            XML.Close
        Next Filename
    End With
   
    Set XML = Nothing
    Set FSO = Nothing

End Sub

Function GetLastRow(wkSheet As String) As Long

    With Worksheets(wkSheet)
        GetLastRow = .Cells.Find( _
                        What:="*", _
                        LookIn:=xlValues, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious).Row
    End With

End Function

 

[JAVEDR]

million thanks sir this is what i was looking for

 

[JAVEDR]

Just one query when i run macro & xml files are exported in this line DATA is changed "<Date>7/12/2018 5:02:13 PM</Date>"

as i need data to be displayed "<Date>2018-07-12T17:02:13Z</Date>" Like this.

 

[YasserKhalil]

Change .Value to .Text

XML.WriteLine ("        <Date>" & .Offset(0, -1).Text & "</Date>")

 

[JAVEDR]

Change .Value to .Text

XML.WriteLine ("        <Date>" & .Offset(0, -1).Text & "</Date>")

I have tried above but still data is exported "<Date>2018-07-12T17:2:13Z</Date>" instead of "<Date>2018-07-12T17:02:13Z</Date>" Like this.

i.e -- 17:2 where as data need to be shown 17:02

 

[YasserKhalil]

In the code declare this line

Dim v, x, i As Integer, s As String

and implement those lines

                xml.WriteLine ("    <File>")
                
                
                v = Split(.Offset(, -1).Text, "T")
                x = Split(v(1), ":")
                s = Empty
                For i = LBound(x) To UBound(x)
                    s = s & IIf(s > Empty, ":", Empty) & Format(Replace(x(i), "Z", ""), "00")
                Next i

                xml.WriteLine ("        <Date>" & v(0) & "T" & s & "Z" & "</Date>")

 

[JAVEDR]

In the code declare this line

Dim v, x, i As Integer, s As String

and implement those lines

                xml.WriteLine ("    <File>")
               
               
                v = Split(.Offset(, -1).Text, "T")
                x = Split(v(1), ":")
                s = Empty
                For i = LBound(x) To UBound(x)
                    s = s & IIf(s > Empty, ":", Empty) & Format(Replace(x(i), "Z", ""), "00")
                Next i

                xml.WriteLine ("        <Date>" & v(0) & "T" & s & "Z" & "</Date>")

Hello sir,

where to implement & declare this codes can you please advise.

 

[YasserKhalil]

After those lines

  Dim Filename As Range
    Dim FSO As Object
    Dim XML As Object

and you can declare them just before the new lines (no matter)

 

[JAVEDR]

After those lines

  Dim Filename As Range
    Dim FSO As Object
    Dim XML As Object

and you can declare them just before the new lines (no matter)

hello sir, i'm getting attached error.