View Single Post
  #9   Report Post  
Posted to microsoft.public.excel.charting
nandan nandan is offline
external usenet poster
 
Posts: 11
Default 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 -