Dear
@kennypete, Thank you so much for your help. Cannot thank you enough for this.
One last request, i have removed the xml tags since i only save the data in xml format. The only issue which i get when i save the data is it concatenate the each row data.
Following the updated code:
Sub Sheet1CurrentRegionToXML()
Dim strStartingSheet As String
Dim lngRow As Long
Dim varSaveAsXML As Variant
Dim rngA As Range
Dim strRangeXML As String
strStartingSheet = ActiveSheet.Name
' Turn off showing the screen
Application.ScreenUpdating = False
' NB: Hardcoded to "Sheet1" tab as requested!
Sheets("Sheet1").Visible = True
Sheets("Sheet1").Select
' Now using CurrentRegion
Set rngA = Range("A1").CurrentRegion
' Reselect the starting sheet before rehiding Sheet1
Sheets(strStartingSheet).Select
Sheets("Sheet1").Visible = False
' Turn on showing the screen
Application.ScreenUpdating = True
varSaveAsXML = Application.GetSaveAsFilename(FileFilter:="XML (XML data) (*.xml), *.xml")
If varSaveAsXML = False Then Exit Sub
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim oFile As Object
Set oFile = fso.CreateTextFile(varSaveAsXML)
Dim rngCell As Range
For Each rngCell In rngA
If CInt(Split(rngCell.Address, "$")(2)) <> lngRow Then
strRangeXML = strRangeXML
lngRow = CInt(Split(rngCell.Address, "$")(2))
oFile.writeline strRangeXML
End If
strRangeXML = strRangeXML & rngCell.Value
Next rngCell
oFile.writeline
oFile.Close
Set fso = Nothing
Set oFile = Nothing
End Sub
and the result which i get from the above code is as follows:
View attachment 36440
But the result of the output data which i am looking is as follows
13565981022ssGGMMOOSHIB
13565981022ssGGMMOOSHIB
13565981022ssGGMMOOSHIB
13565981022ssGGMMOOSHIB
13565981022ssGGMMOOSHIB
13565981022ssGGMMOOSHIB
13565981022ssGGMMOOSHIB
13565981022ssGGMMOOSHIB
13565981022ssGGMMOOSHIB
Many thanks once again
Best Regards,