Excel to XML conversion

[priyaahh]

Hi Friends, I want code to convert Excel data to xml using VBA Macros. I have once code which i got from codeproject, its converting well. But when I give space inbetween words in first row(Header part) its giving error. Not only space all special characters. Kindly help. Its very urgent. Thanks in Advance. Regards, Priya.

[evd46]

Hi Priya, I'm developping a website for an befriended artist. I've put her concerts and repertoire in two Excel files - easy for her to update - and added one single macro to them that runs several tasks in a row: - sort the data - search for duplicates - rename the files with a date-code (YYMMDD) in the name, what helps for backing-up the data - convert the data first into XML-format and then into an HTML-table that can be forwarded to people who might be interested in printing the data - open an form in IE that uploads the files in XML and HTML-formats directly to the server of her website. As far as I'm aware, I didn't encounter your type of problem. For your info and hopefully your help, I add the lengthy code hereunder:

Option Explicit
Sub Sort_and_Eliminate_doubles_and_Send()
' This macro is written by Erik van Dyck
'
Dim OldFn, ShortFn As String, NewFn As String, FnRoot As String, DirDest As String, FN As String
Dim rownumber As Integer, colnumber As Integer, n As Integer
Dim MailAd As Variant, RRange As Variant
Dim fso As Object

' Hide screen
Application.DisplayAlerts = False
' Stop "EnableEvents" while making automatic changes
Application.EnableEvents = False

Remplacer_symboles_par_caractères 'remplace "Œ" par "OE", "..." par "etc.", "œ" par "oe"

' Sort
Range("A1").Select 'Select top left corner
Selection.End(xlDown).Select 'Search bottom row
rownumber = ActiveCell.Row 'Remember bottom row
'MsgBox "Bottom row = " & rownumber

Range("A1").Select
Selection.End(xlToRight).Select 'Search most right column
colnumber = ActiveCell.Column
'MsgBox "Most right column = " & colnumber

Range(Cells(1, 1), Cells(rownumber, colnumber)).Sort \_
    Key1:=Range("A2"), Order1:=xlAscending, \_
    Key2:=Range("B2"), Order2:=xlAscending, \_
    Key3:=Range("C2"), Order3:=xlAscending, \_
    Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, \_
    DataOption1:=xlSortNormal, DataOption2:=xlSortNormal

' Identify doubles
n = 2
While Range("A" & n) <> ""
Range("A" & n).Select
If (LCase(Range("A" & n) & Range("B" & n) & Range("C" & n) & Range("D" & n) & Range("E" & n)) = LCase(Range("A" & n - 1) & Range("B" & n - 1) & Range("C" & n - 1) & Range("D" & n - 1) & Range("E" & n - 1))) Then
rownumber = ActiveCell.Row
RR