Database Query macro works in Excel 2003 but fails in Excel 2007

Hi,

I am stumped on why my code works fine in XL2003 but failing in XL2007.

I created a workbook originally in XL2003. It has multiple sheets and I have a button that runs my macro, which essentially refreshes each query to our database on each sheet returning different information on each sheet, all this using one inputted "ProjectID" criteria and then it creates a final summary page with all essential data needed. At this point it works in both Excel versions.

Now I added a couple more sheets today using XL2007 with new queries and for some reason I am getting the error:

1004: Application defined or object-defined error

But, in if I run the macro in XL2003, it works just fine right to the end.

here is a portion of my code. I have 7 other queries before these... and it is in the 9th query (Sheets("WProject")) that the error occurs at.

ie: it stops at this part:

Please Login or Register  to view this content.

Any ideas?

Could it be because I created the new queries using XL2007, and the workbook is still a .xls file?

Please Login or Register  to view this content.

I'd use:

Please Login or Register  to view this content.

As far as I know only a sheet can contain a querytable, a Range/cell can't.

You are referring to the querytables' destination property:

Please Login or Register  to view this content.

Why would that be better? It's worked for me all along using my original method. What is the flaw using that method?

How do you know which Querytable number each table is?

I still get the same error with that suggestion, snb.

That's a pity..

Originally Posted by NBVC

How do you know which Querytable number each table is?

You can give a name to every querytable and refer to it by it's name, or you could loop through the collection and check it's destination property

Please Login or Register  to view this content.

Well, I did a little web searching and found that if I add ListObject it works:


so from:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

it works, but don't know why.

If the code passes

Please Login or Register  to view this content.

and fails at

Please Login or Register  to view this content.

It might be that the first QueryTable was created in 2003 and the second in 2007
When a Query table is created in 2007 it will put it in a ListObject.
So you might need something on these lines

Please Login or Register  to view this content.

[EDIT]
Okay you found it while I was typing!

Thanks Marcol..

I think you are right.

I tried adding the ListObject to the queries I made previously in 2003 and now they failed... so I have a mix of ones with and without the ListObject.... yuk!

Maybe something on these lines will help, can't test it

Please Login or Register  to view this content.

Thanks.. though that seems like a lot... and it has to be done for 10 queries... in this workbook

Okay, how about using a function on these lines. (Again I can't test it.)

Please Login or Register  to view this content.

Then in your code

Please Login or Register  to view this content.

I also noticed that sConn8 = sConn9 in your snippet.
If all the others are the same it might be easier, and safer, just to use one variable, then any change need only be done in one place.

Thanks again, Marcol. I look into trying this out.

Marcol,

Just wanted to thank you again for this fix (using the Function). It does work (for anyone having same problem). I have been using it satisfactorily.

Adding the function allows you to use the .querytable in both 2003 and 2007 versions.