how to cycle through sheets of excel workbook.

[Nifrabar]

Hi.
I got an excel workbook with about 60 sheets.
On every sheet there is same kind of data at same cells per sheet.
I like to retrieve that data and put it in a vfp-table.

I know how to create an excel object. But don't know the syntax for cycling through the (I call it) sheets-collection to retrieve e.g. the value of cell A12.

Any suggestion?

-Bart

 

[TamarGranor]

Something long the lines of:

FOR EACH oSheet in oXL.ActiveWorkbook
uValue = oSheet.Cells("A12").Value
* do something with it
ENDFOR

Tamar

 

[JRB-Bldr]

Here are some code snippets that I am using for a client to look at the name of each individual worksheet and then look on that chosen worksheet for a specific cell value.

tmpsheet = CREATEOBJECT('excel.Application')
oExcel = tmpsheet.APPLICATION

mlMonthSheet = .F.
mnMaxWeek = 0
mnMaxSheet = oExcel.ActiveWorkbook.Sheets.COUNT
FOR SheetCntr = 1 TO mnMaxSheet
   mcSheetName = oExcel.ActiveWorkbook.Sheets(SheetCntr).NAME

   IF mcSheetName = mcThisMonthSheet
      mlMonthSheet = .T.
      EXIT
   ENDIF
ENDFOR

* --- Select Desired Worksheet ---
oExcel.Sheets(mcThisMonthSheet).SELECT
xlSheet = oExcel.activesheet

* --- Find Value of 'NAME' Cell on Worksheet ---
mNameValue = xlSheet.cells(mnRowCntr,mnNameCol).VALUE

You should be able to modify the code to do whatever you want on a sheet-by-sheet manner.

Good Luck,
JRB-Bldr

 

[Nifrabar]

Tamar and JRB,
Both thanks for yr help.
It gives me enough 'source'code to proceed with my app.
-Bart