Jay,

OK I didnt know that.

There are a few solution you can use:
1) Convert the text into number in the Query
Open the query in microsoft query editor and change the SQL code into
CLng(`number as text`), where 'number as text' is the field you would like to
convert into text. Afterwards you can use the my previous pivottable solution.
2) Add VBA code to update the pivot either to the Excel file or to the
external tool (if possible)
Dim pvtSource As String

pvtSource =
Sheets("Pivot").Range("A1").CurrentRegion.Address( ReferenceStyle:=xlR1C1)
Sheets("Data").PivotTableWizard SourceType:=xlDatabase,
SourceData:="Sheet2!" & pvtSource

Where pivot is the sheet the pivot table is and data is the datasource

HTH

"Jayneedshelp" wrote:

There are 2 issuses with the solution.
On is that I want to pass a parameter in the spreed sheet. I would like to
do this because I have an external tool automatically running the querry.
The tool saves the new excel file in the harddrive so I can acces it later.
It also attaches a time stamp to the file name.

The second and big problem is that the field that is displayed as a number
is really text. I need excel to convert the text to a number. I was using the
=value() function.
I cannot get excel to automatically up date that function.


"Brotha Lee" wrote:

Jay,

The results from the SQL query are those retreived in an Excel sheet on
which you have a pivottable?

If so you could try the following by connecting your pivottable directly to
your datasource
1) Add a new pivottable
2) Select external source
3) Click get data
4) Select the query tab and select your query (or locate the query on your
machine by clicking browse
4) In the following step ammend the query as you like
5) Right click the pivot table and select "Table options"
6) Set the refresh every to any value you like (if the table should refresh
every 12 hours then set it to 720 minutes)

HTH

"Jayneedshelp" wrote:

I have a SQL query that runs every 12H. It puts the data into Excel and then
creates a new file and prints the pivot table. The problem is that the Pivit
table does not update when data in the querry updates.