fetch excel comments from all sheets

[EDN Admin]

I have written the following code to fetch cells number having excel comments in a active sheet. I would like to know how can i loop through all the sheets. Currently using the code i am able to get only last sheet containing data information. how to fetch
the first sheet and loop through all the available sheets.
DWORD WINAPI AutomateExcelByImport()<br/>
{<br/>
CoInitialize(NULL);<br/>
try<br/>
{<br/>
Excel::_ApplicationPtr spXlApp;<br/>
HRESULT hr = spXlApp.CreateInstance(__uuidof(Excel::Application));<br/>
if (FAILED(hr))<br/>
{<br/>
wprintf(L"CreateInstance failed w/err 0x%08lxn", hr);<br/>
return 1;<br/>
}<br/>
<br/>
Excel::WorkbooksPtr spXlBooks = spXlApp->Workbooks;<br/>
Excel::_WorkbookPtr spXlBook = spXlBooks->Open("C:\Users\Rajitha\Desktop\excel\e.xlsx");<br/>
<br/>
Excel::_WorkbookPtr books = spXlApp->ActiveWorkbook;<br/>
Excel::SheetsPtr wSheet= books->Worksheets;<br/>
long sheetCount = wSheet->Count; //Number of sheets in the current spreadsheet<br/>
<br/>
//loop through all the sheets<br/>
for(long p=1; p<=sheetCount; p++) // need to change accordingly<br/>
{<br/>
Excel::_WorksheetPtr spXlSheet = spXlBook->ActiveSheet; //need to change<br/>
<br/>
/*Excel::SheetsPtr g = spXlBook->GetSheets();*/<br/>
<br/>
//Get the name of the sheet<br/>
_bstr_t sheet = spXlSheet->GetName();<br/>
printf("Sheetname=%sn",LPCSTR(sheet));<br/>
<br/>
Excel::RangePtr usedrange = spXlSheet->UsedRange;<br/>
Excel::RangePtr cell = usedrange->SpecialCells(Excel::xlCellTypeComments);<br/>
<br/>
Excel::RangePtr row = usedrange->Rows;<br/>
long rowCount = row->Count;<br/>
Excel::RangePtr col = usedrange->Columns;<br/>
long colCount = col->Count;<br/>
<br/>
//Loop to retreive all the comments from a sheet<br/>
for(long i = 1; i <= rowCount; i++)<br/>
{<br/>
for(long j = 1; j <= colCount; j++)<br/>
{<br/>
Excel::RangePtr cell = usedrange->GetItem(i,j); //Get every cell<br/>
Excel::CommentPtr comm = cell->Comment;<br/>
if(comm!=NULL)<br/>
{<br/>
//Cell number where comment is located<br/>
long r = cell->Row;<br/>
long c = cell->Column;<br/>
<br/>
/////////ASCII conversion of column value////////////<br/>
int dividend = c;<br/>
_bstr_t colName = "";<br/>
int modulo;<br/>
while (dividend > 0)<br/>
{<br/>
modulo = (dividend - 1) % 26;<br/>
colName = (65 + modulo); //colname contains ascii value of column name // need to convert as related string(char)<br/>
dividend = (int)((dividend - modulo) / 26);<br/>
}<br/>
<br/>
printf("Row = %ld-------", r);<br/>
printf("Column = %ld------", c);<br/>
<br/>
//Retrieves the comment<br/>
_bstr_t text = comm->Text();<br/>
printf("Comment = %s n", (LPCSTR)text);<br/>
text.Detach();<br/>
}<br/>
}<br/>
}<br/>
row->Release();<br/>
col->Release();<br/>
cell->Release();<br/>
usedrange->Release();<br/>
sheet.Detach();<br/>
spXlSheet.Release();<br/>
}<br/>
<br/>
wSheet->Release();<br/>
books->Close();<br/>
spXlBook->Close();<br/>
spXlBooks->Close();<br/>
_putws(L"Quit the Excel application");<br/>
spXlApp->Quit();<br/>
<br/>
}<br/>
catch (_com_error &err)<br/>
{<br/>
wprintf(L"Excel throws the error: %sn", err.ErrorMessage());<br/>
wprintf(L"Description: %sn", (LPCWSTR) err.Description());<br/>
}<br/>
<br/>
// Uninitialize COM for this thread.<br/>
CoUninitialize();<br/>
<br/>
return 0;<br/>
}<br/>
<br/>
<hr class="sig saikalyan

View the full article