Kirk wrote:
[Quoted Text] > I have created a MS Query that returns a single record's information
> from an Access Database using a parameter. This parameter comes from
> a cell in the spreadsheet and is used in the query.
>
> For example, cell A1 contains a part number "1234". When I refresh
> the query, it returns information from the Access db where the part
> number = "1234". This works well for my single cell/single query.
>
> However, I have about 200 lines where I would like this same query to
> be run, and the parameter value needs to be relative. In other words,
> I need the query on the next line to read from cell A2 to get the
> parameter value. I can manually create the query for each cell, but I
> was wondering if there was a way to copy the query to multiple cells
> (and use a relative reference for the parameter I need)?
>
> I have saved the query to a [*.DQY] file, but I still would have to
> manually insert/use this query on all 200 rows to accomplish what I
> need.
>
> I would greatly appreciate any suggestions anyone might have. I have
> experience in VB/VBA, but I don't know how to use queries within code.
>
Kirk:
I rarely use single cell external data queries anymore. I find ADO in a UDF
to be more to my liking. You may too. Check out this post:
http://www.dicks-blog.com/archives/2005/01/31/using-ado-in-functions/
--
Dick Kusleika
MVP-Excel
www.dailydoseofexcel.com
|