Excel charts do not update when using SendKeys

[nandan]

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.

 

[Tushar Mehta]

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]

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

 

[nandan]

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..

 

[nandan]

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...

 

[nandan]

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((bool)FALSE));
XlfExcel::Instance().Call(xlfGoto,ret,1,XlfOper((short)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((bool)TRUE)); //
turn screen updates back on

 

[Vernouli]

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

 

[Bob Flanagan]

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

 

[nandan]

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.