D
Dave Calkins
We're using VS2005 with native C++ MFC code and OLE Automation to integrate
with MS Excel. We're using the OLE Automation interface provided by Excel to
create a new workbook, add content, then save it. This allows us to export
our report data to Excel for our end users.
Users that have upgraded to Office 2007 report that when trying to use our
Excel export command, the app appears to hang and they have to then terminate
it. Actually, its not hanging, what's happening is Excel is popping a dialog
and sometimes that dialog isn't noticed by the user (it can be behind another
window, etc).
The dialog being shown by Excel is the "Microsoft Office Excel -
Compatibility Checker" shown in the screenshot linked to below.
http://i831.photobucket.com/albums/zz236/bytebucket/excel-compat-checker-dlg.png
This dialog indicates that, "The following features in this workbook are not
supported by earlier versions of excel....". Specifically, "Minor loss of
fidelity, Some cells or styles in this workbook contain formatting that is
not supported by the selected format...".
Now, clicking Continue allows the file to save (if the user knows to look
for this dialog and doesn't just assume the app is hung), and the resultant
file is fine, all the formatting is there and correct.
How can we avoid this dialog? It gives users the impression something is
wrong when, in fact, nothing is wrong and the file saves just fine, but its
not obvious how to do this since the dialog doesn't always appear on top of
other windows.
Here are more details as to how we're interacting with Excel.
- We're using COleDispatchDriver and calling CreateDispatch with a Prog ID
of "Excel.Application"
- To create a new workbook, we're getting the Workbooks collection and
invoking the Add method
- We then get the Range object (workbook -> worksheets -> etc)
- To put text in a cell, we're using the Value2 property
- To set the background color of a cell, we're setting the Interior.Color
property. This is the one that seems to be causing the dialog. If we
comment this out and don't set the background color, the dialog goes away
- We finally save the file using the below to an ".xls" file
m_workbook.Invoke(_T("SaveAs"),12,
&VAR_CSTR(file), /* Filename */
&VAR_ENUM(Excel::xlWorkbookNormal), /* FileFormat */
&VAR_EMPTY, /* Password */
&VAR_EMPTY, /* WriteResPassword */
&VAR_EMPTY, /* ReadOnlyRecommended */
&VAR_EMPTY, /* CreateBackup */
&VAR_ENUM(Excel::xlNoChange), /* AccessMode */
&VAR_EMPTY, /* ConflictResolution */
&VAR_BOOL(FALSE), /* AddToMru */
&VAR_EMPTY, /* TextCodePage */
&VAR_EMPTY, /* TextVisualLayout */
&VAR_EMPTY /* Local */
);
As I mentioned, commenting out the cell background color gets rid of the
dialog. So for some reason thats triggering it.
Also, I notice we're writing to an ".xls" file not an ".xlsx" file.
with MS Excel. We're using the OLE Automation interface provided by Excel to
create a new workbook, add content, then save it. This allows us to export
our report data to Excel for our end users.
Users that have upgraded to Office 2007 report that when trying to use our
Excel export command, the app appears to hang and they have to then terminate
it. Actually, its not hanging, what's happening is Excel is popping a dialog
and sometimes that dialog isn't noticed by the user (it can be behind another
window, etc).
The dialog being shown by Excel is the "Microsoft Office Excel -
Compatibility Checker" shown in the screenshot linked to below.
http://i831.photobucket.com/albums/zz236/bytebucket/excel-compat-checker-dlg.png
This dialog indicates that, "The following features in this workbook are not
supported by earlier versions of excel....". Specifically, "Minor loss of
fidelity, Some cells or styles in this workbook contain formatting that is
not supported by the selected format...".
Now, clicking Continue allows the file to save (if the user knows to look
for this dialog and doesn't just assume the app is hung), and the resultant
file is fine, all the formatting is there and correct.
How can we avoid this dialog? It gives users the impression something is
wrong when, in fact, nothing is wrong and the file saves just fine, but its
not obvious how to do this since the dialog doesn't always appear on top of
other windows.
Here are more details as to how we're interacting with Excel.
- We're using COleDispatchDriver and calling CreateDispatch with a Prog ID
of "Excel.Application"
- To create a new workbook, we're getting the Workbooks collection and
invoking the Add method
- We then get the Range object (workbook -> worksheets -> etc)
- To put text in a cell, we're using the Value2 property
- To set the background color of a cell, we're setting the Interior.Color
property. This is the one that seems to be causing the dialog. If we
comment this out and don't set the background color, the dialog goes away
- We finally save the file using the below to an ".xls" file
m_workbook.Invoke(_T("SaveAs"),12,
&VAR_CSTR(file), /* Filename */
&VAR_ENUM(Excel::xlWorkbookNormal), /* FileFormat */
&VAR_EMPTY, /* Password */
&VAR_EMPTY, /* WriteResPassword */
&VAR_EMPTY, /* ReadOnlyRecommended */
&VAR_EMPTY, /* CreateBackup */
&VAR_ENUM(Excel::xlNoChange), /* AccessMode */
&VAR_EMPTY, /* ConflictResolution */
&VAR_BOOL(FALSE), /* AddToMru */
&VAR_EMPTY, /* TextCodePage */
&VAR_EMPTY, /* TextVisualLayout */
&VAR_EMPTY /* Local */
);
As I mentioned, commenting out the cell background color gets rid of the
dialog. So for some reason thats triggering it.
Also, I notice we're writing to an ".xls" file not an ".xlsx" file.