How do I iterate a collection of Excel columns in C++ using Automation?

Question

I want to do the moral equivalent of the following VBA code:

For Each col In Worksheets("Sheet1").Columns
    # do stuff
Next col

I have generated MFC wrappers for the Excel type library that get me this far (the generated types all derive from COleDispatchDriver:

CApplication app;
app.CreateDispatch( clsid, e );

CWorkbooks  wbks( app.get_Workbooks() );
CWorkbook   book( wbks.Open( filename, /* optional args */ ) );
CRange      cols( app.get_Columns() );
long        numCols = cols.get_Count();

and from there I'm stuck. It looks like I can sort of iterate over cells using Range::get_Item( rowid, colid ), and then get the column from the cell, but I was looking for a more direct translation of the above loop.

(EDIT) Clarification: I don't actually care about the individual cells. My goal is to determine which columns have width 0 (are hidden) and delete them from the worksheet.

Answer 1

I assume that you're trying to traverse all the cells in the spreadsheet. You can get the active worksheet's "all cells" range, and loop through its rows and columns :

CSheets sheets = book.get_WorkSheets();
CWorkSheet sheet = sheets.get_ActiveSheet();
Range cells = sheet.get_Cells();

int nRows = cells.get_Rows().get_Count();
int nCols = cells.get_Columns().get_Count();

for (int i = 0; i <= nRows; i++)
    {
    for (int j = 0; j <= nCols; j++)
        {
        Range cell = cells.get_Item(i+1,j+1);
        //Do stuff with individual cell
        }
     }

EDIT: In response to OP clarification:

This will probably do what you're looking for:

CSheets sheets = book.get_WorkSheets();
CWorkSheet sheet = sheets.get_ActiveSheet();
Range cols= sheet.get_Columns();
int nCols = cols.get_Count();

for (int i = nCols; i > 0; i--)
    {         
    Range topCellOfCol = cells.get_Item(1, i);
    Range entireCol = topCellOfCol.get_EntireColumn();
    if (entireCol.get_Hidden())
         entireCol.Delete( xlShiftToLeft );        
    }

Answer 2

Thanks to Steve, I got most of the way there. It turns out that despite the Excel VBA documentation, Range::Item has different behavior depending on how the range is created; thus:

COleVariant OPTIONAL( (long)DISP_E_PARAMNOTFOUND, VT_ERROR );

Range cols = sheet.get_Columns();
for ( long i = cols.get_Count(); i > 0; --i )
{
    Range col = cols.get_Item( COleVariant( i ), OPTIONAL ) ;
    // ...
}

will iterate through ranges representing the columns, and

Range rows = sheet.get_Rows();
for ( long i = rows.get_Count(); i > 0; --i )
{
    Range row = cols.get_Item( COleVariant( i ),  OPTIONAL );
    // ...
}

will iterate through ranges representing the rows. This is implicit in the VBA examples, but not actually stated in the documentation.