Link to home
Start Free TrialLog in
Avatar of silviaaa
silviaaa

asked on

visual basic excel access

i have this: in access i hava a table that contains emails:
   1 aaa@aaa.com
   2 bbb@bb.com
   3 ccc@ccc.com and so...

well in excel i have something like that..
1 aaa@aaa.com
2 bbb@bb.com
3 ddd@ddd.com

so i want to compare the access with the excel and make a report in excel like this
1 aaa@aaa.com  already exist
2 bbb@bb.com   already exist
3 ddd@ddd.com  new

so i want to take each cell of excel and compare it with the table in access, so if it is the same, i want to make another excel with the results that already exist, and if it doesn't match with anyone, put the result that is new.
ASKER CERTIFIED SOLUTION
Avatar of WintersVine
WintersVine

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of WintersVine
WintersVine

you can test to see if the match funtion returns an error like this:
=IF(IsNA(MATCH(A1,Sheet2!A:A,0)),"New","Exists")

This will give you the true/false you are looking for.

Note: the IsNA returns a boolean value (true or false)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
oh yeah, I forgot to mention: Add a reference to the Excel object library and one to the DAO library in the project.
Oops, I misread your original post. The posted code tests if the addresses in the MDB are new in the XLS or not, and you asked for a test the other way round. Replace the command1_click code with the following.

Sub Command1_Click()
Dim xlSource As Excel.Workbook, xlDestination As Excel.Workbook, rng As Range, n, thefile
Set db = OpenDatabase("d:\documents and settings\lanlord\desktop\db1.mdb")
Set rs = db.OpenRecordset("tabel1", dbOpenDynaset)
Set xlSource = Excel.Workbooks.Open("d:\documents and settings\lanlord\desktop\map1.xls")
Set xlDestination = Excel.Workbooks.Add
thefile = "d:\documents and settings\lanlord\desktop\testing.xls"
xlDestination.SaveAs thefile
n = 1
Do While xlSource.Worksheets(1).Cells(n, 1) <> ""
   rs.FindFirst "email = '" & xlSource.Worksheets(1).Cells(n, 1) & "'"
   xlDestination.Worksheets(1).Cells(n, 1) = xlSource.Worksheets(1).Cells(n, 1)
   If rs.NoMatch = True Then
      xlDestination.Worksheets(1).Cells(n, 2) = "new"
   Else
      xlDestination.Worksheets(1).Cells(n, 2) = "already exists"
   End If
   n = n + 1
Loop
xlSource.Close
xlDestination.Save
xlDestination.Close
Set xlSource = Nothing
Set xlDestination = Nothing
End

End Sub
Hydberg's is a "Macro" solution that works when you click a button.

my solution is a "spreadsheet" solution, meaning Excel will keep it updated without clicking a button.

Both are good solutions that will work.
Its just a matter of how you want to use the spreadsheet
Well, actually mine isn't a macro solution, I wrote it in the IDE of Visual Basic 5.0. Only for the sake of ease of testing I attached it to a command button, but you could also create a project that has no forms, just code. That way you could turn it into a stand alone executable program that you can run anytime you want to.

Also, my solution doesn't generate a #N/A, it just enters "doesn't exist" in the cell

It's a matter of taste I guess, which solution you prefer. I'll happily admit that I'm biassed to mine, since it was ME that wrote it. :-)
Hi silviaaa,
This old question (QID 20559060) needs to be finalized -- accept an answer, split points, or get a refund.  Please see http://www.cityofangels.com/Experts/Closing.htm for information and options.
This question has been classified as abandoned.  I will make a recommendation to the moderators on its resolution in a week or two.  I would appreciate any comments by the experts that would help me in making a recommendation.
It is assumed that any participant not responding to this request is no longer interested in its final deposition.

If the asker does not know how to close the question, the options are here:
https://www.experts-exchange.com/help/closing.jsp

GPrentice00
Cleanup Volunteer
What recommendation will you be making? I'm Dutch and perhaps there's some linguistic nuance I'm missing in your comment, but I don't know what kind of comments you're looking for.

Henk
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

 -->Split between WintersVine and ignacioperez and hvdberg

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER

GPrentice00
Cleanup Volunteer