Updating a Piviot tabel when Excel data updates

[Guest]

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.

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

In my SQL query how would I change the
TASK_VARIABLES_OCCURANCES.VARIABLEVALUE into a number?

SELECT TSKVAROCALINE.NAME, TASK_VARIABLES_OCCURRENCES.NAME,
TASK_VARIABLES_OCCURRENCES.VARIABLEVALUE, TASK.CREATOR,
TASK_VARIABLES_OCCURRENCES.VARIABLETIME
FROM OPS$OCSHIS.TASK TASK, OPS$OCSHIS.TASK_VARIABLES_OCCURRENCES
TASK_VARIABLES_OCCURRENCES, OPS$OCSHIS.TSKVAROCALINE TSKVAROCALINE
WHERE TASK.TASKID = TASK_VARIABLES_OCCURRENCES.TASKID AND
TSKVAROCALINE.VARIABLEVALUE = TASK.CREATOR AND
((TASK_VARIABLES_OCCURRENCES.NAME Like '%tt') AND
(TASK_VARIABLES_OCCURRENCES.VARIABLETIME>? And
TASK_VARIABLES_OCCURRENCES.VARIABLETIME<?) OR
(TASK_VARIABLES_OCCURRENCES.NAME In ('CL2 Used','PVC Used')) AND
(TASK_VARIABLES_OCCURRENCES.VARIABLETIME>? And
TASK_VARIABLES_OCCURRENCES.VARIABLETIME<?))
ORDER BY TASK.CREATOR


I am in the process of trying the VB solution you suggested.

Not solved yet but thankful for your help

 

[Guest]

Put this code at the end of your query. Just change the page name and table
name as required.
Worksheets("Listing").PivotTables("PivotTable1").PivotCache.Refresh

Best
Steve