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]

bump

 

[gobobbygo]

bump again

 

[RoryA]

As a matter of interest, why copy a querytable that's wrong, then correct it, rather than simply creating the right querytable in the first place?
For the calculation, you might try setting the worksheet's Enablecalculation property to False and then back to True at the end.

 

[gobobbygo]

As a matter of interest, why copy a querytable that's wrong, then correct it, rather than simply creating the right querytable in the first place?

Mainly, so I'll know where to put the next query after it. If I'm making queries that have a fixed number of records, I'll just create the right querytable in the first place. But if not, I'll copy the original one first so as to make sure I know where everything goes.

For the calculation, you might try setting the worksheet's Enablecalculation property to False and then back to True at the end.

Thanks, will try.

 

[RoryA]

I don't understand your first point. You would know where to put the new querytable in the same way you know to where you should copy the existing one.

 

[gobobbygo]

I don't understand your first point. You would know where to put the new querytable in the same way you know to where you should copy the existing one.

The query for the first office might have 50 records, the query for the second might have 40, etc.

If I make copies of the first query, I know that the second one starts on line 52, the third starts on line 103, the fourth starts at line 154, etc.

If each query has a different number of records, I won't know where to put the next one until I count them.

 

[RoryA]

I still don't see how it makes any difference. If you copy and paste, then you copy and paste to a specific location. By the same token, you could simply create the new querytable at that same location without copying the old one.

Edit: Actually, I do sort of see how it is different, I just don't get the point! You can just offset from the ResultRange.

 

[gobobbygo]

I still don't see how it makes any difference. If you copy and paste, then you copy and paste to a specific location. By the same token, you could simply create the new querytable at that same location without copying the old one.

Let's say the first query has 40 records and the second has 50.
So I create the second query at line 42. I create the third query at line 83. Which is right smack in the middle of the second query.

 

[RoryA]

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