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 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 a large workbook with about 20 worksheets that I have used for several years without problems. Recently, I have been getting strange behavior when the screen refreshes. The biggest problem is that I get images of other worksheets covering my current worksheet when I change a cell. I can make them go away by scrolling or flipping between another worksheet and back. I have tried using manual recalc and this has helped somewhat but I can still get the images sometimes. The workbook contains VBA that performs various tasks on every worksheet (unlock, update pivots...) but these have been in there forever. There is also one piece of code that performs advanced filtering. I can only think there was something changed with a security patch, but I wanted to know if anyone else has had this issue and might be able to provide some advice on how to prevent it.
I've developed a complex dictator application, which pretty much takes over the entire Application object. During beta testing, I've had some user complaints that, once my program is started, they can't access their existing workbooks.
In order to remedy this situation, I'm trying to construct a front-loader workbook, which will automatically determine whether or not to start a new instance (application) of Excel. If the user already has a workbook open (i.e. "MiscBook.xls"), the front-loader will start a new application; otherwise, it will just use the existing application.
My "Auto_Open" code for the front-loader workbook is shown below:
Sub Auto_Open()
Dim xlApp As Excel.Application Dim wbkTemp As Workbook, wbkApp As Workbook Dim lCount As Long Dim vTemp As Variant
The code works fine, but I have a minor gripe: every time a new application is created ("Else" in the "If-Else" above), the screen doesn't appear to refresh. In other words, I get a new window (instance of Excel), but none of my custom-defined buttons and menus show up. If I hover my mouse over their intended locations, they "appear" one at a time.
I'm just wondering - is there something I can do to prevent this behavior, or "refresh" the new application window upon startup? I'm essentially looking for something like "Userform.Repaint", only for a brand-new Excel Application object.
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"?
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.
Neither is it because you've turned SU off, and then used 'Select' or 'DoEvents' etc in your code.
When running an update for a QueryTable (once a second), 3 'buttons' (activeX image controls with code behind them) flicker - though I see no reference to selecting and/or screenupdating in my code
Is this purely something about refreshing a querytable that I can't fix?
Is there a subclass or API call I can intercept to stop drawing the images alone?
Code:
Public Sub ActivateConn()
Dim strFilespec As String Dim strConn As String Dim sqlStr As String
Set RecSet = New ADODB.Recordset
[Code] .....
In short, there's no REASON why I couldn't just use the selection_change event to fire the same macros as the buttons but... well... it doesn't look anywhere near as good (I have a thing about making VB apps look as good as the code behind them is smart)
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)
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.
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)
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
I have 2 excel tables that are frequently edited on a laptop and saved to a Dropbox folder. What I would like to do is have a second computer that will have these tables open and constantly updates (periodically or when changes are made). This computer and monitor will serve as a display in a common area (think of it kind of like a flight tracker).
What I have tried: I have tried to enable the Shared Workbook with a refresh of 5 minutes but the display computer is not showing the changes. I would like to make it refresh every minute if a periodic refresh is possible.
Note: It is my understanding that if you reopen the file, the data will update so maybe there is a way to have the file reopen every few minutes? I was able to successfully test this method; If I am able to automate this process without the two excel sheets changing the window positions (using a Workspace file perhaps) it would be perfect.
I'm looking to create a refresh/update button for my userform. What I have is various textboxes, of which the last textbox contains input from the other textboxes. For example, I have 3 textboxes. The first one contains months, the second contains years, the third pulls from both the first two.
First: August Second. 2009 Third: August 2009
What I need is if I change the text in either the first or second textbox, I can click the refresh button and it'll update the third box.
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.
I have a program which iterates on command. A plot on the spreadsheet is designed to show the progress of the iteration. However, the plot does not update while VBA is running. What is the command or technique to refresh the plot at an intermediate stage in the calculation?
I have a Workbook with multiple PivotTables which are feed from an external data cube. I need to update all of this Pivotables to the same date through VBA. The date will be a known variable and it corresponds to the "Periodo" field. I had thought of doing this by having the date stored in a cell of maybe with a calendar pop-up
I ran into a screen update problem in a project I was working on and couldn't find a solution, so I wrote some test code in a new workbook as follows;
Sub Flkr() '------------------------- 'Screen update test '------------------------- 'Select sheet1 Sheets("Sheet1").Select 'Disable screen update Application.ScreenUpdating = False 'Select sheet2 Sheets("Sheet2").Select 'display message MsgBox "Why is the %&$@ screen updating?", vbCritical, "??????" End Sub Seems simple enough,eh?
When I step through this code the first statement works(sheet1 is selected) The next statement is executed without an error, so I assume it works The next statement sheet2 is selected, and the screen updates!
i was wondering if there is a way to display a pop up message that a stating that the spreadsheet is currently updating.
What i have is a macro running on Auto_Open when workbook is opened. I have switch off the screen updating. What i want now is for maybe to have a pop up stating the spreadsheet is updating. Setting screen updating to false means that my excel freezes for a few seconds. I think users might be thinking that excel has crashed and therefore would like to include the message that the spreadsheet is updating its information.
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 have 2 forms. Form1 and Form2. I load Form1 to display. A button on Form1 calls Form2 to display. After I hit Cancel-Unload on Form2 it doesn't dissapear. How do I get rid of the residual image of pesky Form2 after I unload it and only display Form1 WITHOUT having to reload Form1 after Form1 calls Form2
In English Form1 Displays Form1 call Form2 to display Form2 is unloaded by hitting a button on Form2 Form2 is unloaded, however, it's image still displays!!!
Need some way to force screen to refresh without having to unload Form1!
This is to run stored procedures (in the SQL Database), and pass variables. But...once the query is setup...all I really want to do is change the variable...and "fix" the sql query with the new variable value.
Im using a spreadsheet with several third-party add-in queries using Showcase Strategy 4.5. The trouble I'm having is in writing a macro that will automatically refresh the queries. Currently I am using the line
application.run ("refreshdata")
however this refreshes all the queries and at several points I only need to refresh one or two. If anyone has any suggestions then I'd be very gratefull.
Also I feel the need to confess that I pilfered the above code from another forum and so I dont actually know how it works. VBA help doesnt seem to have anything relevant to say and I'd feel better knowing what the code I'm using actually does.
I have a form that laods when the workbook is opened. I also have an MS query that retrieves data from another source. When i open the workbook without the form loading i get a message asking if i want to update the query automatically. When i open the workbook with the form opening the message doesnt show until i close the form meaning i have no data in the sheet to use. Is there a way to get the query to refresh before the form opens?
