Delphi, What do I do about "no GetEnumerator present" error when using a for loop over Excel Interop Worksheets collection?

Question

Hello,

I'm trying to write a Delphi program that will loop through each worksheet in an Excel file and format some cells. I'm receiving an error while trying to use the for-in loop over the Workbook.Worksheets collection, though. The error is specifically:

[DCC Error] Office.pas(36): E2431 for-in statement cannot operate on collection type 'Sheets' because 'Sheets' does not contain a member for 'GetEnumerator', or it is inaccessible

The line of code this occurs for is:

for Worksheet in Workbook.Worksheets do

The definition of Worksheet and Workbook is as follows:

var ExcelApp: ExcelApplication;
var Workbook: ExcelWorkbook;
var Worksheet: ExcelWorksheet;

I'm porting this code to Delphi from C#, in which it works. Does anyone know why I'd be getting this GetEnumerator error? I'm using the Office 2007 Excel Interop file and Embarcadero® Delphi® 2010 Version 14.0.3593.25826.

Thanks in advance.

Answer 1

EDIT: Based on comments, the GetEnumerator method exists for the object, so this solution is likely incorrect.

It's been a while since I've used Delphi, but my guess based on the error is that the ExcelWorksheet collection is not enumerated, which would mean doing a for/each style loop would not work and you'd need to use a C-like for loop:

 For  i := 1 to Length(Workbook.Worksheets) do
  Worksheet = Workbook.Worksheets[i];
end;

Answer 2

I'm not sure how C# handles enumerable collections, but for Delphi, it looks for a method on the collection called GetEnumerator, which returns an enumerator. The enumerator must be a data structure that contains at least the following two members:

public
  function MoveNext: boolean;
  property Current: <some type> read <some read method>;
end;

If Delphi says 'Sheets' does not contain a member for 'GetEnumerator', or it is inaccessible, then that means exactly what it says. Either there's no GetEnumerator on Sheets, or it's not a public method. What does the definition of Sheets look like?

Answer 3

C# handles enumerators similar to Delphi. There is an IEnumerable interface that has two methods: MoveNext, and Reset; and also one property called Current.

For ExcelWorkBook.Sheets property, you should take note that although MSDN documentation says it has a GetEnumerator method, this class's definition in ExcelXP unit provided by Delphi does not provide such a method. So maybe you have to import a newer version of its type library into Delphi.