How to Delete Excel Sheet without Warning - MFC

[leonard522]

Hi ~
I am automating Excel by embedding it in my MFC dialog. I need to
delete an excel chart and I have tried to use the app function
SetDisplayAlerts to FALSE. However, this doesn't work for me. I get
an error message "Unable to Set DisplayAlerts property of Application
Class." I have searched online for hours, and all I have found is that
this may not be available when embedding excel into your program.

So my question is, any ideas on getting this to work, or hiding an
error message? Is there an alternative way to delete a worksheet so
that it won't give an error message?
Please help!!
Jen

 

[Tom Ogilvy]

That is the only way I know to suppress the prompt.

Why not clear and hide the sheet. Always using the same sheet if this is a
recurring requirement (unhide it).

 

[leonard522]

I definitely could do it that way, however that causes another problem.

I use CopyFromRecordset to set the data in the spreadsheet with data
from my Access database. The sheet I am deleting is actually a chart
that uses data from a worksheet. If I clear it, and then try
CopyFromRecordset, that function crashes. My only thought is that it
is crashing because the chart has it's source data as the data I am
hiding/setting.

I thought that just deleting the sheet with the chart and then adding
the chart again would be easier. However because of the warning
message, this won't work!

So I think your recommendation is the way I need to go...Any tips on
getting around the crash in CopyFromRecordset?
Thanks!!
Jen

 

[Tom Ogilvy]

If that is in fact the cause, then

Try using the equivalent of

Activesheet.Cells.ClearContents

to clear the cells, rather than Delete.

or delete the series in the chart before deleting the cells, then add them
back in after adding the new data.

 

[Tom Ogilvy]

Or just copy over the old data without deleting or clearing it, then adjust
the series to point to the new data and adjust any residual.

 

[leonard522]

Tom,
I tried clearing the cells, and just copying over them, and it crashed
both times. Deleting the series and then adding it back afterwards
works great though!

Thanks for your help...I really appreciate it!!
Jen