Using Automation to get_Text from Excel. If cell too narrow, get #####. How can I avoid that?

Question

I'm using Automation to get_Text from an Excel worksheet. I do this because I need the formatted value (getting the value of the cell doesn't apply any formatting). If the column the cell is in is too narrow, I get "#####" the same was I would if I were to look at the spreadsheet via Excel. How can I avoid that?

EDIT:

Here is the relevant code:

// Return the (string) value of a cell
HRESULT CDialogImport::GetCellValue(IRange *irange, int irow, int icol, CString &cstrValue)
{
// Get dispatch interface for the cell at irow,icol
COleVariant vCell;
HRESULT hr = AutoWrap(
     DISPATCH_PROPERTYGET, 
     &vCell, 
     irange, 
     L"Item", 
     2,
     COleVariant((short)(icol+1)), 
     COleVariant((short)(irow+1)));
if (FAILED(hr)) return hr;

// Use the dispatch interface to get the value of the cell
COleVariant result;
hr = AutoWrap(
    DISPATCH_PROPERTYGET, 
    &result, 
    vCell.pdispVal, 
    L"Text", 
    0);
if (SUCCEEDED(hr))
 {
 cstrValue = result; 
 }

return hr;
}

Answer 1

You should be able to get and set the column width (rangeobject.ColumnWidth) - increasing the width before grabbing the text should do the trick.

Answer 2

The IRange interface provides an AutoFit() method.

According to the documentation, calling this would make columns wide enough to fit their contents. (It's the .NET interop documentation, but I expect no differences here)

Be aware that (emphasis mine):

The expression must be a row or a range of rows, or a column or a range of columns. Otherwise, this method generates an error.