Private Function XmlToExcelfile(ByVal XMLSourcefileAs String, ByVal ExcelfileAs String)
Dim Exapp As New Excel.Application
Dim fileInf As New System.IO.FileInfo(XLSFile)
If fileInf.Exists Then
SetAttr(XLSXFile, vbNormal)
fileInf.Delete()
End If
Dim fxml As New System.IO.FileInfo(XMLFile)
If fxml.Exists Then
exapp.Workbooks.Open(XMLFile)
exapp.Workbooks.Item(1).CheckCompatibility = True
exapp.DisplayAlerts = False
exapp.Workbooks.Item(1).SaveAs(XLSXFile,exappcel.XlFileFormat.xlWorkbookDefault)
exapp.DisplayAlerts = False
exapp.Workbooks.Close()
SetAttr(XMLFile, vbNormal)
fxml.Delete()
Else
MessageBox.Show("XML File does not exists")
End If
Dim file2 As New System.IO.FileInfo(XLSXFile)
If file2.Exists Then
SetAttr(XLSFile, FileAttribute.Normal)
End If
exapp.Quit()
Exapp= Nothing
MessageBox.Show("xml file Exported Successfully to Excel")
Return True
End Function
Call this code on button click
Dim oldfile As String = ""
Dim DialogtosaveAs New SaveFileDialog
Dialogtosave.InitialDirectory = "I:\"
Dialogtosave.Title = "Save as Excel Files"
Dialogtosave.CheckPathExists = True
Dialogtosave.DefaultExt = "xml"
Dialogtosave.Filter = "Excel Files (*.xls)|"
Dialogtosave.FilterIndex = 2
Dialogtosave.RestoreDirectory = True
Dialogtosave.AddExtension = True
If (Dialogtosave.ShowDialog() = DialogResult.OK) Then
oldfile = Dialogtosave.FileName
End If
Dim dt As DataTable = dv.ToTable
ExportToXml(oldfile, dt)
Dim oldfilenew As String = oldfile.Substring(0, oldfile.Length - 3)
oldfilenew = oldfilenew & "xlsx"
XmlToExcel(oldfile, oldfilenew)