excel 2007 query question

[Gary Keramidas]

don't really have any way to test this locally, since i don't have an as400.
this works in excel 2003, but in excel 2007, the following line debugs.

ws.Range("A6").QueryTable.Refresh BackgroundQuery:=False

is there a different syntax in 2007

thanks

 

[Ed Ferrero]

Hi Gary,

Try this,
ws.Range("A6").ListObject.QueryTable.Refresh BackgroundQuery:=False

 

[Gary Keramidas]

i'll have them give it a try tomorrow and report back.

thanks

 

[Gary Keramidas]

ok, ed. i finally have an update. if this goes unseen since it's so old, i'll
repost.
pertinent code:
in 2003:
a query refresh dialog appears when the workbook is opened, enable or disable
automatic refresh.
If Application.Version <= 11 Then
ws.Range("A6").QueryTable.Refresh
BackgroundQuery:=False
ElseIf Application.Version > 11 Then
ws.Range("A6").ListObject.QueryTable.Refresh
BackgroundQuery:=False
End If

file saved as an excel 2003 file running under excel 2007:
get a dialog: file error: data may be lost. no query refresh dialog.
debug error on this line
ws.Range("A6").ListObject.QueryTable.Refresh BackgroundQuery:=False

saved as an xlsm file running under 2007:

same as previous debug error, except when the user clicked debug, then closed
the vba editor, the query ran.

so, i'm not sure what's going on. also, is there a way, other than editing the
registry, to stop the refresh query dialog from popping up when the workbook is
opened?

any help is appreciated.

 

[Ed Ferrero]

Hi Gary,

Ok, I see what you are trying to do.

The problem is that, if you create a data query in XL 2003, the data is held
in a Query Table.
If the query is created in XL 2007, the data is held in a List Object that
then holds a Query Table.

So, use this code - works in both versions.

Dim ws As Worksheet
Dim rng As Range

Set ws = Worksheets(1)
Set rng = ws.Range("A6")

If rng.ListObject Is Nothing Then
' this was created in Xl 2003, so refresh the qt
rng.QueryTable.Refresh BackgroundQuery:=False
Else
' created in XL 2007, get the qt in a list Object
rng.ListObject.QueryTable.Refresh BackgroundQuery:=False
End If

Ed Ferrero
www.edferrero.com

 

[Gary Keramidas]

thanks ed, but still not working. the query is created in excel 2003, but needs
to run in 2007 and 2003. i believe with your code it would still try to run the
xl2003 code even though the workbook is running in excel 2007.

in the code i posted, it executes the correct statement, it just debugs in 2007.

 

[Ed Ferrero]

Hi Gary,

Well, maybe I don't understand what you want to do.

I created a query in Excel 2003.
Then ran the code in Excel 2003 - works.
Then opened the file in Excel 2007, ran the code - works.

Ed Ferrero
www.edferrero.com

 

[Gary Keramidas]

i just want the query to run in both versions. are you saying that
rng.QueryTable.Refresh BackgroundQuery:=False

should work in both versions?

 

[Ed Ferrero]

Hi Gary,

i just want the query to run in both versions. are you saying that
rng.QueryTable.Refresh BackgroundQuery:=False

should work in both versions?

Yes, try it.

The difference in versions is that creating a data query in 2007 will
automatically put it in a ListObject.
So you could have both ListObject and QueryTable objects in one workbook -
some created in 2003, some created in 2007. The code will handle both types.

Ed Ferrero
www.edferrero.com

 

[Gary Keramidas]

ok, i'll have them try it. i thought they had an issue with that query refresh
in 2007, that's why i was looking to change it.

the problem is, i don't have the client's as400 or data to run the query myself,
so it takes a while to get it tested,

thanks.