edit query without name (or control name at creation) VBA 2007

[Bobby]

I need to create a bunch of similarly-structured queries, that query
different workbooks. In Excel 2003, I could create one, then copy it N times
and then go back and edit the copies so they referred to the workbooks that I
copied them to.

As long as the range "query1" was entirely withing the query that I wanted
to edit, a command that started with:

Range("query1").QueryTables.CommandText = ...

would work.

I'm having trouble doing the same thing in 2007; I can't seem to figure out
how to edit a query without the connection name, which Excel seems to be
naming sequentially.

I need to either edit the query without having to provide the name, or I
need to be able to name the connection at the time it's being created
(through VBA). When I record myself editing a query, it's always referring to
"Query From Excel Files###". Even when I create the query with a different
name for the connection, it records as creating a query named "Query From
Excel Files###" and then renaming it to the name I want.

How do I get around this, short of keeping track of exactly which queries
are created in which order?

 

[NOPIK]

I need to create a bunch of similarly-structured queries, that query
different workbooks. In Excel 2003, I could create one, then copy it N times
and then go back and edit the copies so they referred to the workbooks that I
copied them to.

As long as the range "query1" was entirely withing the query that I wanted
to edit, a command that started with:

    Range("query1").QueryTables.CommandText = ...

would work.

I'm having trouble doing the same thing in 2007; I can't seem to figure out
how to edit a query without the connection name, which Excel seems to be
naming sequentially.

I need to either edit the query without having to provide the name, or I
need to be able to name the connection at the time it's being created
(through VBA). When I record myself editing a query, it's always referring to
"Query From Excel Files###". Even when I create the query with a different
name for the connection, it records as creating a query named "Query From
Excel Files###" and then renaming it to the name I want.

How do I get around this, short of keeping track of exactly which queries
are created in which order?

Assign newly created query to object (or objects, if you need to
access all of them):
dim MyQuery as QueryTable;
dim rs,rs2 as RecordSet
set MyQuery=QueryTables.Add(rs, Range("A1"))
MyQuery.Refresh
'Do something
set MyQuery=QueryTables.Add(rs, Range("B2"))
MyQuery.Refresh
'Do something
set MyQuery=QueryTables.Add(rs2, Range("A1"))
MyQuery.Refresh
'Do something