View Single Post
  #1 (permalink)  
Old February 12th, 2010, 08:47 PM
Lennie Lennie is offline
Authorized User
Points: 97, Level: 1
Points: 97, Level: 1 Points: 97, Level: 1 Points: 97, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2010
Location: New Zealand
Posts: 18
Thanks: 0
Thanked 1 Time in 1 Post
Default Vb.Net 2003 problem Export XML file to Excel

Hi Friends,
Encounter another interesting problem.
Being ask to write script to transfer data from XML file to Excep and it's not working. Very Puzzle and desperate. Need your help.

This portion of scipt on compilation caused error:
xReader = XmlReader.Create("Order.xml", XMLReaderSettings)

Here are the script

Code:
Imports Excel = Microsoft.Office.Interop.Excel
Imports Word = Microsoft.Office.Interop.Word
Imports System.IO
Imports System.Xml
Imports System.Data
Imports System.Text

  Private Function FExportExcel()
        'export XML file data to Excel Spreadsheet

        Dim objExcel As Excel.Application
        Dim objWorkbook As Excel.Workbook
        Dim objWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        Dim DS As New DataSet
        Dim xReader As XmlReader
        Dim i, x As Integer

        objExcel = New Excel.Application
        objWorkbook = objExcel.Workbooks.Add(misValue)
        objWorkSheet = objExcel.Worksheets("sheet1")

        xReader = XmlReader.Create("Order.xml", XMLReaderSettings)
        DS.ReadXml(xReader)

        For i = 0 To (DS.Tables(0).Rows.Count - 1)
            For x = 0 To (DS.Tables(0).Columns.Count - 1)
                objWorkSheet.Cells(i + 1, x + 1) = DS.Tables(0).Rows(i).Item(i)
            Next
        Next

        objWorkSheet.SaveAs("xml2Excel.xls")
        objWorkbook.Close()
        objExcel.Quit()

        System.Runtime.InteropServices.Marshal.ReleaseComObject(objWorkSheet)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(objWorkbook)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel)

    End Function
__________________


Thank you very much.

Have a good day.

Cheers,
Lennie