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.
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
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.......................
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.)
I have an Excel workbook which runs a query via DAO against an Access database. The query usually takes about 6-7 seconds. Id like to do some animation while the query is running. My animation is just a simple Autoshape that I rotate (1 degree at a time) in a loop. Is there a way I can excecute my query and then continue to run my code while the query is excecuting?
View 2 Replies View RelatedI'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 RelatedI 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.
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 .....
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
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 RelatedI 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"?
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 RelatedI'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
I am using Excel 2010 and I have a password protected workbook with password protected sheets that uses several macros. Most of them, in order to run, have to un-protect the sheet and then re-protect it again. This has been accomplished easily enough by adding ActiveSheet.Unprotect Password:= "mypassword" and ActiveSheet.Protect Password:= "mypassword" to the appropriate places in the script. All of my macros, which do various things like sorting and moving data, deleting blank rows, displaying dialog boxes containing warning messages etc. run fine.
My problem is this: when I password protect the sheets manually, I have checked the following options in the "Protect Sheet" dialog box. Under "Allow users of this worksheet to" I have checked 1)Select unlocked cells and 2)Format cells. After entering my password and closing the dialog box my sheet is protected, but I can edit cells in the manner my allowances permit. However, once I run any of the macros that un-protect and re-protect the sheet, I remain able to select and edit unlocked cells (practically, for my purposes, this means that I can input data which will appear in the default font size and color of the sheet) but I cannot format cells (which, practically, for my purposes would allow me to occasionally change the font color and size of the data). Naturally, after running a macro, the other cell-formatting options are unavailable to me as well. Is there any way to get my manual selections to remain in place after running a macro that functions as mine do? Or is there any way to make my manual selections the default settings for a protected sheet?
I have an automatic macro that runs from every sheet but is only based on one sheet. I had to protect the worksheet and in order to make the macro work I had to add an unlock and a lock code. The problem now is every time a user enters something it looks back to the other sheet and then comes back. Is there a way to prevent the user from seeing this. See code below
Private Sub Worksheet_Calculate()
Sheets("Profit Calc Local Currency").Unprotect Password:="hspricing1"
Application.EnableEvents = False
Dim lr As Long
lr = Cells(Rows.Count, "XEA").End(xlUp).Row
Range("$XEA$1:$XEA$" & lr).AutoFilter Field:=1, Criteria1:="1"
Sheets("Profit Calc Local Currency").Protect Password:="hspricing1"
Application.EnableEvents = True
End Sub
So I have a worksheet that has a lot of macros in it. I have just barely run into a problem that happens anytime a change a cell in another workbook that I have open. The second workbook I have open isn't even a macro enabled workbook. But for some reason it gives me an error and says I am trying to run this code.
[Code] ....
It gives me the runtime error 9. This code is supposed to run when I click on a combobox in my main workbook but it is running anytime I change something in my other simple workbook. Why this happens and how to stop it? I have had a lot of similar problems in the past but I just dealt with it by only having one workbook open but in this case I will need both.
Is there a way to prevent the Workbook_SheetChange event macro from triggering when I run Excel's Solver?
The solver goes through 100 or so iterations changing multiple cells each of which seemed to trigger the Workbook_SheetChange event. If there was a Solver Run event, I could turn off application events, but as far as I know such an event does not exist.
I have 2 sheets.
Sheet2: it contains data that is being imported from an external link with the following info: ....
Is there a way to refresh a sheet every minute? So not on selection change but automatically every minute
I know this code will refresh but i dont know how to do this every minute (Not a selection change as then this could refresh every second which will not be efficient)
Sheets("Notes").Calculate
I am trying to build a sales sheet that will keep all the records during the month but every day it refreshes to a blank sheet, then allowing users to flick back to a previous day and all the data from then is still saved?
I think this is possible in excel as I am sure I have seen a working sheet before but I don't know where to start or even what the process would be called in excel?
The code below enables entire workbook protect/unprotect, however ultimately it refreshes a particular sheet (sheet 8 in this instance) when the relevant data table is edited.
I have 7 sheets (one is the data table and the other 6 house pivots that work off this data table). What I want to happen is for the code to enable the unprotect, refresh and protect of all the sheets exactly like it does for the current sheet 8 (I am simply new to this an am unsure of how to write the code to make the refresh happen for all sheets).
[Code] .....
I have inserted the following code in the 'ThisWorkbook' section to allow for to sheet protection macro to work:
[Code] ......
Along with the following code in the sheet 8 section to enable the refresh macro to work:
[Code] .....
I have a userform that collects data from a worksheet, if I press back on the userform to take me back to the sheet and then change the data, when I fire up the userform it doesn't update with the new data?
I've put DoActions in UserForm_Initialize tried userform1.repaint and nothing works..
To get from sheet to userform there is a button that valdates the data before showing userform1 so it should always run the UserForm_Initialize at a guess?
I want to make it possible to refresh a particular sheet on clicking a button. i can create the button and even assign the macro. but the code for macro, to refresh i don't know how to generate the code.
View 6 Replies View RelatedI have a workseet that is basically a massive list of orders that have been placed. I have some conditional formatting based on cells that use the today() function to work out how long (in days) since the order was placed.
Trouble is, the spreadsheet is always open, and is never closed down... so it does not refresh automatically. So orders that should go red to indicate a week since they've been placed, don't!
I don't want to rely on people refreshing the screen. Ideally, I would like it to refresh once a day at 1 minute past midnight, or even every hour...
I have a summary sheet that pulls data from several worksheets. I have a filter on column I that hides all rows that have zero values in that column. Once I protect the sheet I cannot get the filter to update as the source data changes. I have tried other macros from forums but I cannot get them to work. I would like the filter to update every time that the workbook is calculated.
View 2 Replies View RelatedI am running a macro to create a combined master summary sheet from data in several other sheets contained in one workbook. That is working fine. I need to create some kind of refresh macro so that when data in these sheets change the master sheet will change and update automatically.
View 9 Replies View RelatedI found one of your old VBA entries that allowed me to automatically refresh an autofilter function, and it works great.... with protection off.
As soon as I protect the sheet so users can't enter data into the pages showing the autofiltered data, the autofilter kicks off.
i have set the protection to allow autofiltering, so it's something that is preventing the macro (for automatically updating/refreshing the autofilter).
I'd love to either fix the macro to "autorefresh autofilter" with protection on, or just enter another code that will disable all functionality on the worksheet. (workbook is set up with multiple sheets, first sheet is for all data entry, subsequent sheets are filtered data-trying to make them viewable only).
In sheet1 (SA Awards) I have the source table for my pivot table in Range ("A1:G50"). In sheet2 (Team Listing) my pivot table is located in Range("K2:S13")
When I make changes in sheet1 I need my pivot table to update, I recorded a macro to refresh, however have only got it to work via a button & only if Sheet2 is unlocked
Sub PivotTableUpdate()
Sheets("Team Listing").Select
ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh
Sheets("SA Awards").Select
Range("B2").Select
End Sub
1. How can I get this to work in the Worksheet_Change Event?
2. How can I password protect Sheet2 & still have it work?
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.
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).