Solved

Visual basic and Excel

Posted on 2003-11-05
10
759 Views
Last Modified: 2013-11-25
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
Comment
Question by:chaiti_c
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
10 Comments
 
LVL 5

Expert Comment

by:fantasy1001
ID: 9692756
should be something like this
cmd = select * from  [excel2$B:B]

~ fantasy ~
0
 
LVL 24

Expert Comment

by:R_Rajesh
ID: 9692797
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
 
LVL 24

Expert Comment

by:R_Rajesh
ID: 9692807
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:chaiti_c
ID: 9694225
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
 
LVL 24

Expert Comment

by:R_Rajesh
ID: 9694405
0
 
LVL 24

Accepted Solution

by:
R_Rajesh earned 500 total points
ID: 9694517
i suggest you use named range, and use something like this
"SELECT * [Sheet2$] WHERE ColG='" & variable "'" as mentioned in the article.

0
 

Author Comment

by:chaiti_c
ID: 9698980
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
 
LVL 24

Expert Comment

by:R_Rajesh
ID: 9699137
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
 
LVL 24

Expert Comment

by:R_Rajesh
ID: 9699148
thanks for the grade :)
0
 

Author Comment

by:chaiti_c
ID: 9712886
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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question