excel 2007 query question
 

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
--


Gary Keramidas
Excel 2003

excel 2007 query question
 

Hi Gary,

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

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

excel 2007 query question
 

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

thanks

--


Gary Keramidas
Excel 2003


"Ed Ferrero" wrote in message
...
Hi Gary,

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

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

excel 2007 query question
 

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.


--


Gary Keramidas
Excel 2003


"Ed Ferrero" wrote in message
...
Hi Gary,

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

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

excel 2007 query question
 

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

excel 2007 query question
 

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.
--


Gary Keramidas
Excel 2003


"Ed Ferrero" wrote in message
...
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

excel 2007 query question
 

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


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.
--


Gary Keramidas
Excel 2003


"Ed Ferrero" wrote in message
...
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

excel 2007 query question
 

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

should work in both versions?

--


Gary Keramidas
Excel 2003


"Ed Ferrero" wrote in message
...
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


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.
--


Gary Keramidas
Excel 2003


"Ed Ferrero" wrote in message
...
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

excel 2007 query question
 

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

excel 2007 query question
 

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.

--


Gary Keramidas
Excel 2003


"Ed Ferrero" wrote in message
...
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