Retrieve and save to another excel workbook

[bigzos]

Hey,,I tried the following code to retrieve data from an excel file,apply query and save the result of query to another excel file.
Now there are two problems:
One,I am getting the required data but some reduntant unwanted data too.
Second,It only works if the excel file, from which the data is retrieved, is open.
Help me out.

 Dim dr As OleDbDataReader
            Dim i As Integer
            Dim strLine As String = ""
            Dim fileExcel As String
            Dim objFileStream As FileStream
            Dim objStreamWriter As StreamWriter
            Dim MyCommand As OleDbCommand

            Dim MyConnection As System.Data.OleDb.OleDbConnection
            MyConnection = New System.Data.OleDb.OleDbConnection( _
            "provider=Microsoft.Jet.OLEDB.4.0; " & _
            "data source=  C:\Users\Space Era\Documents\Book1.xlsx  ; " & _
            "Extended Properties=Excel 8.0")
            fileExcel = "c:\temp\test.xls"

            'Use FileStream to create the .xls file.
            objFileStream = New FileStream(fileExcel, FileMode.OpenOrCreate, FileAccess.Write)
            objStreamWriter = New StreamWriter(objFileStream)

            MyConnection.Open()

            MyCommand = New System.Data.OleDb.OleDbCommand("select * from [Sheet1$] where [Name]='Rahul' ", MyConnection)
            dr = MyCommand.ExecuteReader()
            For i = 0 To dr.FieldCount - 1
                If Not String.IsNullOrEmpty(strLine) Then
                    strLine &= ","
                End If
                strLine &= """" & dr.GetName(i).ToString & """"
            Next

            'Write the field name information to file.
            objStreamWriter.WriteLine(strLine)

            'Reinitialize the string for data.
            strLine = ""

            'Enumerate the database that is used to populate the file.
            While dr.Read()
                For i = 0 To dr.FieldCount - 1
                    strLine = strLine & dr.GetValue(i) & Chr(9)
                Next
                objStreamWriter.WriteLine(strLine)
                strLine = ""
            End While


            dr.Close()

            objStreamWriter.Close()
            objFileStream.Close()

            MyConnection.Close()

[Reverend Jim]

Can you post an excel file with some sample data which causes the problem you are describing?

[Begginnerdev]

You will need to make use of the Microsoft Office Interopt libraries if you are wanting to manipulate a workbook that is not open.

[bigzos]

@Reverend ,i guess the excelfile type is not allowed for upload here.Anyways,I have an excel file with data
Name|Emailid|Asset
Rahul|[e-mail]|ty
Vijay|[e-mail]|yu
Rahul|[e-mail]|rt
When i apply the query posted above i.e.

select * from [Sheet1$] where [Name]='Rahul'

,i get
Name|Emailid|Asset
Rahul|[e-mail]|ty
Rahul|[e-mail]|rt
"Blank"|[e-mail]|rt

@Beginnerdev,i am using Microsoft.office.interop and also added excel library 12 from add reference section,still needs the file opened

[Reverend Jim]

I'll have a look in a bit. I have to go pound nails for a while. To read the data you don't need interopt. You can do it using ADO and treating the source as a database. Should also be possible via oledb. I'll have a look later. If you don't want to use interopt to write, you could always write the output as a csv file which can be opened by Excel. Either way works.

[bigzos]

Ok ,i worked on it and most of the problem is solved,now i am only left with the thing that it doesnt work until i explicitly open the excel file,,any help in this regard would be appreciated.

[Reverend Jim]

I'm going to post the first part regardless. It may be better than your solution or it may be worse.

Imports ADODB

Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        Dim con As New ADODB.Connection
        Dim rec As New ADODB.Recordset

        con.Open("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=D:\temp\asset.xls;")
        rec.Open("select * from [Sheet1$] where [Name] = 'Rahul'", con, CursorTypeEnum.adOpenStatic)

        Do Until rec.EOF
            Debug.WriteLine(rec("Name").Value & " " & rec("Emailid").Value & " " & rec("Asset").Value)
            rec.MoveNext()
        Loop

        rec.Close()
        con.Close()

    End Sub

End Class

[Reverend Jim]

More complete - also write an Excel file:

Imports ADODB
Imports Microsoft.Office.Interop

Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        Dim con As New ADODB.Connection
        Dim rec As New ADODB.Recordset
        Dim xls As New Excel.Application
        Dim sheet As Excel.Worksheet

        xls.Workbooks.Add()
        sheet = xls.ActiveWorkbook.ActiveSheet

        con.Open("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=D:\temp\asset.xls;")
        rec.Open("select * from [Sheet1$] where [Name] = 'Rahul'", con, CursorTypeEnum.adOpenStatic)

        Dim row As Integer = 0

        Do Until rec.EOF
            row += 1
            sheet.Cells(row, 1) = rec("Name").Value
            sheet.Cells(row, 2) = rec("Emailid").Value
            sheet.Cells(row, 3) = rec("Asset").Value
            rec.MoveNext()
        Loop

        rec.Close()
        con.Close()

        xls.ActiveWorkbook.SaveAs("D:\temp\test.xls")
        xls.Workbooks.Close()
        xls.Quit()

        releaseObject(sheet)
        releaseObject(xls)

    End Sub

    Private Sub releaseObject(ByVal obj As Object)

        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try

    End Sub

End Class

[bigzos]

i got it,,i was missing the xlapp.workbooks.open(path)
Problem solved,anyways thanx fro all the replies

[bigzos]

ok there is just one more problem that the data validation in one workbook dont get pass on to the other one,anybody knows the fix?

[bigzos]

@Reverend Jim,I tried your code .It helps to retain the validation,but i am not getting the desired output,It provides me with the required rows plus all the remaining rows and i do not want the remaining rows

[Reverend Jim]

Put a test in the loop to terminate it when you have all the rows that you want.

[bigzos]

Cant think of a test statement since the code seems okay,the recordset should contain only those rows with column name 'rahul' but dont know why it is showing all the rows including those with column name other than 'rahul'

[Reverend Jim]

I only get the requested rows so I don't know what your problem could be.

[bigzos]

Okay the thing is that i am opening the actual workbook too which is causing the problem because if i dont,it will show an error that"external table format not supported".
If i dont open it programmatically and ratther open it explicitly,I get the required rows but without the column names ..I need the column names to be there and again data validation column is not getting saved