0 Replies - 285 Views - Last Post: 06 May 2012 - 04:06 AM Rate Topic: -----

#1 meetjoe  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 31
  • Joined: 27-November 10

Excel data validation

Posted 06 May 2012 - 04:06 AM

 xlApp = New Excel.Application
            xlApp.Workbooks.Open("C:\Users\Space Era\Documents\Book1.xlsx")
            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")
            MyConnection.Open()
           
            ' Select the data from Sheet1 ([in-house$]) of the workbook.
            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$] where [Name]='" & TextBox1.Text & "' ", MyConnection)
            ds = New System.Data.DataSet
            MyCommand.Fill(ds)
            dt = ds.Tables(0)
            DataGridView1.DataSource = dt


            MyConnection.Close()

            xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = xlWorkBook.Sheets("sheet1")

            For i = 0 To DataGridView1.RowCount - 2
                For j = 0 To DataGridView1.ColumnCount - 1
                    xlWorkSheet.Cells(i + 1, j + 1) = _
                        DataGridView1(j, i).Value.ToString()
                Next
            Next
            For j = 0 To DataGridView1.ColumnCount - 1

                xlWorkSheet.Cells(1, j + 1) = DataGridView1.Columns(j).Name

            Next

            For i = 0 To DataGridView1.RowCount - 1

                For j = 0 To DataGridView1.ColumnCount - 1

                    Dim cell As DataGridViewCell
                    cell = DataGridView1(j, i)
                    xlWorkSheet.Cells(i + 2, j + 1) = cell.Value
                Next
            Next

            xlWorkSheet.SaveAs("C:\Users\Space Era\Documents\" & filename)
            xlWorkBook.Close()
            xlApp.Workbooks.Close()
            xlApp.Quit()


I have this code for reading from one excel workbook,apply query and save the result to a new workbook.Now I am using list data validation in one of the columns of first workbook and the validation is not getting passed on to the new workbook,how do i fix this?

Is This A Good Question/Topic? 0
  • +

Page 1 of 1