If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|