1,105,399 Community Members

excel automation using vb6

Member Avatar
ponnu
Light Poster
40 posts since Sep 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

I am trying to compare one column of an excel sheet with the database table.If the value in the column matches with the data in the table,the excel has to be populated with another value in the table in a new column.If match is not found the value in the new column should be kept as "X".

Dim i As Integer
i = 1
datafile = "database.accdb"
With cn
    .Provider = "microsoft.ACE.OLEDB.12.0"
    .ConnectionString = datafile
End With
cn.Open

Dim oXL As Object
Set oXL = CreateObject("Excel.Application")
Dim oBook As Object

Set oBook = oXL.Workbooks
Dim oSheet As Object
'Set oSheet = oXL.Worksheets(1)
Dim vValue As Variant    

Set oBook = oXL.Workbooks.Open("C:\s.xlsx")
Set oSheet = oBook.Worksheets("Sheet1")

Do Until (i >= 5)

    vValue = oSheet.Cells(i, 5).Value
    MsgBox vValue

    Set rs = cn.Execute("select * from table where ID = vValue ")

    If (rs.EOF = False) Then
        oSheet.Cells(i, 5).Value = rs!field
    Else
        oSheet.Cells(i, 5).Value = "X"
    End If

    rs.Close        

    i = i + 1

Loop
Member Avatar
Reverend Jim
Noli mentula
5,450 posts since Aug 2010
Reputation Points: 746 [?]
Q&As Helped to Solve: 653 [?]
Skill Endorsements: 51 [?]
Moderator
Featured
 
0
 

And what is your problem?

By the way, please use the Code tool to insert code otherwise you lose all of the formatting.

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article