Convert XML(XLS) to XLSX

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Convert XML(XLS) to XLSX

Post by YasserKhalil »

Hello everyone

I have a file that I need to convert from xls to xlsx. It seems that the extension is xls but when I opened the file using notepad++, I found the following contents

Code: Select all

<?xml version="1.0" encoding="utf-8"?>
<?mso-application progid="Excel.Sheet"?>
<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
  <ss:Styles>
    <ss:Style ss:ID="General" />
    <ss:Style ss:ID="Number">
      <ss:NumberFormat ss:Format="General Number" />
    </ss:Style>
    <ss:Style ss:ID="DateTime">
      <ss:NumberFormat ss:Format="General Date" />
    </ss:Style>
    <ss:Style ss:ID="Currency">
      <ss:NumberFormat ss:Format="Currency" />
    </ss:Style>
    <ss:Style ss:ID="ShortDate">
      <ss:NumberFormat ss:Format="Short Date" />
    </ss:Style>
  </ss:Styles>
  <ss:Worksheet ss:Name="Sheet1">
    <ss:Table>
      <ss:Row>
        <ss:Cell>
          <ss:Data ss:Type="String">إسم الخصم</ss:Data>
        </ss:Cell>
        <ss:Cell>
          <ss:Data ss:Type="String">رقم العقد</ss:Data>
        </ss:Cell>
        <ss:Cell>
          <ss:Data ss:Type="String">سبب المديونية</ss:Data>
        </ss:Cell>
        <ss:Cell>
          <ss:Data ss:Type="String">قيمة المطالبة</ss:Data>
        </ss:Cell>
        <ss:Cell>
          <ss:Data ss:Type="String">تاريخ الورود</ss:Data>
        </ss:Cell>
        <ss:Cell>
          <ss:Data ss:Type="String">جنسية العميل</ss:Data>
        </ss:Cell>
        <ss:Cell>
          <ss:Data ss:Type="String">تاريخ الإنذار</ss:Data>
        </ss:Cell>
        <ss:Cell>
          <ss:Data ss:Type="String">قيمة المصروف</ss:Data>
        </ss:Cell>
        <ss:Cell>
          <ss:Data ss:Type="String">تاريخ المصروف</ss:Data>
        </ss:Cell>
        <ss:Cell>
          <ss:Data ss:Type="String">نوع المصروف</ss:Data>
        </ss:Cell>
        <ss:Cell>
          <ss:Data ss:Type="String">رقم أمر الأداء</ss:Data>
        </ss:Cell>
        <ss:Cell>
          <ss:Data ss:Type="String">الرقم الآلي</ss:Data>
        </ss:Cell>
      </ss:Row>
      <ss:Row>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">ميثم عبدالله حسين اشكناني</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">700000045332</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">بيع مرابحة محلي</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="Number">
          <ss:Data ss:Type="Number">48948.176</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="ShortDate">
          <ss:Data ss:Type="DateTime">2020-10-28T00:00:00</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">كويتي</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String"></ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="Number">
          <ss:Data ss:Type="Number">50</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="ShortDate">
          <ss:Data ss:Type="DateTime">2022-03-23T00:00:00</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">مصروفات حجز سيارة</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">2021/500969</ss:Data>
        </ss:Cell>

        </ss:Cell>
        <ss:Cell ss:StyleID="ShortDate">
          <ss:Data ss:Type="DateTime">2022-03-23T00:00:00</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">مصروفات حجز سيارة</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">2021/500969</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">211856060</ss:Data>
        </ss:Cell>
      </ss:Row>
      <ss:Row>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">ميثم عبدالله حسين اشكناني</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">700000045332</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">بيع مرابحة محلي</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="Number">
          <ss:Data ss:Type="Number">48948.176</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="ShortDate">
          <ss:Data ss:Type="DateTime">2020-10-28T00:00:00</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">كويتي</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String"></ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="Number">
          <ss:Data ss:Type="Number">50</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="ShortDate">
          <ss:Data ss:Type="DateTime">2022-03-23T00:00:00</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">مصروفات حجز سيارة</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">2021/500969</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">211856060</ss:Data>
        </ss:Cell>
      </ss:Row>
    </ss:Table>
  </ss:Worksheet>
</ss:Workbook>
How can I convert it to xlsx ?
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert XML(XLS) to XLSX

Post by HansV »

Change the extension from .xls to .xml
Then open it in Excel and save it as .xlsx
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Convert XML(XLS) to XLSX

Post by YasserKhalil »

Thanks a lot, my tutor. I already did that for one file and it is ok. But I have a folder with 200 files

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert XML(XLS) to XLSX

Post by HansV »

You can loop through the .xls files in the folder using the Dir function.
Use Name ... As ... to change the extension of the file in the loop from .xls to .xml
Use Workbooks.OpenXML to open the XML file.
Use SaveAs to save the workbook as .xlsx
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Convert XML(XLS) to XLSX

Post by YasserKhalil »

This is my attempt but it takes too long time

Code: Select all

Sub ConvertXmlToXlsx()
    Dim XML, xmlFolder As String, convFolder As String, NewFileName As String
    Dim ConvertThis As Workbook
    Application.DisplayAlerts = False
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    xmlFolder = ThisWorkbook.Path & "\xml\"
    convFolder = ThisWorkbook.Path & "\xls\"
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(xmlFolder)
    For Each objFile In objFolder.Files
        If UCase(Right(objFile.Name, Len(XML))) = UCase(XML) Then
            NewFileName = convFolder & objFile.Name
            Set ConvertThis = Workbooks.Open(objFolder & "\" & objFile.Name)
            ConvertThis.SaveAs Filename:=NewFileName, FileFormat:= _
                xlOpenXMLWorkbook
            ConvertThis.Close
        End If
    Next objFile
End Sub


User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert XML(XLS) to XLSX

Post by HansV »

The line

Code: Select all

        If UCase(Right(objFile.Name, Len(XML))) = UCase(XML) Then
should be

Code: Select all

        If UCase(Right(objFile.Name, Len("XML"))) = UCase("XML") Then
or somewhat shorter

Code: Select all

        If UCase(Right(objFile.Name, 3)) = "XML" Then
Opening and saving 200 files will always take time...
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Convert XML(XLS) to XLSX

Post by YasserKhalil »

Thanks a lot, my tutor
In fact, I have searched a lot and found at last python code that enables me to do the task in a shorter time.
Best and Kind Regards

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert XML(XLS) to XLSX

Post by HansV »

Ah - I don't know anything about Python...
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Convert XML(XLS) to XLSX

Post by YasserKhalil »

Python is so simple and more powerful. I recommend to have a look through this programming language.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert XML(XLS) to XLSX

Post by HansV »

I'll restrict myself to Monty Python...
Best wishes,
Hans