Edit SQL-Query String in Excel Dagmar 3/16/2007 8:51:08 AM Hi, this is a follow up of the original question "View SQL Query-String in Excel" which I postet here. Now, that I can read the SQL-Query I would like to change it. e.g. the original query is like this: SELECT Datum=L.Beginn, Mitarbeiter=M.Name,Klient=K.Suchbegrif + ' (' + RTRIM(K.Kundennummer) + ')', Auftrag=A.Bezeichnung, L.Stunden, Expensargruppe=L.HGruppencode, L.Periode,[Verkaufspreis]=L.Wert0, Aliquotierung=Aliquot, Honorar=(isnull(L.Wert0,0)+Aliquot), Bemerkung=L.Beschreibung, Referat=M.Abteilungsnummer, Abteilung=M.DTA_Code, Klientenkanzlei=K.Kanzleinummer , Mitarbeiterkanzlei=M.Kanzleinummer, LC=L.Artikelnummer FROM LeistLA0 L (NOLOCK)INNER JOIN Klientenstamm K (NOLOCK) ON L.Kundennummer=K.Kundennummer INNER JOIN Mitarbeiter M (NOLOCK) ON L.MAID=M.MAID LEFT JOIN Auftrag A (NOLOCK) ON L.AuftragsID=A.AuftragsID WHERE (L.Beginn Between '1.1.2006' And '31.12.2006') It would be nice to change the L.Beginn Date in the end. However, I copied the SQL-Query String above and added the "?ActiveSheet.PivotTables(1).PivotCache.CommandText" in the front. Unfortunately this does not work ("Syntax error ..."). I also tried less complex SQL-Queries which do work. Are there any better/more comfortable ways to change this query? Thank you for your help! Dagmar

Re: Edit SQL-Query String in Excel Dick Kusleika <dkusleika[ at ]gmail.com> 3/20/2007 10:11:53 PM On Fri, 16 Mar 2007 01:51:08 -0700, Dagmar <Dagmar[ at ]discussions.microsoft.com> wrote: [Quoted Text] > >Now, that I can read the SQL-Query I would like to change it. >e.g. the original query is like this: <snip> >WHERE (L.Beginn Between '1.1.2006' And '31.12.2006') > >It would be nice to change the L.Beginn Date in the end. However, I copied >the SQL-Query String above and added the >"?ActiveSheet.PivotTables(1).PivotCache.CommandText" in the front. >Unfortunately this does not work ("Syntax error ..."). I also tried less >complex SQL-Queries which do work. > >Are there any better/more comfortable ways to change this query? > Nope. There are no good ways. There's an extra space, or maybe not enough spaces, or something else that's really hard to find. It would be nice if there was an easier way. Maybe this way is a *little* easier. I tend to use the Replace method, e.g. ActiveSheet.PivotTables(1).PivotCache.CommandText = Replace(ActiveSheet.PivotTables(1).PivotCache.CommandText, "WHERE (L.Beginn Between '1.1.2006' And '31.12.2006')", "WHERE (L.Beginn Between '1.1.2007' And '31.12.2007')") You still have to get the ReplaceWith syntax right, but it takes the rest of the SQL statement out of the picture. Make sure you include enough text in the ReplaceWhat argument so that you don't inadvertantly replace more than you bargained for. -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com

Re: Edit SQL-Query String in Excel Dagmar 3/21/2007 8:12:08 AM Hi, thank you for your answer. In which event should I place the SQL Query? Does it make any difference? --> In the Excel MS Visual Basic Window, I select "Worksheet" and then "PivotTableUpdate", "Change", "Calculate" etc.... Dagmar "Dick Kusleika" wrote: [Quoted Text] > On Fri, 16 Mar 2007 01:51:08 -0700, Dagmar > <Dagmar[ at ]discussions.microsoft.com> wrote: > > > > >Now, that I can read the SQL-Query I would like to change it. > >e.g. the original query is like this: > <snip> > > >WHERE (L.Beginn Between '1.1.2006' And '31.12.2006') > > > >It would be nice to change the L.Beginn Date in the end. However, I copied > >the SQL-Query String above and added the > >"?ActiveSheet.PivotTables(1).PivotCache.CommandText" in the front. > >Unfortunately this does not work ("Syntax error ..."). I also tried less > >complex SQL-Queries which do work. > > > >Are there any better/more comfortable ways to change this query? > > > > Nope. There are no good ways. There's an extra space, or maybe not enough > spaces, or something else that's really hard to find. It would be nice if > there was an easier way. > > Maybe this way is a *little* easier. I tend to use the Replace method, e.g. > > ActiveSheet.PivotTables(1).PivotCache.CommandText = > Replace(ActiveSheet.PivotTables(1).PivotCache.CommandText, "WHERE (L.Beginn > Between '1.1.2006' And '31.12.2006')", "WHERE (L.Beginn Between '1.1.2007' > And '31.12.2007')") > > You still have to get the ReplaceWith syntax right, but it takes the rest of > the SQL statement out of the picture. Make sure you include enough text in > the ReplaceWhat argument so that you don't inadvertantly replace more than > you bargained for. > > -- > Dick Kusleika > Microsoft MVP-Excel > http://www.dailydoseofexcel.com >

Re: Edit SQL-Query String in Excel Dick Kusleika <dkusleika[ at ]gmail.com> 3/21/2007 12:51:58 PM On Wed, 21 Mar 2007 01:12:08 -0700, Dagmar <Dagmar[ at ]discussions.microsoft.com> wrote: [Quoted Text] > >In which event should I place the SQL Query? Does it make any difference? >--> In the Excel MS Visual Basic Window, I select "Worksheet" and then >"PivotTableUpdate", "Change", "Calculate" etc.... When do you want it run? Are you prompting for new dates? Is the user putting new dates in a cell? Another option that I failed to mention is Parameters. You can read about Parameters here http://www.dicks-clicks.com/excel/ExternalData6.htm#Parameters I don't think I've ever tried Parameters with a an externa data pivot table, but I don't know why they wouldn't work. -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com