View SQL Query-String in Excel

That works! Thank you!

Dagmar

"Dick Kusleika" wrote:

> On Thu, 8 Mar 2007 01:49:43 -0800, Dagmar
> <Dagmar DeleteThis @discussions.microsoft.com> wrote:
>
> >Hi!
> >
> >We are using an application which creates Excel Pivot tables. These Pivot
> >tables get their data from an SQL-Server. I need to see (maybe edit) the
> >SQL-Query string. From your newsgroup I learned that the commands "?
> >ActiveCell.QueryTable.CommandText" etc. would be the solution to my problem.
> >
> >However, when I run them in the immediate window I geht "Run-time error
> >1004" Application-defined or object-defined error".
> >
>
> Dagmar: That will work for normal External Data Tables, but for
> PivotTables based on external data, I think you'll need:
>
> ?ActiveSheet.PivotTables(1).PivotCache.CommandText
>
> --
> Dick Kusleika
> http://www.dailydoseofexcel.com
>

Hi!

I have got another question:
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







Es funktioniert:



Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

ActiveSheet.PivotTables(1).PivotCache.CommandText = “select * from auftrag�

End Sub



"Dick Kusleika" wrote:

> On Thu, 8 Mar 2007 01:49:43 -0800, Dagmar
> <Dagmar RemoveThis @discussions.microsoft.com> wrote:
>
> >Hi!
> >
> >We are using an application which creates Excel Pivot tables. These Pivot
> >tables get their data from an SQL-Server. I need to see (maybe edit) the
> >SQL-Query string. From your newsgroup I learned that the commands "?
> >ActiveCell.QueryTable.CommandText" etc. would be the solution to my problem.
> >
> >However, when I run them in the immediate window I geht "Run-time error
> >1004" Application-defined or object-defined error".
> >
>
> Dagmar: That will work for normal External Data Tables, but for
> PivotTables based on external data, I think you'll need:
>
> ?ActiveSheet.PivotTables(1).PivotCache.CommandText
>
> --
> Dick Kusleika
> http://www.dailydoseofexcel.com
>