Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Excel charts do not update when using SendKeys
I apologize in advance, because i know there are a lot of similar
posts. None, as far as i can tell , have a definitive answer as to how this was fixed. I have written and addin with a number of custom formulas. A client has a spreadsheet that gets data from these formulas, performs simple calculations on it (like determines a percentage) and uses a range of these values to populate a chart. When i recalculate the spreadsheet, all the formulas are recalculated (I use xlc SendKeys CNTRL-ALT-F9) to calculate the data. However the graph/chart is not refreshed. I believe this is an Excel bug because (1) If i save the spreadsheet and reopen it, then the chart is updated !! (2) If i simply "delete" an empty row in the spreadsheet, the chart is updated !!! (go figure). I think i remember reading somewhere that hiding / deleting rows causes Excel to recalculate or something, but i maybe wrong. Is there a non-kludgy way to force the chart to update? OR Is there a less kludgy way to force the chart to refresh? (than always deleting an empty row, and undeleting it - I'm assuming there is a way to do that programmatically) How has everyone else handled this ? Thank you for your response and time. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Excel charts do not update when using SendKeys
Someone else may have a more definitive answer but...
Searched support.microsoft.com? I just did with the keywords 'excel chart does not update' (w/o the quotes) and got several hits. Of course, I don't know if any applies to your problem. -- Regards, Tushar Mehta http://www.tushar-mehta.com Custom business solutions leveraging a multi-disciplinary approach "nandan" wrote: I apologize in advance, because i know there are a lot of similar posts. None, as far as i can tell , have a definitive answer as to how this was fixed. I have written and addin with a number of custom formulas. A client has a spreadsheet that gets data from these formulas, performs simple calculations on it (like determines a percentage) and uses a range of these values to populate a chart. When i recalculate the spreadsheet, all the formulas are recalculated (I use xlc SendKeys CNTRL-ALT-F9) to calculate the data. However the graph/chart is not refreshed. I believe this is an Excel bug because (1) If i save the spreadsheet and reopen it, then the chart is updated !! (2) If i simply "delete" an empty row in the spreadsheet, the chart is updated !!! (go figure). I think i remember reading somewhere that hiding / deleting rows causes Excel to recalculate or something, but i maybe wrong. Is there a non-kludgy way to force the chart to update? OR Is there a less kludgy way to force the chart to refresh? (than always deleting an empty row, and undeleting it - I'm assuming there is a way to do that programmatically) How has everyone else handled this ? Thank you for your response and time. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Excel charts do not update when using SendKeys
Yup..
I did , and have spent a lot of time searching the google groups as well.. There are tons of hits, but amazingly , none of them have a solution ... At least i haven't found one yet.. Thanks, Nandan On Feb 2, 12:59 pm, Tushar Mehta <tmUnderscore200...@tushar- mehta.SeeOhEm wrote: Someone else may have a more definitive answer but... Searched support.microsoft.com? I just did with the keywords 'excel chart does not update' (w/o the quotes) and got several hits. Of course, I don't know if any applies to your problem. -- Regards, Tushar Mehtahttp://www.tushar-mehta.com Custom business solutions leveraging a multi-disciplinary approach "nandan" wrote: I apologize in advance, because i know there are a lot of similar posts. None, as far as i can tell , have a definitive answer as to how this was fixed. I have written and addin with a number of custom formulas. A client has a spreadsheet that gets data from these formulas, performs simple calculations on it (like determines a percentage) and uses a range of these values to populate a chart. When i recalculate the spreadsheet, all the formulas are recalculated (I use xlc SendKeys CNTRL-ALT-F9) to calculate the data. However the graph/chart is not refreshed. I believe this is an Excel bug because (1) If i save the spreadsheet and reopen it, then the chart is updated !! (2) If i simply "delete" an empty row in the spreadsheet, the chart is updated !!! (go figure). I think i remember reading somewhere that hiding / deleting rows causes Excel to recalculate or something, but i maybe wrong. Is there a non-kludgy way to force the chart to update? OR Is there a less kludgy way to force the chart to refresh? (than always deleting an empty row, and undeleting it - I'm assuming there is a way to do that programmatically) How has everyone else handled this ? Thank you for your response and time.- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Excel charts do not update when using SendKeys
Actually,
Tushar, I did find something... http://support.microsoft.com/kb/821644/en-us XL2002: Charts Do Not Update When Source Cells Are Updated will give this a try... Thanks again.. On Feb 2, 12:59 pm, Tushar Mehta <tmUnderscore200...@tushar- mehta.SeeOhEm wrote: Someone else may have a more definitive answer but... Searched support.microsoft.com? I just did with the keywords 'excel chart does not update' (w/o the quotes) and got several hits. Of course, I don't know if any applies to your problem. -- Regards, Tushar Mehtahttp://www.tushar-mehta.com Custom business solutions leveraging a multi-disciplinary approach "nandan" wrote: I apologize in advance, because i know there are a lot of similar posts. None, as far as i can tell , have a definitive answer as to how this was fixed. I have written and addin with a number of custom formulas. A client has a spreadsheet that gets data from these formulas, performs simple calculations on it (like determines a percentage) and uses a range of these values to populate a chart. When i recalculate the spreadsheet, all the formulas are recalculated (I use xlc SendKeys CNTRL-ALT-F9) to calculate the data. However the graph/chart is not refreshed. I believe this is an Excel bug because (1) If i save the spreadsheet and reopen it, then the chart is updated !! (2) If i simply "delete" an empty row in the spreadsheet, the chart is updated !!! (go figure). I think i remember reading somewhere that hiding / deleting rows causes Excel to recalculate or something, but i maybe wrong. Is there a non-kludgy way to force the chart to update? OR Is there a less kludgy way to force the chart to refresh? (than always deleting an empty row, and undeleting it - I'm assuming there is a way to do that programmatically) How has everyone else handled this ? Thank you for your response and time.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Excel charts do not update when using SendKeys
Too bad.
My functions are already Volatile (at least i call xlfVolatile) and the start of each function. I double checked to make sure. Also this problem is in XL2002, but i still see this happenning in xl2003. Oh well back to the drawing board... On Feb 2, 1:25 pm, "nandan" wrote: Actually, Tushar, I did find something... http://support.microsoft.com/kb/821644/en-us XL2002: Charts Do Not Update When Source Cells Are Updated will give this a try... Thanks again.. On Feb 2, 12:59 pm, Tushar Mehta <tmUnderscore200...@tushar- mehta.SeeOhEm wrote: Someone else may have a more definitive answer but... Searched support.microsoft.com? I just did with the keywords 'excel chart does not update' (w/o the quotes) and got several hits. Of course, I don't know if any applies to your problem. -- Regards, Tushar Mehtahttp://www.tushar-mehta.com Custom business solutions leveraging a multi-disciplinary approach "nandan" wrote: I apologize in advance, because i know there are a lot of similar posts. None, as far as i can tell , have a definitive answer as to how this was fixed. I have written and addin with a number of custom formulas. A client has a spreadsheet that gets data from these formulas, performs simple calculations on it (like determines a percentage) and uses a range of these values to populate a chart. When i recalculate the spreadsheet, all the formulas are recalculated (I use xlc SendKeys CNTRL-ALT-F9) to calculate the data. However the graph/chart is not refreshed. I believe this is an Excel bug because (1) If i save the spreadsheet and reopen it, then the chart is updated !! (2) If i simply "delete" an empty row in the spreadsheet, the chart is updated !!! (go figure). I think i remember reading somewhere that hiding / deleting rows causes Excel to recalculate or something, but i maybe wrong. Is there a non-kludgy way to force the chart to update? OR Is there a less kludgy way to force the chart to refresh? (than always deleting an empty row, and undeleting it - I'm assuming there is a way to do that programmatically) How has everyone else handled this ? Thank you for your response and time.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Excel charts do not update when using SendKeys
Just in case this helps anyone, i overcame this problem by turning
off, screenupdates, deleting a row, and then undoing the operation. In code, int success = getActiveCell(&sheetActiveCell); //turn screen updates off XlfExcel::Instance().Call(xlcEcho,ret,1,XlfOper((b ool)FALSE)); XlfExcel::Instance().Call(xlfGoto,ret,1,XlfOper((s hort)500)); // this row number chosen at random success = XlfExcel::Instance().Call(xlcEditDelete, 0, 1, XlfOper((short)3)); //delete the row - this causes graphs to refresh success = XlfExcel::Instance().Call(xlcUndo, 0, 0);// undo XlfExcel::Instance().Call(xlfGoto,ret, 1,XlfOper(sheetActiveCell));// go back to active cell XlfExcel::Instance().Call(xlcEcho,ret,1,XlfOper((b ool)TRUE)); // turn screen updates back on On Feb 2, 2:50 pm, "nandan" wrote: Too bad. My functions are already Volatile (at least i call xlfVolatile) and the start of each function. I double checked to make sure. Also this problem is in XL2002, but i still see this happenning in xl2003. Oh well back to the drawing board... On Feb 2, 1:25 pm, "nandan" wrote: Actually, Tushar, I did find something... http://support.microsoft.com/kb/821644/en-us XL2002: Charts Do Not Update When Source Cells Are Updated will give this a try... Thanks again.. On Feb 2, 12:59 pm, Tushar Mehta <tmUnderscore200...@tushar- mehta.SeeOhEm wrote: Someone else may have a more definitive answer but... Searched support.microsoft.com? I just did with the keywords 'excel chart does not update' (w/o the quotes) and got several hits. Of course, I don't know if any applies to your problem. -- Regards, Tushar Mehtahttp://www.tushar-mehta.com Custom business solutions leveraging a multi-disciplinary approach "nandan" wrote: I apologize in advance, because i know there are a lot of similar posts. None, as far as i can tell , have a definitive answer as to how this was fixed. I have written and addin with a number of custom formulas. A client has a spreadsheet that gets data from these formulas, performs simple calculations on it (like determines a percentage) and uses a range of these values to populate a chart. When i recalculate the spreadsheet, all the formulas are recalculated (I use xlc SendKeys CNTRL-ALT-F9) to calculate the data. However the graph/chart is not refreshed. I believe this is an Excel bug because (1) If i save the spreadsheet and reopen it, then the chart is updated !! (2) If i simply "delete" an empty row in the spreadsheet, the chart is updated !!! (go figure). I think i remember reading somewhere that hiding / deleting rows causes Excel to recalculate or something, but i maybe wrong. Is there a non-kludgy way to force the chart to update? OR Is there a less kludgy way to force the chart to refresh? (than always deleting an empty row, and undeleting it - I'm assuming there is a way to do that programmatically) How has everyone else handled this ? Thank you for your response and time.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Excel charts do not update when using SendKeys
I too have this issue within Excel 2003, and no amount of research has
enabled me to refresh my charts without closing and reopening my file. This is very cumbersome and time consuming when working with a 1,600 line worksheet driving 80 chart tabs. If anyone has solved this already, I would appreciate advise. I can't believe there isn't a quick key, command, or button to resolve this issue. Even our company tech support is mute on the issue. Thanks in advance. Nandan, hope you've gotten your/our problem solved. Vernouli "nandan" wrote: Just in case this helps anyone, i overcame this problem by turning off, screenupdates, deleting a row, and then undoing the operation. In code, int success = getActiveCell(&sheetActiveCell); //turn screen updates off XlfExcel::Instance().Call(xlcEcho,ret,1,XlfOper((b ool)FALSE)); XlfExcel::Instance().Call(xlfGoto,ret,1,XlfOper((s hort)500)); // this row number chosen at random success = XlfExcel::Instance().Call(xlcEditDelete, 0, 1, XlfOper((short)3)); //delete the row - this causes graphs to refresh success = XlfExcel::Instance().Call(xlcUndo, 0, 0);// undo XlfExcel::Instance().Call(xlfGoto,ret, 1,XlfOper(sheetActiveCell));// go back to active cell XlfExcel::Instance().Call(xlcEcho,ret,1,XlfOper((b ool)TRUE)); // turn screen updates back on On Feb 2, 2:50 pm, "nandan" wrote: Too bad. My functions are already Volatile (at least i call xlfVolatile) and the start of each function. I double checked to make sure. Also this problem is in XL2002, but i still see this happenning in xl2003. Oh well back to the drawing board... On Feb 2, 1:25 pm, "nandan" wrote: Actually, Tushar, I did find something... http://support.microsoft.com/kb/821644/en-us XL2002: Charts Do Not Update When Source Cells Are Updated will give this a try... Thanks again.. On Feb 2, 12:59 pm, Tushar Mehta <tmUnderscore200...@tushar- mehta.SeeOhEm wrote: Someone else may have a more definitive answer but... Searched support.microsoft.com? I just did with the keywords 'excel chart does not update' (w/o the quotes) and got several hits. Of course, I don't know if any applies to your problem. -- Regards, Tushar Mehtahttp://www.tushar-mehta.com Custom business solutions leveraging a multi-disciplinary approach "nandan" wrote: I apologize in advance, because i know there are a lot of similar posts. None, as far as i can tell , have a definitive answer as to how this was fixed. I have written and addin with a number of custom formulas. A client has a spreadsheet that gets data from these formulas, performs simple calculations on it (like determines a percentage) and uses a range of these values to populate a chart. When i recalculate the spreadsheet, all the formulas are recalculated (I use xlc SendKeys CNTRL-ALT-F9) to calculate the data. However the graph/chart is not refreshed. I believe this is an Excel bug because (1) If i save the spreadsheet and reopen it, then the chart is updated !! (2) If i simply "delete" an empty row in the spreadsheet, the chart is updated !!! (go figure). I think i remember reading somewhere that hiding / deleting rows causes Excel to recalculate or something, but i maybe wrong. Is there a non-kludgy way to force the chart to update? OR Is there a less kludgy way to force the chart to refresh? (than always deleting an empty row, and undeleting it - I'm assuming there is a way to do that programmatically) How has everyone else handled this ? Thank you for your response and time.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
Excel charts do not update when using SendKeys
Just a thought. Does changing the Windows zoom setting with screenupdating
on cause the charts to refresh? Bob Flanagan Macro Systems http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Vernouli" wrote in message ... I too have this issue within Excel 2003, and no amount of research has enabled me to refresh my charts without closing and reopening my file. This is very cumbersome and time consuming when working with a 1,600 line worksheet driving 80 chart tabs. If anyone has solved this already, I would appreciate advise. I can't believe there isn't a quick key, command, or button to resolve this issue. Even our company tech support is mute on the issue. Thanks in advance. Nandan, hope you've gotten your/our problem solved. Vernouli "nandan" wrote: Just in case this helps anyone, i overcame this problem by turning off, screenupdates, deleting a row, and then undoing the operation. In code, int success = getActiveCell(&sheetActiveCell); //turn screen updates off XlfExcel::Instance().Call(xlcEcho,ret,1,XlfOper((b ool)FALSE)); XlfExcel::Instance().Call(xlfGoto,ret,1,XlfOper((s hort)500)); // this row number chosen at random success = XlfExcel::Instance().Call(xlcEditDelete, 0, 1, XlfOper((short)3)); //delete the row - this causes graphs to refresh success = XlfExcel::Instance().Call(xlcUndo, 0, 0);// undo XlfExcel::Instance().Call(xlfGoto,ret, 1,XlfOper(sheetActiveCell));// go back to active cell XlfExcel::Instance().Call(xlcEcho,ret,1,XlfOper((b ool)TRUE)); // turn screen updates back on On Feb 2, 2:50 pm, "nandan" wrote: Too bad. My functions are already Volatile (at least i call xlfVolatile) and the start of each function. I double checked to make sure. Also this problem is in XL2002, but i still see this happenning in xl2003. Oh well back to the drawing board... On Feb 2, 1:25 pm, "nandan" wrote: Actually, Tushar, I did find something... http://support.microsoft.com/kb/821644/en-us XL2002: Charts Do Not Update When Source Cells Are Updated will give this a try... Thanks again.. On Feb 2, 12:59 pm, Tushar Mehta <tmUnderscore200...@tushar- mehta.SeeOhEm wrote: Someone else may have a more definitive answer but... Searched support.microsoft.com? I just did with the keywords 'excel chart does not update' (w/o the quotes) and got several hits. Of course, I don't know if any applies to your problem. -- Regards, Tushar Mehtahttp://www.tushar-mehta.com Custom business solutions leveraging a multi-disciplinary approach "nandan" wrote: I apologize in advance, because i know there are a lot of similar posts. None, as far as i can tell , have a definitive answer as to how this was fixed. I have written and addin with a number of custom formulas. A client has a spreadsheet that gets data from these formulas, performs simple calculations on it (like determines a percentage) and uses a range of these values to populate a chart. When i recalculate the spreadsheet, all the formulas are recalculated (I use xlc SendKeys CNTRL-ALT-F9) to calculate the data. However the graph/chart is not refreshed. I believe this is an Excel bug because (1) If i save the spreadsheet and reopen it, then the chart is updated !! (2) If i simply "delete" an empty row in the spreadsheet, the chart is updated !!! (go figure). I think i remember reading somewhere that hiding / deleting rows causes Excel to recalculate or something, but i maybe wrong. Is there a non-kludgy way to force the chart to update? OR Is there a less kludgy way to force the chart to refresh? (than always deleting an empty row, and undeleting it - I'm assuming there is a way to do that programmatically) How has everyone else handled this ? Thank you for your response and time.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
Excel charts do not update when using SendKeys
In my case,
I am writing an addin, and our users noticed this problem. It was unrealistic to expect every user to close and reopen their spreadsheet, so now everytime the user recalculates , i catch the xlcOnrecalc event, and then delete a row and undo. This seems to have overcome the problem. If it is annoying to you then could probably write a macro that does this. i.e. use a VBA macro that turns off screen updating, then deletes a row and then does and undo. Or you could of course do that manually as well. Just delete a row and undo. On Apr 4, 9:54 am, Vernouli wrote: I too have this issue within Excel 2003, and no amount of research has enabled me to refresh my charts without closing and reopening my file. This is very cumbersome and time consuming when working with a 1,600 line worksheet driving 80 chart tabs. If anyone has solved this already, I would appreciate advise. I can't believe there isn't a quick key, command, or button to resolve this issue. Even our company tech support is mute on the issue. Thanks in advance. Nandan, hope you've gotten your/our problem solved. Vernouli "nandan" wrote: Just in case this helps anyone, i overcame this problem by turning off, screenupdates, deleting a row, and then undoing the operation. In code, int success = getActiveCell(&sheetActiveCell); //turn screen updates off XlfExcel::Instance().Call(xlcEcho,ret,1,XlfOper((b ool)FALSE)); XlfExcel::Instance().Call(xlfGoto,ret,1,XlfOper((s hort)500)); // this row number chosen at random success = XlfExcel::Instance().Call(xlcEditDelete, 0, 1, XlfOper((short)3)); //delete the row - this causes graphs to refresh success = XlfExcel::Instance().Call(xlcUndo, 0, 0);// undo XlfExcel::Instance().Call(xlfGoto,ret, 1,XlfOper(sheetActiveCell));// go back to active cell XlfExcel::Instance().Call(xlcEcho,ret,1,XlfOper((b ool)TRUE)); // turn screen updates back on On Feb 2, 2:50 pm, "nandan" wrote: Too bad. My functions are already Volatile (at least i call xlfVolatile) and the start of each function. I double checked to make sure. Also this problem is in XL2002, but i still see this happenning in xl2003. Oh well back to the drawing board... On Feb 2, 1:25 pm, "nandan" wrote: Actually, Tushar, I did find something... http://support.microsoft.com/kb/821644/en-us XL2002: Charts Do Not Update When Source Cells Are Updated will give this a try... Thanks again.. On Feb 2, 12:59 pm, Tushar Mehta <tmUnderscore200...@tushar- mehta.SeeOhEm wrote: Someone else may have a more definitive answer but... Searched support.microsoft.com? I just did with the keywords 'excel chart does not update' (w/o the quotes) and got several hits. Of course, I don't know if any applies to your problem. -- Regards, Tushar Mehtahttp://www.tushar-mehta.com Custom business solutions leveraging a multi-disciplinary approach "nandan" wrote: I apologize in advance, because i know there are a lot of similar posts. None, as far as i can tell , have a definitive answer as to how this was fixed. I have written and addin with a number of custom formulas. A client has a spreadsheet that gets data from these formulas, performs simple calculations on it (like determines a percentage) and uses a range of these values to populate a chart. When i recalculate the spreadsheet, all the formulas are recalculated (I use xlc SendKeys CNTRL-ALT-F9) to calculate the data. However the graph/chart is not refreshed. I believe this is an Excel bug because (1) If i save the spreadsheet and reopen it, then the chart is updated !! (2) If i simply "delete" an empty row in the spreadsheet, the chart is updated !!! (go figure). I think i remember reading somewhere that hiding / deleting rows causes Excel to recalculate or something, but i maybe wrong. Is there a non-kludgy way to force the chart to update? OR Is there a less kludgy way to force the chart to refresh? (than always deleting an empty row, and undeleting it - I'm assuming there is a way to do that programmatically) How has everyone else handled this ? Thank you for your response and time.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update data in Excel | New Users to Excel | |||
Update charts | Charts and Charting in Excel | |||
Excel Charts do not update when Workbook recalculated | Charts and Charting in Excel | |||
Update Excel table in Word automatically from data in Excel | Excel Discussion (Misc queries) | |||
Excel Charts Linked to Spreadsheets | Charts and Charting in Excel |