excel 2007 query question
in [General]
|
Prev: Formating cells - "Too Many Different Cell Formats" Error
Next: How do extract cells from hundreds of excel files and put in one n
From: Gary Keramidas on 18 Nov 2009 17:25 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" <ed(a)edferrero.com> wrote in message news:uo$HfYmQKHA.4004(a)TK2MSFTNGP04.phx.gbl... > 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 >
From: Ed Ferrero on 18 Nov 2009 20:26 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
From: Gary Keramidas on 18 Nov 2009 20:45 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" <ed(a)edferrero.com> wrote in message news:OiJoCeLaKHA.196(a)TK2MSFTNGP05.phx.gbl... > 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
From: Ed Ferrero on 18 Nov 2009 23:48 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" <ed(a)edferrero.com> wrote in message > news:OiJoCeLaKHA.196(a)TK2MSFTNGP05.phx.gbl... >> 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 >
From: Gary Keramidas on 19 Nov 2009 03:44
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" <ed(a)edferrero.com> wrote in message news:efchsONaKHA.2188(a)TK2MSFTNGP04.phx.gbl... > 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" <ed(a)edferrero.com> wrote in message >> news:OiJoCeLaKHA.196(a)TK2MSFTNGP05.phx.gbl... >>> 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 >> > |