Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating a Piviot tabel when Excel data updates
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating a Piviot tabel when Excel data updates
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating a Piviot tabel when Excel data updates
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating a Piviot tabel when Excel data updates
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating a Piviot tabel when Excel data updates
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 "Brotha Lee" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating a Piviot tabel when Excel data updates
Put this code at the end of your query. Just change the page name and table name as required. Worksheets("Listing").PivotTables("PivotTable1").P ivotCache.Refresh Best Steve "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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating one Excel chart in my PowerPoint file updates all graphs | Charts and Charting in Excel | |||
Dragging bar updates tabel | Charts and Charting in Excel | |||
average of sumed data in pivot tabel | Excel Worksheet Functions | |||
How can I find out where data in piviot table comes from | Excel Discussion (Misc queries) | |||
Goal Line in chart but not on data tabel | Charts and Charting in Excel |