Solved

Visual basic and Excel

Posted on 2003-11-05
10
748 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
  • 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now