How could I appreciate you Rahul?

KP

Thanks Rahul.

Your code really helped me.

Anita

Hey Rahul,

Thanks for this code buddy.

But I have an issue here - I want to save contents of a RecordSet into an excel file. I want to display a File Open / Save As dialog box, the user will then select existing / specify name for new excel file. Once this is done, I want to rename the "Sheet1" to "Search Results" and then copy each record from the recordset into the excel sheet.

Can you please help? I am using VB6 and ADODB.

i want to read excel cell comments and write them into database

Hi Sagar Just check following code,

excel file is opened as a adodb connection, then u can manipulate as u want

Dim cn As ADODB.Connection

Dim rsT As New ADODB.Recordset

Dim tblList As ADODB.Recordset

Dim I As Integer

Private Sub cmdImport_Click()

   List1.Clear

   rsT.Open "select * from  [" & cmbSheetName.Text & "]", cn, adOpenDynamic, adLockReadOnly

   Do While Not rsT.EOF

   If IsNull(rsT.Fields(5)) = False Then

      List1.AddItem rsT.Fields(5) & ""

   End If

   rsT.MoveNext

   Loop

   rsT.Close

End Sub

Private Sub cmdOpenFile_Click()

   cDiag.Filter = "*.xls"

   cDiag.Action = 1

   txtFileName.Text = cDiag.FileName

   With cn

       .Provider = "Microsoft.Jet.OLEDB.4.0"

       .ConnectionString = "Data Source=" & txtFileName.Text & _

        ";Extended Properties=Excel 8.0;"

       .CursorLocation = adUseClient

       .Open

   End With

   Set tblList = cn.OpenSchema(adSchemaTables)

   For I = 1 To tblList.RecordCount

       cmbSheetName.AddItem tblList.Fields("TABLE_NAME").Value & ""

       tblList.MoveNext

   Next I

   cmbSheetName.ListIndex = 0

End Sub

Private Sub Form_Load()

   Set cn = New ADODB.Connection

End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)

'    cn.Close

End Sub

Thanks, This was helpful

Thanks much for the Sample Rahul, it helped save some time.

Take care.

Hi,

my requirement is to populate data into excel sheet using vb6. i tried in this way,

Dim oXL As Excel.Application

Dim oWBK As Excel.Workbook

Dim oWS As Excel.Worksheet

Set oXL = New Excel.Application

Set oWBK = New Excel.Workbooks

Set oWS = New Excel.Worksheet

oWBK = oXL.Workbooks.Open("D:\Copy of July_Jobtimigs_Production_New.xls")

oWS.Cells(5, 5) = "File Validation"

but, i am getting error as 'Activex component can't create object' for 'Set oWBK = New Excel.Workbooks'

could you help me in this?

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

   Private Sub Button1_Click(ByVal sender As System.Object, _

 ByVal e As System.EventArgs) Handles Button1.Click

       Dim xlApp As Excel.Application

       Dim xlWorkBook As Excel.Workbook

       Dim xlWorkSheet As Excel.Worksheet

       Dim range As Excel.Range

       Dim rCnt As Integer

       Dim cCnt As Integer

       Dim Obj As Object

       xlApp = New Excel.ApplicationClass

       xlWorkBook = xlApp.Workbooks.Open("c:\vbexcel.xlsx")

       xlWorkSheet = xlWorkBook.Worksheets("sheet1")

       range = xlWorkSheet.UsedRange

       For rCnt = 1 To range.Rows.Count

           For cCnt = 1 To range.Columns.Count

               Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)

               MsgBox(Obj.value)

           Next

       Next

       xlWorkBook.Close()

       xlApp.Quit()

       releaseObject(xlApp)

       releaseObject(xlWorkBook)

       releaseObject(xlWorkSheet)

   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

Thanks Rahul

That helped a ton

hi,

my requirement:

populate a vb combobox with values from one single excel column(say column D of an excel file stored in My Documents folder). Also, this column has some values that repeat themselves. I need to filter these and only populate distinct values in the combobox.

i have been able to open the excel worksheet via vb. but, how do i pass the values from this columb onto the combobox on the form?

help is greatly appreicated.

i am using vb6.0 and excel 2003 - this helps.

I'm reading an excel file to compare to the current Access DB records. If there is a change I write the change - so much for that.

It seems like it takes FOREVER to read thru each of the cells and do whatever. Is there a FASTER way to get this done?