I can download my data from the web. I can set it to download every so many minutes. But, I have to select run on my vb program to complete calculations and put data in correct format/sheets. I am trying to put a delay/timer in the program so it will refresh on itself and I will not have to keep selecting program run. If I put this in program it appears to refresh in a timely manner however I do not get any data on the sheets ? I have tried the wait routine and the if now = time + routine
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 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'm downloading data from a database that takes sometimes up to a minute to download the data. However I have a sum function in the macro that calls the cells that the downloaded data will be in. The problem is the sum function is occuring before the data is downloaded so the sum is always 0. Any way to build in a delay timer?
I want to close a spreadsheet on a timed event. i can manage the timed close but i need to reset the timer if any action occurs. I have attached what i have so far...
I’m using live data and was wondering if it’s it possible to trigger B1 to change its value from ‘no’ to ‘yes’ 20 seconds after cell A1 changes its value from 0 to 1?
I place number 7 in cell A2, A3, A12 and A22. I want to know the delay this particular number has. Looking at the example placed before the pattern should be the following (A2) 0, (A2-A3) 1, (A3-A12) 9, (A12-A22) 10. After A22 there's is no more number 7 therefore the delay in the example is of 3 i want this to change automatically until the next 7 appears. I've given an example, from C1 to P1 i have written 14 numbers i would like to know the delay of all these numbers.....
I don't know any way ask this question without attaching an example. But the problem itself is pretty easy to explain. On the attached worksheet, whenever anyone makes a change to the information in column C (labeled as Act. Chicks) this triggers a worksheet change that makes that cell have a red font. It also triggers a routine (in a standard module) that updates textboxes that continually display a running total.
Here is the problem: After the information is entered, and the routines have finished executing, there is an incredibly annoying delay of about three seconds. That is, it takes three seconds before you can even select a different cell. This is very difficult for the user to deal with, especially when she has a great deal of information that she would like to enter very quickly. What is strange to me is that if I run the routine for the Worksheet Change within the VBE, I get the delay after the last line of the code (in the standard module) has been executed. In other words, I can't see any code that is running during this long "pause." What is the computer doing during this time, and what can I do to fix it?
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"?
Trying to find a way to close outlook from excel workbook close. If outlook is open then delay excel workbook 45 seconds from closing.If outlook is not open then close excel normal. Here is what I've tried with no luck
Basically, I just recorded a macro to change the cell value. This code continues to 4.8 . . .Is there a more efficient way to code this?? Also, I'd like to add a time delay (say +-5seconds) between each step. So basically it would say:
Range("A1").Select ActiveCell.FormulaR1C1 = "3.1" [CODE THAT DELAYS FOR A TIME, THEN GOES TO THE STEP BELOW] Range("A1").Select ActiveCell.FormulaR1C1 = "3.2" [CODE THAT DELAYS FOR A TIME, THEN GOES TO THE STEP BELOW]
I am trying to see if it is possible to create an intentional delay when executing a macro. Basically I would like to pause the macro for 5 seconds and then continue with the rest of the code.
Workbook Name Changes – But Workbook Does Not For Five Seconds?? MS Office Excel 2003 / SP3 Installed. I have noticed an odd occurrence from time to time while switching between workbooks. When I switch from one workbook to another, (typically via the “Window” pull down – but the same result happens when switching workbooks on the tabs at the bottom of the screen), the “switched to” workbook name will instantly appear at the top of the application, but the actual switch to the selected workbook is delayed for several seconds, (five seconds or so).
While not a major issue, it is annoying. I suspect that it is a memory allocation issue, (I have a Duo CPU System / XP Pro / 2 GB RAM), and with that in mind I have both restated MS Excel and also performed a complete re-boot. While either option does (seem) to result in the issue being (temporally) removed from my machine, it does return. I may go for several days without this issue occurring, and then it appears for no known reason
I have created a macro that goes to a report application. The report application runs a script that extracts certain parts of various reports (the script asks for user input to pull the various reports) and exports the data to a spreadsheet. The script gets one report, writes the data to a spreadsheet, gets the next report, appends the data to the spreadsheet, etc. The macro then moves and formats the data in a customized way to be put to second spreadsheet. My problem is that the macro tries to format the data before the script has completed - how can I stop the macro from running until the script has finished? The script varies in run time - anywhere from 1 to 5 minutes.
I have a workbook that has many simple functions moving data around to different sheets and processing some if statements on some of the data. The problem that I am running into is all of the data is initially pulled in from a SQL DB, and I believe that the workbook is calculating many of it's if statements, prior to all of the SQL data being loaded, therefore, many of the worksheet formulas are coming up with the wrong result. Is there a way to delay the calculation of all the basic worksheet functions, so that I can ensure that all the data is in the workbook from SQL, before they all fire. I have attempted to use Tools--Options--Calculation--Manual Calculation, but it appears that even though I have things set that way, all of the formulas in the workbook have already pulled their values through.
I have code that generates random numbers 1 - 90, what I would like is when the command button is pressed the numbers are generated every 5 seconds, I have attached the workbook.
I have the following code saving a copy of a spreadsheet as a backup every 30 minutes. The problem is the save code is running as soon as the file is opened. I want to save only after the file has been open for 30 minutes. The way it is now if I open the sheet up and something is wron the backup has already been overwritten by the messed up sheet. Will someone please let me know what needs changed to eliminate the inital running of the code?
Sub do_something() sec = 1800 when = Now + sec / 60 / 60 / 24 Application.OnTime when, "do_something" Application.DisplayAlerts = False ThisWorkbook.SaveCopyAs "S:QUALITYTest Results Spread SheetBackup of Riser MezRiser Mez backup.xls" Application.DisplayAlerts = True End Sub
When I first entered the function code as a formula on the worksheet, the result was instant and correct.
However, if I change the contents of one of the cells in the stringconcat range, the result does not change.
I need to edit (eg F2 then enter) either the cell with the text or the cell with the formula for the result to update. If I highlight the column where the formula is used and Find / Replace to change "=" for "=", they all update at once (tip I learn't here)
Has anyone experienced this or have any ideas why it may happen?
Notes: I am using Excel 2000 (work) I have 6 cols of info (I to N), These 6 columns contain formulae that basically copies plain text from the same row in Cols A to F. (Since not all the left hand side cells are full, the formula copies down the value above if the cell isblank, or copies the cell value if there istext) Formula is in Cell G2 and reads =stringconcat("; ",TRUE,I2:N2) Code is stored in the Module1 I have 1 header row (Row 1) I have 16 rows of text (Rows 2 to 17) Not all rows have text in all 6 columns, but the are no blank cells betweeen data working left to right I need our IT Admin to install Excel Genie!
My software is Excel for Mac 2011 version 14.4.3 (140616).
I am using the present value formula =PV(rate, nper, pmt, [fv], [type]), with the pmt expressed as a negative number, to determine the present value of a lump sum that would generate specific, equal, regular annual payments, exhausting itself in the process. I fill it in this way: rate is an annual rate, nper is the number of years the payments run, pmt is the annual amount paid each year, fv =0, type =1
I would like to be able to have a similar formula in Excel that would do the same thing except that it would take into account a time period where no payment is made, ie a "delay".
For example using the PV formula I know that if I have $49,019.96 invested at 1%, it will generate $10,000 per year for 5 years, leaving a net investment of 0. I would like to know the formula that would tell me how much I would need to invest at 1% to yield $10,000 per year for 5 years, again exhausting itself completely in the process, with the first $10,000 payment starting 3 years from now. I know it would be less because I would have the full amount earning interest for 3 years at 1% before any payments need to be made.
Problem: We have some rather large excel spreadsheets (142,000 rows, 190 columns) and some users that need to delete the data from rows of cells 5,000 rows + at a time. When doing this machines jump in CPU, Excel goes "not responding" and on average comes back to life 1 minute 45 seconds after pressing the delete key.
Note: We are not doing a right click and deleting the rows. This is a highlight of the 5000 rows and pressing the delete keyboard key to clear the data.
We've tried disabling the Auto Save and the Workbook Calculation changed to Manual.
Dell is no help, Microsoft is no help. This process can be replicated on multiple Dell machines and with Operating Systems of XP SP2, SP3 and Vista, machines with single drives, raided systems and even a machine with a solid state drive. All have 2GB of RAM or more.
Put in a statement within a Macro that populates cells with the values that I want it to but instead of populating all at the same time, is it possible for the values to be delayed.
I have designed a mock spreadsheet (attached) it has two columns 'Before' and 'After'
After = Before values (in this mock)
When you press the button, the values are populated straight into the 'After column' can we add the delay between the values? So that the values dont come up straight away.
I have two worksheets, one with "supplier names", "order numbers" and "delay in days" as columns. The other one with "supplier names" and "average delays" as columns.
It is the column "average delays" that I have issues with, I need Excel to search the "supplier names" column in the "delays" sheet and identify every specific supplier name, connect the delay in days for that order and calculate the average delay in the right cell of the column "average delays" in the sheet "delay statistics".
I have tried the help files and to search this forum but I have found nothing. I have also with my knowledge tried a few different ways using the IF function but nothing so far.
This is something I started doing but it is of course far away from any truth.
=IF(;Delays!B1:B200="Greber")..............
This is the "delays" sheet where I want to find my info. Nothing in the delay column stands for "no delay"...Kinda obvious but you know... So even the "nothing" needs to be included in the calculation..
