Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grab a key and use it to query another worksheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grab a key and use it to query another worksheet
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. -- Regards, Tom Ogilvy "Don N." wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grab a key and use it to query another worksheet
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--------------- -- "Don N." wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
another macro query - deleting a worksheet within a query | Excel Discussion (Misc queries) | |||
number grab | New Users to Excel | |||
grab detail when = 1 | Excel Worksheet Functions | |||
First Letters Grab | Excel Discussion (Misc queries) | |||
Grab Password Name | Excel Programming |