Edit SQL-Query String in Excel

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

On Fri, 16 Mar 2007 01:51:08 -0700, Dagmar
<Dagmar.RemoveThis@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