• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 767
  • Last Modified:

Visual basic and Excel

I have to create Excel Sheets using Visual Basic 6 based on two Excel files given. I have to compare 2 columns in the given Excel files, that is the col B in Excel 1 with Col G in Excel 2-

The Excel files are quite large I am using ADODB and getting the data from the first Excel file.
I am using 2 connections to acquire data from both the files but am unable to use the where clause in the cmd statemnet ie
cmd = select * from excel2 where excel2.col2='abc'

Is my approach efficient?
How do I define the column excel2.col2  to execute this statemnet?
regards
 
0
chaiti_c
Asked:
chaiti_c
  • 6
  • 3
1 Solution
 
fantasy1001Commented:
should be something like this
cmd = select * from  [excel2$B:B]

~ fantasy ~
0
 
R_RajeshCommented:
try this,

this code opens test1.xls and test2.xls and stores columnB of test1 in Table1 of the database and columnG of test2 in Table2.
create a new database in c:\temp name it Data.mdb, Create two tables in it "Table1" & "Table2" (watch the case) with a field called "field1" in both of them.
make sure you reference these before you try the code.
Microsoft ActiveX Data Objects Library
Microsoft ADO Ext. 2.5 for DDL and Security
Microsoft excel 10 object library

--------------
Private Sub Form_Load()
Dim xlApp As Excel.Application
Dim xlBook1 As Excel.Workbook
Dim xlSht As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset, rst1 As New ADODB.Recordset
Dim looprange As Range
Dim currcell As Range
With conn
.Provider = "Microsoft.JET.OLEDB.4.0"
.Open "C:\temp\Data.mdb"
End With
cat.ActiveConnection = conn
With rst
.ActiveConnection = conn
.Open "Table1", LockType:=adLockOptimistic
End With
Set xlBook1 = xlApp.Workbooks.Open("C:\Temp\Test1.xls")
Set xlSht = xlBook1.Sheets(1)
lCell = xlSht.Range("B65535").End(xlUp).Row
Set looprange = xlSht.Range(xlSht.Range("b2"), xlSht.cells(lCell, 2))
For Each currcell In looprange
With rst
.AddNew
.Fields("field1").Value = currcell.Value
.Update
End With
Next currcell
'add second sheet
With rst1
.ActiveConnection = conn
.Open "Table2", LockType:=adLockOptimistic
End With
Set xlBook1 = xlApp.Workbooks.Open("C:\Temp\Test2.xls")
Set xlSht = xlBook1.Sheets(1)
lCell = xlSht.Range("G65535").End(xlUp).Row
Set looprange = xlSht.Range(xlSht.Range("G2"), xlSht.cells(lCell, 7))
For Each currcell In looprange
With rst1
.AddNew
.Fields("field1").Value = currcell.Value
.Update
End With
Next currcell
rst1.Close
rst.Close
Set cat = Nothing
conn.Close
Set xlBook1 = Nothing
Set xlApp = Nothing
End
End Sub
---------------------------
0
 
R_RajeshCommented:
or you can do away with access all togther,

this code compares columnB of test1.xls to columnG of test2.xls begining from row2 and puts the matches in a new workbook with the row number and the data contained in the matching rows.

make sure you make a reference to microsoft excel object library using the reference option form project menu.

----------------
Private Sub Form_Load()
Dim xlApp As Excel.Application
Dim xlBook1 As Excel.Workbook, xlBook2 As Excel.Workbook, _
xlBook3 As Excel.Workbook
Dim xlSht1 As Excel.Worksheet, xlSht2 As Excel.Worksheet, _
xlSht3 As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook1 = xlApp.Workbooks.Add
Set xlBook2 = xlApp.Workbooks.Open("C:\Temp\Test1.xls")
Set xlBook3 = xlApp.Workbooks.Open("C:\Temp\Test2.xls")
Set xlSht1 = xlBook1.Worksheets(1)
Set xlSht2 = xlBook2.Worksheets(1)
Set xlSht3 = xlBook3.Worksheets(1)
r1 = xlSht2.UsedRange.Rows.Count
r2 = xlSht3.UsedRange.Rows.Count
r3 = IIf(r1 > r2, r1, r2)
With xlSht1
.Cells(1, 1).Value = "Row No"
.Cells(1, 2).Value = xlBook2.Name
.Cells(1, 3).Value = xlBook3.Name
.Range("A1:C1").Font.Bold = True
End With
For Counter = 2 To r3
If xlSht2.Cells(Counter, 2).Value = xlSht3.Cells(Counter, 7).Value Then
curcell = xlSht1.Range("A65535").End(xlUp).Row + 1
xlSht1.Cells(curcell, 1).Value = Counter
xlSht1.Cells(curcell, 2).Value = xlSht2.Cells(Counter, 2).Value
xlSht1.Cells(curcell, 3).Value = xlSht3.Cells(Counter, 7).Value
End If
Next Counter
xlBook2.Save: xlBook3.Save
xlBook2.Close: xlBook3.Close
Set xlSht1 = Nothing: Set xlSht2 = Nothing: Set xlSht3 = Nothing
Set xlBook2 = Nothing: Set xlBook3 = Nothing: Set xlBook1 = Nothing
Set xlApp = Nothing
End
End Sub
------------------
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
chaiti_cAuthor Commented:
Actually the value in the first worsheet does not exist in any row of the second worksheet.
For this I am using DSN connection. I am selecting from the first work sheet-

rs1.Open "select * from sheet1", , adOpenStatic, adLockBatchOptimistic
do while not rs1.eof

rs2.open "select * from sheet2 where............................ "

rs1.movenext
loop

Now the problem is I am unable to pass the value of the column in rs2. ie I have to check the values in column G
Now how do I write
column G="abcd" in the SQL statement
Please help
Regards
0
 
R_RajeshCommented:
i suggest you use named range, and use something like this
"SELECT * [Sheet2$] WHERE ColG='" & variable "'" as mentioned in the article.

0
 
chaiti_cAuthor Commented:
Thanks for all the help to everyone concerned. Can I sort the Excel sheet on a particular column by executing my Visual Basic program?
0
 
R_RajeshCommented:
dont you think this question belongs in a new thread :)

just kidding, sorts sheet1 in decending order by columnC
-------------
Private Sub Form_Load()
Dim xlApp As Excel.Application
Dim xlBook1 As Excel.Workbook
Dim xlSht1 As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook1 = xlApp.Workbooks.Open("C:\Temp\Test1.xls")
Set xlSht1 = xlBook1.Worksheets(1)
xlSht1.Cells.Select
xlSht1.Cells.Sort Key1:=Range("C1"), Order1:=xlDescending
xlBook1.Save
'uncomment next line to close the workbook
'xlBook1.Close
Set xlSht1 = Nothing
Set xlBook1 = Nothing
Set xlApp = Nothing
End
End Sub
-----------------
0
 
R_RajeshCommented:
thanks for the grade :)
0
 
chaiti_cAuthor Commented:
Thanks for the aid. Yes it definitely was not mentioned in the original query and should have been on a new thread. Thanks once again for all the help. The application is through but the credit goes to you. Regards
Ena
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now