Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Visual basic and Excel

Posted on 2003-11-05
10
Medium Priority
?
765 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 2000 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

618 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