Excel Crashing On Refresh Of Data
Sep 12, 2013
I have a report that uses external data to feed a Pivot Table.
I noticed that the data source had been turned off automatically by Excel and turned it on and added the file location to the trust centre.
I also set the data to refresh when the workbook is opened.
I saved the file and then tried to refresh the data which caused Excel to crash. Of course now I can't open the file either because when it attempts to refresh itself on opening that causes Excel to crash as well.
Addendum: I have removed the Trusted Location which has caused Excel to disable the connection again. This has solved the crash on opening but I still can't refresh the data.
View 3 Replies
ADVERTISEMENT
Aug 11, 2012
I made an Excel/VBA solution for a company in which userforms were created and manipulated programatically, including their code modules. They would then be deleted so next time they were created there wasn't naming errors.
They acted as data input forms which would have an input control for each column of data on the sheet.
There was one method which would create the form and then call another to add code to it's code module.
In one scenario, if the user would do data entry on one sheet and then immediately after do data entry on the same sheet using the data entry forms.
When this occurred something peculiar happened, after the method which added the code to the code module executed excel would crash. By putting a MsgBox to pop up just before the End Sub statement and having another in the calling function immediately after the procedure call. It became evident that the sub adding code to the module was not exiting correctly and causing the program to crash.
In essence the program was crashing when End Sub was executed
View 1 Replies
View Related
Apr 28, 2014
My Excel file crashes whenever I attempt to make a copy of a tab, within the same workbook, and then save. This problem only occurs with its existing naming convention. In other words, the file crashes with the name "Cash Flow Model 5 Year Plan". If I rename the file to "Copy of Cash Flow Model 5 Year Plan" then it works perfectly, with no issues copying a tab within the workbook. The file size is not too large, as this issue never occurred when the model was twice the size.
View 1 Replies
View Related
Nov 21, 2013
I have to send out emails to all of my staff with their new login ID's and PW's and other misc information one by one to each user. Reason being is that all ID's, PW's and other information pertains to that specific user and includes sensitive information.
I know there's a VBA script I can use to send out the emails, but the script I found online keeps crashing my Outlook and requires it to go through my personal inbox which takes 15 minutes every time.
I have the pertinent information split up into different columns/cells.
A1: email
A2: subject
A3: body (ID, PW, verbage)
A4: attachment link (if required)
A5: if I could have Outlook automatically stamp each email with my signature that'd be awesome as well.
View 4 Replies
View Related
Dec 27, 2012
I have a semi-large macro which I have been creating on and off for a few months now. The file will fundementally be used by people with little knowledge of Excel, hence I force the user down certain alleyways depending on what they have previously done with the aid of veryhidden sheets and such like.
Upon opening the file, some code is ran to hide all of the sheets in the file apart from the home page. This is a pain when I am still working on the code though, so a portion of it is commented out. When I uncomment the "on error.. - next sheetIn" so that the file runs as it would be used, the code itself works perfectly and hides all of the sheets apart from the home page. Unfortunately, I can't then view the VBA code as it instantly crashes Excel (and any other Excel file I currently have open). This happens whether I click the Visual Basic button in the Excel ribbon, whether I try and access it through the design mode on a control, pressing Alt+F11 or even opening up another spreadsheet containing code and attempting to click on the broken macro after viewing the VBA code for the working spreadsheet.
VB:
Sub workbook_open()
'stop screen flickering whilst running the code
Application.ScreenUpdating = False
[Code]....
The only way currently to get back into the code is to open the spreadsheet up with macros disabled and recomment the code, but obviously this loses the function of the code.
View 3 Replies
View Related
Apr 16, 2014
I've been trying to put together a function in excel which will make it simpler and a bit clearer to produce the coefficients of trend lines in cells. I want to produce an excel function like SLOPE and INTERCEPT but for exponential, power and logarithmic trendlines. For example, I can produce the coefficients for an exponential trendline like this:
Code:
=SLOPE(LN(K2:K11);J2:J11)
=EXP(INTERCEPT(LN(K2:K11);J2:J11))
Ideally, I want to be able to do so without the need to convert the terms with LN function, and be able to replace it with a new function that deals directly with an X Range and a Y range
I've produced the following code:
Code:
Function PowerSlope(YRange As Range, XRange As Range)
For Loops = 1 To YRange.Count
YRange.Value2(Loops, 1) = Log(YRange.Value2(Loops, 1))
Next Loops
PowerSlope = Application.WorksheetFunction.Slope(YRange, XRange)
End Function
However, this causes Excel to crash and shut down (not the macro itself, but the entire program). I can't even get the function to run to a break point in the first line before the crash happens. I'm able to remove the FOR ... NEXT loop and use the function to directly return the result of the linear slope, so I'm confident the issue is not in that part of the code.
View 4 Replies
View Related
Jul 21, 2014
I'm building a bookkeeping workbook that is only currently 800kb with TWO cells that have data validation and no cells have conditional formatting. There are a fair few formulae but they are all simply SUM, SUMIF and CONCATENATE.
View 1 Replies
View Related
Mar 9, 2012
I have a genuine copy of office pro 2010. All works fine except excel!
It seems to be mainly on one workbook I have brought over from open office and on a mac! It wont save due to errors it won't tell you about? It then stops responding or crashes? Wont save as etc....
I have repaired, I have even deleted and downloaded a new office pro genuine and installed and doing the same so i guess it has to be the file?
BUT it seems to work on another laptop we have, same file, same excel????
The only thing i seem to be able to find is in the permissions it has an "unknown account"? Also under the file menu where it allows you to check the file it states something about conditional formatting i have used and not being compatible BUT i cant for the life of me find that in the menus to get rid?
View 10 Replies
View Related
Aug 28, 2013
I'm using Excel 2010 to link a table to a XML file on my server. I'd like to distribute the Excel file to a group of people and have it updated every time the XML file is updated on the server.
In Excel, I'm pulling the data using "Data", "From Web" and then I type in the path of the XML file.
Excel then builds a table with all the data but if the XML file is updated on the server, the data remains static, even if I click "Refresh" or "Refresh XML data" on the table.
It should be able to download new data including new columns to the table, if any.
View 2 Replies
View Related
Nov 8, 2013
I recently received an .xls book which I then saved as .xlsx (I'm using 2010). There are just under 8,000 rows and 20 columns. File Size 1MB.
The only formulas in the sheet are the ones in a column which I inserted and copied down for all 8,000 rows. Nothing too complicated: no arrays or anything. The sheet calculates fine.
I am simply trying to copy and paste these formulas as values (into the same cells), though at every attempt Excel crashes. I tried on smaller sets of the column and just got it to work for a few hundred rows, though it struggles with any more than that.
I opened a different workbook of mine, and tried the same operation on twice as many cells containing complicated, lengthy array formulas and the action completed instantly.
There is no Conditional Formatting in the book, no code, no 'last cell' issue, no Named Ranges, no external links.
I have even copied the data to a new workbook, then copied the text of just one of the formulas over into this book, added an equals sign, copied down and recalculated, then tried to paste as values again. Still crashes.
Formula:
=IF(AND(N3>1,ROWS($1:1)<>MATCH(M3,$M$3:$M$7979,0)),"Exact Duplicate","")
is far more resource-hungry than I thought, though if that were the case, wouldn't the issue be during calculation (which, as I said, is fine) and not during a paste attempt? No, it can't be this.
View 1 Replies
View Related
Jun 18, 2014
vba in excel 2013 pivot table that updates from an online CRM rows are a to h and it can be any number of rows. What I am trying to do is track progress. In column f values indicate probability for success 10 25 45 90, which can go up or down. The pivot table is refreshed to get the latest values from CRM. the update is handled by a connection to the crm not in the vba.
So far I been researching methods to conditionally format values that went up, down or remained the same since the last refresh with up down and across arrows. I have managed to piece together what I think should work but alas it is not. So I have come to you internet.
My code first clears any formatting and goes down the column avoiding null or empty cells, comparing the values in column f to values in column j.
I have 3 conditions greater than, less than or equal to, and would like add an icon for each based on the result of the comparison.
Finally when it finishes the column the code copies the current values in the pivot table column f to column j outside the pivot table which i hope to be able to hide once the cf works. The code is below
[Code] .....
View 1 Replies
View Related
Aug 3, 2013
Ok so I have an extensive worksheet that pulls a lot of data from the web, 47 connections to be exact. So on my work computer that runs excel 2007, the data refreshes instantly and I can see all the numbers flying through and changing as the sheets refresh.
However, my personal computer running excel 2010 hangs up with any type of data refresh, not just this sheet in particular. This computer has 8 GB of ram so I don't think that would be the issue?
Does excel 2010 have an issue with data refresh from the web?
View 4 Replies
View Related
Feb 10, 2014
I have one simple (but large table). It has dates across the top (formatted in hh format). I would like the associated table to format according to the day and also to format differently when there is a public holiday. So I have built a table with the holidays and named the relevant cells as "Holiday_Valid".
I have the following formula in the conditional formatting;
[Code]....
Where D11 has the current day in question and Holiday_Valid is a list of public holidays. Since there is a cell for each hour of each day I am using "int()".
There is a second conditional format to format Sundays differently as below;
[Code] .....
My problem is that these both work well....but then after a few minutes the whole sheet crashes with those dreaded "Trying to recover your data" and "Excel will restart" etc.
I have removed references to named ranges and so far - so good....but this means putting the validation table in the same sheet as the main table. In the past I have been able to use named ranges (albeit not in such convoluted formulae), but now it seems that it is not working any longer.
When I open the recovered sheet, all the conditional formatting has been removed and the message from the repairs is that there was some invalid conditional formatting.
Extensive web searches did show some issues with conditional formatting using names ranges....especially with frozen panes....which I need use with a sheet this big.
View 4 Replies
View Related
Jul 12, 2013
I am using Excel 2011 and we have a link data source to the outside SQL Server.
For the 1st step, I linked to the Database and retrieve the data.
P1.png
2nd step. Sort the data by using the column "Description" Descending. Add another column to the table and put the 1, 2, 3, etc in that column.
P2.png
3rd step. I clicked on "Refresh" and the new column values are not consistent. I mean they are moving. So, I added another row in the database to test and it becomes very obvious. It can be clearly seen at the following image. The value of "A" should always be "10", but it has been changed to "1" after the refresh.
P3.png
how I could retain the position of another column after the data refresh?
View 3 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
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
View Related
Feb 15, 2013
I have an excel tool I am adapting to 2010 from 2003. It imports another tool which has been created elsewhere in my company and it is protected so I cannot alter it. This tool that I cannot change creates its own custom toolbar the "old fashioned" way so it dumps into the add-in tab on the ribbon. My tool opens this corporate tool and changes some items on the toolbar (enters values in a text box) and then imports data from it. However in run-time the custom toolbar never appears. Is there a way to refresh the ribbon in run-time after opening the file so I can then access the toolbar items?
View 1 Replies
View Related
Jun 1, 2014
At work we use a connection to a SharePoint List within Excel to refresh the data in the files.
When excel is refreshed I get this error (this happens for all users in the team and multiple workbooks)
Image 1.png
The weird thing is I can export the SharePoint list to an excel file and it is fine but as soon as i go to refresh it (Data>Refresh All) it produces the above error.
View 1 Replies
View Related
Feb 1, 2012
Using Excel 2007.I have references set for Microsoft ADO Ext 2.8 for DDL and Security and Microsoft AciveX Data Objects 2.7 Library.
I am trying to refresh tables in Access dbase from Excel.
I am receiving this error:
Run-time error '3709' The connection cannot be used to perform this operation. It is either closed or invalid in this context
Debug points here
Code:
Set adoTbl.ParentCatalog = adoCat
what I am doing wrong?
Full code below
Code:
Option Explicit
Sub RefreshLinks()
'Comments: 1.)Refresh linked tables
' 2.)Set Reference To Microsoft ADO Ext. 2.8 for DDL and Security
'
'Date Developer Action
'---------------------------------------------
'02/01/12 ws Created
[code]...
View 3 Replies
View Related
Jun 18, 2012
Excel 2010. I have a field that is color formatted based on the value of another field, which is calculated.
The conditional formatting does not refresh when the value is recalculated, but when I go away from the tab and come back, it works fine.
Any simple VB code to refresh the conditional formatting upon a workbook change?
View 1 Replies
View Related
Feb 8, 2013
My doubt it's at the final of the code when the condition If IE.Document.URL Like constantsValues.urlLoginData Then is true, it enter into a class an introduce the user and password from a login page AND then the page is REDIRECTED to another page SO when it goes out the IF CONDITION, the values that I had at the beginning in the IE Object are now DIFERENT because I'm now in a new page.
So that's why I put Application.Wait for 30 seconds, and then try to refresh the values of the IE object and get the new ones, but it always get the first values (the values of the login page :S) NO the new ones :S
How can I update the IE object to get the values of the actual page?
Code:
Private Sub runMacro_Click()
Dim constantsValues As CConstants
Set constantsValues = New CConstants
[Code].....
View 1 Replies
View Related
Feb 4, 2013
I am using Excel 2007.
I have some pivot tables that have the data source in another workbook. I didn't create any external data connections. When I created the first pivot table, I just selected the range from that other workbook. Most of the other pivot tables were copied and modified accordingly, so they should have the same pivot cache.
I have created a macro that basically refreshes all data, using the Workbook.RefreshAll command, then copy/pastes values and then deletes the pivots so that I can send it via e-mail without problems. The macro seems to work fine.
The QUESTION relates to the fact that I am afraid VBA won't wait for the refresh to complete before starting to copy/paste values.
The PivotCache.BackgroundQuery = False solution doesn't work. I get 10004 error. Also, there are no data connections visible in the Data > Connections. I guess that, because of this, "Properties" is greyed out in the Data tab and also "Connection Properties" under Pivot Table Tools > Options > Change Source Data is greyed out. This way, I cannot uncheck the "Enable background refresh" box.
I could just tick the "Refresh when opening the file" in Pivot Table options, but if I open the file without first opening the workbook with the raw data, refresh takes very long.
I have tried to measure time difference between the RefreshAll command and a simple Range("C3").Select command. There is a time difference, but I'm still not 100% sure that VBA waits for refresh to complete before continuing.
VB:
Do Until Application.CalculationState = xlDone
VB:
[COLOR=#333333][COLOR=#333333]DoEvents[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]Loop[/COLOR][/COLOR][COLOR=#333333][COLOR=#333333]
[/COLOR][/COLOR]
View 9 Replies
View Related
May 19, 2014
I have some code that runs and loops through each pivot table to refresh it; however, I remmed out the code so that it only loops once - telling it to refresh the first pivot; the thing I'm confused about is that after the code runs, it appears to have refreshed all my pivots. I think it's also important to note the each pivot table is linked to the same data source. (an external ds)
Maybe when this is the case, refresh will always refresh all?
View 5 Replies
View Related
Jul 11, 2012
I am using excel 2007 and when I change cells my formulas do not automatically refresh. I have set it to automatic and the calculations will not refresh. How I can force a calculation. I did the control alt f9 and nothing seems to work. Any macro that can force all calculations.
View 4 Replies
View Related
Jul 28, 2009
I've been putting together an automated spreadsheet that has external data sources to SQL. I have been having some strange problems with it between versions of Excel. Unfortunately, the company's standard is 2003 but quite a lot of the company use 2007. I have been developing this report on 2007, the server that runs the automated version of the spreadsheet runs 2003 (and cannot be upgraded beyond 2003 as it is running Server 2000!).
The template opens fine on my 2007 machine and all development work has been run on my machine including the downloading of data. The VBA script runs something like
refresh datasources ........
View 14 Replies
View Related
May 2, 2007
I have a large model, and it sometimes starts having very strange errors. The workbook sometimes becomes corrupted, giving a:
"Microsoft Excel has encounted a problem and needs to close. We are sorry for the incovenience"
"AppName: excel.exe AppVer: 10.0.6823.0 ModName: kernel32.dll
ModVer: 5.1.2600.2945 Offset: 00012a5b"
error when I try to close the spreadsheet. One of the sheets can have a lot of data (up to 10,000 x 150) and the error will also occur if I try and delete the sheet, or clear the entire sheet. Also, the model runs vba code, which at one point deletes the data sheet and adds a new sheet. However, sometimes the sheets.add functionality stop working, i.e. when the code runs the sheets.add line, nothing happens. I don't know if the errors are related.
View 3 Replies
View Related
Feb 25, 2009
Is there a way to freeze your pivot table format after clicking for a ' Refresh'. I'm getting tired reformatting my column headers to wrap or in the middle etc. I'm working in Excel 2007.
View 6 Replies
View Related
Oct 22, 2012
If a user changes some data in a pivot table and then refreshes the table, the changed values are supposed to revert to the source data, right? This does not happen in my pivot table.
Any changes made in the underlying data get transferred to the pivot table refresh, as expected. But if I change some values in the pivot table, followed by a refresh, then the cells that I changed remain the same unless I happened to modify the corresponding values in the underlying source data too. In other words, the refresh operation does not touch the pivot table except specifically where an update is driven by a change in the underlying data.
This behavior is different from what i remember from, say, excel 97 / 2000.
View 3 Replies
View Related
Feb 14, 2014
I had this code working perfectly. However in the last 24 hours it has started freezing on me. Here is the code:
' This code will dynamically hide or unhide rows based on whether a row contains a transaction
Sub hideRows()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Sheet1.Unprotect Password:="University"
[Code] .....
I call the code into a Worksheet_Change module on a particular sheet (Sheet1). It hides rows between 11 to 1503, but if I innocently select these rows, the spreadsheet freezes on me & I have to kill Excel. I'm guessing I need to add code to counter these do nothing events?
View 1 Replies
View Related
Jun 4, 2008
The spreadsheet is essentially a VBA GUI that validates information entered before writing it to a sheet in the workbook. There is a button on the first sheet that opens the GUI, and when the workbook is first used the GUI opens and runs fine. After adding a few rows using the GUI, saving the workbook, and then reopening it, attempting to open the GUI by clicking on the button will cause Excel to crash (and no errors are given). To compound the problem, it is not possible to find the issue by using the debugger, as the GUI runs fine as soon as VisualBasic is opened. I've tried to narrow it down by using MsgBoxes to find the approximate location where the form crashes, and it seems to happen when the .ListIndex property of a ComboBox is set inside the UserForm_Initialise method. I've played around for days trying to narrow it down further, removing .ListIndex statements as much as possible without breaking the entire thing.
View 2 Replies
View Related
