Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Query an Excel Database
- Thread starter robert693
- Start date
JVFriederick
IS-IT--Management
An alternative, keeping it within Excel, is to type headings on the first row, then add data below. Don't skip columns or rows. Very simple.
Use the Data Filter to query the data, which "hides" records that do not match. This is often all that is needed to analyse data.
As a tip, place a formula like =SUBTOTAL(9,B10:B1000) above your heading row. Then as the filter is turned on and queried, you will see totals for only the selected data.
Use the Data Filter to query the data, which "hides" records that do not match. This is often all that is needed to analyse data.
As a tip, place a formula like =SUBTOTAL(9,B10:B1000) above your heading row. Then as the filter is turned on and queried, you will see totals for only the selected data.
DaleWatson123321
Programmer
Hi Robert,
If the Data-Filter option is not appropriate, here is a brief example of the code required for extracting data from an Excel database:
Range("database"
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:="criteria", _
CopyToRange:=Range("ext_out", _
'Note:"ext_out" is an 'output' range on a separate sheet
Unique:=False
A couple of notes:
1) The names in quotes (e.g. "database" are arbitrary "Range Names" which you can freely assign - by using Control <F3> and specifying the range.
2) The output range can be on any sheet, providing that the range name is UNIQUE to the workbook. Excel allows for more than one instance of the same range name. (Excel should NOT allow for more than one identical name, but it does.) The first identical range name created (of two or more) by default becomes the "global" workbook range name, whereas any additional identical range names are only "sheet" names
If the Data-Filter option is not appropriate, here is a brief example of the code required for extracting data from an Excel database:
Range("database"
.AdvancedFilter Action:=xlFilterCopy, _CriteriaRange:="criteria", _
CopyToRange:=Range("ext_out"
, _ 'Note:"ext_out" is an 'output' range on a separate sheet
Unique:=False
A couple of notes:
1) The names in quotes (e.g. "database"
are arbitrary "Range Names" which you can freely assign - by using Control <F3> and specifying the range.2) The output range can be on any sheet, providing that the range name is UNIQUE to the workbook. Excel allows for more than one instance of the same range name. (Excel should NOT allow for more than one identical name, but it does.) The first identical range name created (of two or more) by default becomes the "global" workbook range name, whereas any additional identical range names are only "sheet" names
- Status