In Excel2002-To find&copy specified "Statements" via Access2002 to

[bjsaw]

In Excel 2002, the specified “Text†to be find, within Specified
“Worksheetâ€{from Original File} and to be Search by specified “Column†and to
be look in “Values†{not “Formulas†or “Commentsâ€})
And paste the results in new excel file

Example: "Don" or "Annik" to be find within specified following "Worksheet"
and to be search in Column "1st Name" , also the "Pay period" should be
either "Jan 1. 2005" or/and "Jan 15. 2005"

2ndName 1st Name Location Dept. Pay Period
Barnhill Josh 1st floor Sales Jan 1. 2005
Barnhill Josh 1st floor Sales Jan 15. 2005
Funk Don 1st floor Sales Jan 1. 2005
Funk Don 1st floor Sales Jan 15. 2005
Funk Don 1st floor Sales Feb 1. 2005
Stahl Annik 1st floor Purchase Jan 1. 2005
Stahl Annik 1st floor Purchase Jan 15. 2005
Stahl Annik 1st floor Purchase Feb 1. 2005

and the paste result into to another excel sheet, example as below:

2ndName 1st Name Location Dept. Pay Period
Funk Don 1st floor Sales Jan 1. 2005
Funk Don 1st floor Sales Jan 15. 2005
Stahl Annik 1st floor Purchase Jan 1. 2005
Stahl Annik 1st floor Purchase Jan 15. 2005


-------------------------------------------------------------------------------------------

Excel file is very huge that is more than 50000 lines, Thus the Data/Filter
is not working also VLOOKUP function is not working properly. Thus need a
program in Access 2002 for following:
1) Require 2 Tables in Access 2002:
One for "1st Name" will keep value such as "Don", "Annik" in same,
Second for "Pay period" will keep value such as "Jan 1. 2005","Jan 15.
2005"
in same.
2) Read the similar Excel file/Sheet(as shown above) in Access 2002
3) Access should check "Don" and/or "Annik" value in "1st Name", and if
found then
3a) Access should check whether for "Don" the "Pay period" is either "Jan 1.
2005" or "Jan 15. 2005"
3b) Access should further also check whether for "Annik" the "Pay period" is
either "Jan 1. 2005" or "Jan 15. 2005"
and if the table values of "1st Name" and "Pay period" is matching, then the
output result to be copied into new excel file/sheet as below:

2ndName 1st Name Location Dept. Pay Period
Funk Don 1st floor Sales Jan 1. 2005
Funk Don 1st floor Sales Jan 15. 2005
Stahl Annik 1st floor Purchase Jan 1. 2005
Stahl Annik 1st floor Purchase Jan 15. 2005

Kindly advise on how to above programming in Access 2002 for Excel 2002 file.

Thanks

 

[Ken Snell \(MVP\)]

Why not just do all of it in EXCEL, using EXCEL VBA? You want the result to
be in an EXCEL spreadsheet, and I don't see any value to using ACCESS as an
intermediary program here.

Post your question in EXCEL newsgroup, and I'm sure you will get suggestions
on how to use VBA in EXCEL to do what you seek.