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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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\d b1.mdb")
Set rs = db.OpenRecordset("tabel1", dbOpenDynaset)
Set xlSource = Excel.Workbooks.Open("d:\d ocuments and settings\lanlord\desktop\m ap1.xls")
Set xlDestination = Excel.Workbooks.Add
thefile = "d:\documents and settings\lanlord\desktop\t esting.xls "
xlDestination.SaveAs thefile
n = 1
Do While xlSource.Worksheets(1).Cel ls(n, 1) <> ""
rs.FindFirst "email = '" & xlSource.Worksheets(1).Cel ls(n, 1) & "'"
xlDestination.Worksheets(1 ).Cells(n, 1) = xlSource.Worksheets(1).Cel ls(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
Sub Command1_Click()
Dim xlSource As Excel.Workbook, xlDestination As Excel.Workbook, rng As Range, n, thefile
Set db = OpenDatabase("d:\documents
Set rs = db.OpenRecordset("tabel1",
Set xlSource = Excel.Workbooks.Open("d:\d
Set xlDestination = Excel.Workbooks.Add
thefile = "d:\documents and settings\lanlord\desktop\t
xlDestination.SaveAs thefile
n = 1
Do While xlSource.Worksheets(1).Cel
rs.FindFirst "email = '" & xlSource.Worksheets(1).Cel
xlDestination.Worksheets(1
If rs.NoMatch = True Then
xlDestination.Worksheets(1
Else
xlDestination.Worksheets(1
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
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. :-)
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 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
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
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
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
=IF(IsNA(MATCH(A1,Sheet2!A
This will give you the true/false you are looking for.
Note: the IsNA returns a boolean value (true or false)