upgraded from xl03 to xl07 - 2 frustrations with how it handles queries of other xl workbooks

[gobobbygo]

Recently upgraded from xl03 to xl07.
Run a project with 40-60 locations.
Each has reporting form with same format, different data.

Periodically need to create central reporting form that pulls data from same parts of each office's forms.

How I did it in xl03:
1. create query by hand.
2. Name the cell in the top left hand corner of the query to the name of the office
3. write macro to make (# of ofcs - 1) copies underneath the first one, naming the top-left cell of each query for the appropriate office
4. record/modify a second macro to change the source file to the appropriate office's forms

Problems I'm running into in xl07:
1. The actual copying step takes FOREVER (copying that in xl03 would take less than 10 seconds takes hours). I suspect this has to do with the formulas to the right of the query - xl07 seems to autocalculate those formulas even when calculation is set to manual, and with increasingly large amounts of data (eventually we get to 40k rows) the calculation takes larger and larger. I guess the simplest thing would be to turn that feature off, but I can't figure out how.
2. Can't seem to figure out in xl07 how to tell VBA "modify the query that the selected cell is part of, regardless of the name"

Sample of the code I use to change the queries that works in xl03:

For rownum = 1 To 40
ofccd = Range("ofclist!A" & rownum).Value
Range(ofccd & "data").Select
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=Excel Files;DBQ=C:\OfcAnalysis\FY09\Forms\" & ofccd & "FY09Data.xls;DefaultDir=C:\OfcAnalysis\FY09\Forms;DriverId=790;MaxBuff" _
), Array("erSize=2048;PageTimeout=5;"))
.CommandText = Array( _
"SELECT Data.Date, Data.Venue, Data.Town, Data.Gross" & Chr(13) & "" & Chr(10) & "FROM `C:\OfcAnalysis\FY09\Forms\\" & ofccd & "FY09Da" _
, _
"ta`.Data Data" & Chr(13) & "" & Chr(10) & "WHERE (Data.Date Is Not Null)" _
)
.Refresh BackgroundQuery:=False
End With
Next rownum

 

[gobobbygo]

So what do you do with your copy/paste then? Surely that would do exactly what you just described?

I name cell A1 to office1. I copy the query to line 42, and name cell A42 to office2. I copy the query to line 83 and name A43 to office3...

Then I go back and edit the queries. The second query now has 50 records instead of 40. Ten lines are automatically inserted, so the third query has been shifted down to line 93 and the named cell office3 is now A93.

 

[RoryA]

Why not simply add the query, offset by one (or more) from the Resultrange and then carry on? Not a big deal, I'm just curious!

 

[gobobbygo]

Why not simply add the query, offset by one (or more) from the Resultrange and then carry on? Not a big deal, I'm just curious!

Probably has to do with being an autodidact as far as this stuff is concerned - it's one more thing I'd have to learn how to do. I know how to add, and I know how to copy, so my method works. I'm sure there's an easy way to get the number of the last row in the query, but I've never needed it so I've never learned it.

 

[RoryA]

Oh, OK. It would be something along the lines of:

Dim qt as QueryTable, rngOut as range
Set rngOut = range("A1")
set qt = activesheet.querytables.add(Connection:=..., Destination:=rngOut)
' blah blah blah
qt.refresh backgroundquery:=false
 
set rngout = rngout.offset(qt.resultrange.rows.count)
' do your next query
set qt = activesheet.querytables.add(Connection:=..., Destination:=rngOut)
' and so on ad nauseam