Pivot Refresh After ODBC Query Complete Refresh
Jan 4, 2008
I have a query with ODBC connection to a SQL database. This query's parameter is linked to a cell. The resultant data is the source for a pivot table. I want to refresh the pivot table, when the query is run. I've tried using the cell that triggers the query....but the problem is that the query takes about 10 seconds to run. By the time the query returns new data....the pivot has already refreshed. I need it to refresh AFTER the query is complete.
I tried adding a cell that sums up the data from the query...thinking when THAT changes (due to updated data), to trigger the pivot refresh. Problem is that I don't know the trigger for when the sum cell changes (ie....formula change, not typed in.)
View 9 Replies
ADVERTISEMENT
Dec 1, 2009
I've used this code prior and it worked fine in Excel 2007, now I'm having issues with it. Originally every Pivottable & Query pointed to a different CSV file. Now I have two pivottables built off a Excel worksheet that is built from a Query. This allows me to do some complex lookups that I couldn't do with just the Pivottable and MS Query alone.
Here's the code that's failing out: Any problems with it in Excel 2007?
Private Sub Update_All_Data_Click()
Dim pvt As PivotTable
Dim ws As Worksheet
Dim qt As QueryTable
mytitle = "This will refresh all data for validation, are you sure?"
Msg = "The Refresh process takes about 5 minutes, are you sure you want to continue?"
Response = MsgBox(Msg, vbExclamation + vbYesNo, mytitle)
Select Case Response
Case Is = vbYes
' Do Nothing, continue with program
Case Is = vbNo
Worksheets("instructions").Range("a1").Select
End
End Select
For Each ws In ThisWorkbook.Worksheets
For Each qt In ws.QueryTables
qt.BackgroundQuery = True
qt.Refresh
Next qt
Next ws
For Each ws In ActiveWorkbook.Worksheets
For Each pvt In ws.PivotTables
pvt.RefreshTable
Next pvt
Next ws
mytitle = "Confirmation of data refresh"
Msg = "The data has been refreshed"
Response = MsgBox(Msg, vbExclamation, mytitle)
End Sub
View 9 Replies
View Related
Nov 27, 2007
Will someone please tell me the difference (if there is a difference) between the following 2 lines of ....
View 6 Replies
View Related
Jan 13, 2009
I've got a spreadsheet with a few queries to extract data into my sheet. Whenever I open the spreadsheet, I get a "query refresh" dialog box, asking if I want to "Enable automatic refresh". I keep clicking on the "Enable" button but I have to keep answering the question for each open. Isn't there a way to set "yean - ok - refresh the data" so that I don't have to keep saying "yes"?
View 2 Replies
View Related
Mar 25, 2014
I am using Task scheduler to open a workbook which has some connections to an ODBC that are set to refresh on opening.
I need to automate:-
One of the sheets needs to recalculate for the sort by ascending code to run on the sheet.
Save and then close after say 2 minutes of opening (to give the ODBC connections enough time to finish).
View 2 Replies
View Related
Feb 18, 2009
I have Excel XP and som spreadsheets that have external...I want the data is refreshed at the opening and this option Prompt the user Window to refresh data on opening or not.
How or where should I chage this in order this initial message is not showed but the book refresh the querys when opening.
View 9 Replies
View Related
Jul 27, 2006
i have an excel spreadsheet with 27 or so workeets. it contains sales figures in it.
I want to be able to link mutiple cells of this workbook to another workbook so that it retreives that data, So that when I hit the refresh (!) button it will automatically put the data in. I will recieve new sales figures (new files) on a monthly basis so i want it to be able to update the figures to the new figures.
View 4 Replies
View Related
Mar 11, 2013
Currently i have a button on sheet 1, that contains this VBA code
Code:
ActiveWorkbook.RefreshAll
Sheets 2 , 3 ,5 all have a MS Queries.
However I only need sheets 2 and 3 refreshed , and not 5.
At the moment it takes long to refresh all , and want to specify which sheets need to be refreshed, to make it faster for the user.
FYG, I cannot remove sheet 5, as it is required on adhoc basis.
View 2 Replies
View Related
Apr 27, 2014
I am trying to avoid selecting sheets in my current project. I have a querytable in A1. I can get it to work if i selects sheet and range.
Can it be done using with as below
Code:
With Worksheets("meeting to look at")
End With
View 1 Replies
View Related
Jun 16, 2008
I am trying to write a creative procedure that on workbook open will check the name of the worksheet if it includes the day's date in sheetname & if it doesn't rename the sheet & delete current region from A1, else exit the sub.
Then runs a query on an Access DB to place in the above worksheet.
I need this due to novice XL & DB (8)users.
This is what I have at the moment on a test DB courtesy of J Walkenbach's sample files .....
View 9 Replies
View Related
Nov 10, 2009
I have a workbook_open macro that refreshes a query. works great when opened directly from Excel 2007, but when I use Task Scheduler to call the sheet or call Excel the query will not refresh...macro runs but no refresh.
View 4 Replies
View Related
Dec 18, 2006
I am bringing data in from an ODBC database, I am using ActiveSheet.QueryTables(1) and then .refresh.
I want to run a sub after the data has been brought in which finds the last row and adds a formula.
The problem I have is that the data brought in is not show on the screen until all of the macros are finished.
Is there a way of forcing the screen to update?, I have tried the recalculate and refreshall commands or is there another method I should be using?
Sub autofill()
Dim myobj As Object
If ActiveSheet.QueryTables.Count > 0 Then
Call clearscreen
Application.DisplayAlerts = False
Set myobj = ActiveSheet.QueryTables(1)
I require the data to be on the screen before I can find the last row used which is done in "addmaths"?
View 5 Replies
View Related
Sep 24, 2007
I have a model which extracts data from an external data sournce using an ODBC query. This data is then used to generate a pivot table. I have incorporated code to update the query and the pivot table on startup but would like to disable the Enable Query Refresh message box which occurs at startup (it would also be nice to disable the security warning but I know that is a no no)
View 2 Replies
View Related
Dec 15, 2007
I've used the code below and it's worked to stop Update Links message, but i still need one tip to avoid continue message that shows up after Update Links massage:
Private Sub Workbook_Open()
Application.AskToUpdateLinks = False
Sheets("Feb").Activate
Range("A200:I400").Select
Selection.QueryTable.Refresh BackgroundQuery:=True
End Sub
View 2 Replies
View Related
Feb 21, 2007
I have a protected worksheet containing an SQL query. I have two cells on the worksheet unlocked so that users can enter fresh parameters to feed the query.
Because I have to use MS Query to interrogate our SQL server (current IT decree), the query won't support parameters directly when the query cannot be represented graphically - so I have to manually edit the sql command text and then refresh the query - or at least that was why I wrote the code below
The problem: My macro won't unprotect the sheet before it calls the SQL.it therefore won't update the sheet to detail the DSN, command text and number of parameters (latter should be 0) - info just used to verify queryit won't run the query / return the data as the sheet is still protected Comment: I have had to remove sheet protection in order to allow the sql to execute and return data. If protection is removed, the query does execute and return data ( changes in the "parameter" cell contents do cause the query to be correctly modified)
I could comment out the debug info but the query still doesn't return data if the sheet is protected.
Activesheet And worksheets(ndx) where ndx has been defined As ActiveSheet.Name
I apologise If my post Is difficult To follow - especially the code.
Sub AgedStockParameters()
nmb = ActiveWorkbook.Name
ndx = ActiveSheet.Name
Workbooks(nmb).Activate
Worksheets(ndx).Activate
ActiveSheet.Range("A1").Select
WkshtQryCon = Workbooks(nmb).Worksheets(ndx).QueryTables(1).Connection
ActiveCell.Value = WkshtQryCon
Range("A2").Select
WkshtQryFld = Workbooks(nmb).Worksheets(ndx).QueryTables(1).CommandText
ActiveCell.Value = WkshtQryFld.......................
View 2 Replies
View Related
Jul 25, 2006
I currently have several pivot table that's linked to a single pivot table(let's call it X) in the same workbook. I'm doing this to limit the file size because the data in X comes from a text file that has millions of lines. However, it's such a pain every time I need to update the tables because simply clicking "refresh" does not update those tables that are linked to X with new data. I would have to instruct the wizard in every linked table to point to X every time. I'm trying to write a small program to re-point to X for each of those other pivot tables whenever i refresh data. However, after trying to record the steps to do this I'm still unable to run these
Sub Macro1()
ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:= _
"PivotTable1"
End Sub
View 6 Replies
View Related
Jan 21, 2010
I have a pivot table being created from multiple sheets in a workbook. This pivot table is created in the same workbook. Now how do I refresh this Pivot sheet automatically when any of the worksheets are updated. Even manually I am not able to refresh since the Refresh data menu is greyed out.
View 14 Replies
View Related
Aug 7, 2006
The code to make all pivot fields in the "PivotTable1" equal exactly as chosen to the ones in "PivotTable2"? Cheers.
View 9 Replies
View Related
Mar 18, 2014
For refreshing the Pivot table , we have to right click the mouse in pivot table and then click Refresh button.
Any short key for refreshing the pivot table after modifying the data?
like f2,f4, ctrl+s.
View 3 Replies
View Related
Jan 17, 2010
I am running XL 2008 on a Mac and have created a pivot table that works well. However, without VBA (and I don't know how to write in Applescript), is there a way to put a REFRESH button on my spreadsheet?
View 9 Replies
View Related
Jul 22, 2006
I have a pivot table linked to a moving dataset, so I want to refresh it every 10 minutes. I can find the option to do this but it is greyed out. How do I enable it? (Excel 2003)
View 2 Replies
View Related
Dec 12, 2007
I am having trouble getting visual basic to update a pivot table.
(using manual refresh on the same pivot works just fine).
I have recorded a macro of the manual procedure, but it fails to work when used in a visual basic program:
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
I have tried using code presented elsewhere in these forums
eg http://www.ozgrid.com/VBA/pivot-table-refresh.htm
but the table stubbornly refuses to update.
The pivot's source data range is dynamic and works fine.
I think I'm missing something obvious or I have have a bug.
View 9 Replies
View Related
Jan 20, 2008
I now have a Pivot table on my worksheet that works great, the only problem I have is I would like it to refresh my data at the push of a button. I now have to click on a cell where the pivot table data is>right click the mouse>select refresh data and then I get the updated info. I know you can install a button to to this exact same thing but I don't quite know how to. I need step by step instructions. I tried OzGrid search, but did not find exactly my need.
This is what I did thus far I opened my work book>click on data>then macros> record new macro>I got a pop screen>I named the macro Refresh_Pivot_Table>store to this workbook>click OK> I see at the bottom left of my screen that the macro is still recording> I then highlighted the Column of cells where the pivot table data is and then clicked>tools>macro>stop recording.
This is the point where I'm lost. What is the next step to do? When I go back to the cell where I began to start the macro and right click I don't get the option to create a text box so that I can make a button and assign the macro I just recorded. Is this the correct method or did I miss something?
View 4 Replies
View Related
Apr 23, 2014
I have a macro to refresh all pivottables in my workbook. Each pivottable source from the same data pool - in addition I have a filter on each pivottable. Is there a way to fix this filter, such that once the source data is updated, the filter doesn't change? Or to only refresh the "data" in the pivottable?
My code for refreshing all pivottables is given below.
Dim pvt As PivotTable
Dim sh As Worksheet
Application.Calculation = xlManual
For Each sh In Worksheets
For Each pvt In sh.PivotTables
pvt.RefreshTable
Next pvt
Next sh
Calculate
Application.Calculation = xlAutomatic
View 2 Replies
View Related
Jan 15, 2014
After pivot refresh, several individual cells of column labels are rotating again from vertical to horizontal.
How can I fix this format?
15-01-2014 8-47-31.png
View 6 Replies
View Related
Jul 18, 2012
I have this code in an active worksheet that works with the exception that when I try refresh other Pivot Tables with the file that it goes into a constant loop.
Private Sub Worksheet_Change(ByVal Target As Range)
'
' Update the pivot table on this worksheet if data in columns A or B is changed
'
If (Target.Column = 1) Or (Target.Column = 2) Then
ActiveSheet.PivotTables(1).RefreshTable
End If
End Sub
View 2 Replies
View Related
Nov 13, 2012
I have two pivot tables on the same worksheet, tied to different tables, but both have a "date" column. When I change the date in either tables Report Filter this code does change the page field in the other one, but the data does not refresh. My primary table is PivotTable2, once I can get this working I would hide that report filter in PivotTable1.
Code:
Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
PivotMacro
PivotMacro1
Application.ScreenUpdating = True
End Sub
[code].....
View 2 Replies
View Related
Jul 24, 2014
I'm hoping to automate some pivot table refreshes. I've got a dozen pivot tables on a sheet all with different numbers. I'm hoping I can write a code that loops through the pivot table names and runs the refresh. Here is the code as it is right now. I've essentially copied, pasted, and changed the name of the pivot table for the refresh.
I would like to create an array in vba (22,21,20,19,18...) that renames the PivotTable and runs the code.
sub Refresh ()
Dim pt4 As PivotTable
Dim Field4 As PivotField
Dim NewCat4 As String
Set pt4 = ActiveSheet.PivotTables("PivotTable22")
[Code] ..........
View 3 Replies
View Related
Mar 14, 2008
I see some code on the forum on how to automatically refresh pivot tables in excel. I know to press alt + f11, but where do I go from there to put the code in? Also, what if I have several pivot tables in the spreadsheet, is there anyway to refresh all of them automatically?
View 9 Replies
View Related
Jul 15, 2008
A macro to refresh a pivot tables. And also Count the Rows and Columns.
I have multiple tabs with a pivot on each. I did copy the main pivot so I was hoping to be able to refresh the main and it will refresh all of the rest?
The macro is pasting in the "DATA" tab then (trying) to count the Rows and Columns.
Then (trying) to refresh all of the "tabs" Pivots at once..
Here is what I have so. I did get it to work without counting the Columns so I will include that.
'***Verifying that "You" want to continue with the Macro***
Answer = MsgBox("Are you sure you want to update?", vbQuestion + vbYesNo)
If Answer = vbNo Then Exit Sub
'***Deleting Rows***
Sheets("Data").Select
Rows("6:60000").Select
Selection.ClearContents
Range("A6").Select
'***Paste in from Business Objects***
ActiveSheet.Paste
'***Deletes the Headers that came with the Business Objects Copy***
Rows("6:9").Select
Selection.Delete Shift:=xlUp
Range("D6").Select..................
View 9 Replies
View Related
