Grab a key and use it to query another worksheet

[Don N.]

I use a workbook with several worksheets. This workbook is
used by many diferent departments so I cannot modify it.
The first worksheet (Summary)is populated with information
such as store number, store opening date, store owner,
etc. There is another worksheet (Addresses) that has all
of the address information. Thanks to Chip Pearson, I have
the code that will query the Summary worksheet and give me
a list of all stores opening between two dates. Now I need
to query the Addresses worksheet in order to get the
address information. There is a common "key" (store
number) in all of the worksheets. Is there a way I can
grab this "key" and use it to query the address worksheet
so I can grab the address information?

Any and all help is greatly appreciated

 

[Tom Ogilvy]

Turn on the macro recorder, select the address sheet and the Store Number
column. Do Edit=>Find and enter the store number as the target. Click OK.
Turn off the macro recorder.

This will give you the code you need to find the store ID on the address
worksheet. Generalize the "What" argument to accept the store ID as a
variable value.

 

[onedaywhen]

You say, 'query', 'key', 'a list...between two dates' - sounds like
database processes to me. If you are familiar with querying a database
using SQL, you should be aware that you can use ADO to query a
(closed) Excel workbook (perhaps this is what Chip suggested?) You can
use the key column to create joins between worksheets.

Here's a bit of example code which joins worksheets 'Summary' and
'Addresses' using the key column 'store number', filters between two
dates: and pastes the selected columns into a new sheet in the current
workbook:

'<code>---------------
Sub test()

Dim oCon As Object
Dim oRS As Object
Dim oWS As Excel.Worksheet
Dim strSql As String

Set oCon = CreateObject("ADODB.Connection")

oCon.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MyWorkbook.xls;" & _
"Extended Properties=Excel 8.0"

strSql = "SELECT T1.[store owner], T2.[address]" & _
" FROM [Summary$] T1 " & _
" INNER JOIN [Addresses$] T2" & _
" ON T1.[store number]=T2.[store number]" & _
" WHERE T1.[store opening date] BETWEEN" & _
" #01 JAN 2003# AND #01 APR 2003#"

Set oRS = oCon.Execute(strSql)

Set oWS = ThisWorkbook.Worksheets.Add()

oWS.Range("A1").CopyFromRecordset oRS

oRS.Close
oCon.Close

End Sub
'</code>---------------