Import XLS Sheet

[4notrump]

Hi:
I'm attempting to use the following code to import data to foxpro 7 from Excel.

cSpreadSheet='myspread.xls'
cSheet='C12'
IMPORT FROM (cSpreadSheet) TYPE XL8 SHEET (cSheet)

It works !

I can check to make sure there is a "myspread.xls" with file("myspread.xls&quot and reject if the file is not there.

The problem is: if the SHEET C12 is not in myspread.xls it goes bonkers and the trouble starts.

How can I programatically A) extract the sheets that are in a spreadsheet, or B) verify that a sheet is in the spreadsheet or B) detect and recover from the error.

TQ ... Wayne

PS: I'm relatively new to VFP although I have a background in VP 2.6 and Clipper.

 

[DEDMOD]

I'm far from an expert on Excel Automation but it appears there's a .sheets collection and a count property for it. So if you use code like

LOCAL FoundSheet, n
FoundSheet = .F.
SheetCount = cSpreadSheet.sheets.count
FOR n = 1 to SheetCount
   IF ALLTRIM(cSpreadSheet.sheets(n).name) = "C12"
      FoundSheet = .T.
      RETURN FoundSheet
   ENDIF
ENDFOR
RETURN FoundSheet

in a procedure, it ought to let you know if your sheet exists or not. Note, I haven't tested this yet, so I make no guarantees

 

[4notrump]

Thanx for responding.

I was hoping for a one line fix but your idea inspired me AND here's what I came up with: a test program and a function
***********************************************************
*
CLEAR

cFindXLS="6CBREAKDOWNOCT26.xls" && spreadsheet
* get other location stuff
cFindXLS=SYS(5)+SYS(2003)+'\'+cFindXLS
cFindSheet="C6" && I think its there

? "Searching for "+cFindXLS+" and Sheet: "+cFindSheet
? IIF(chk4xlsht(cFindXLS,cFindSheet),"FOUND IT","SORRY&quot
WAIT WINDOW "NEXT VICTIM"

CLEAR
cFindSheet="C99" && should not be found
? "Searching for "+cFindXLS+" and Sheet: "+cFindSheet
? IIF(chk4xlsht(cFindXLS,cFindSheet),"FOUND IT","SORRY&quot
WAIT WINDOW "LAST TEST COMING UP"

CLEAR
cFindSheet="C5" && should be found
cFindXLS="WFBT26.xls" && no way its there
? "Searching for "+cFindXLS+" and Sheet: "+cFindSheet
? IIF(chk4xlsht(cFindXLS,cFindSheet),"FOUND IT","SORRY&quot
WAIT WINDOW ("END OF TEST&quot
clear
CLEAR all
RELEASE ALL
return && end of test program



FUNCTION chk4xlsht
PARAMETERS cxls,csht
* checks for a sheet within an Excel Spreadsheet
* cxls - full spreadsheet name
* csht - full sheet within spreadsheet
LOCAL FoundSheet, n

IF !FILE(cxls)
WAIT WINDOW "NO SUCH SPREADSHEET - Bozo"
RELEASE oX
RETURN FoundSheet
ENDIF

IF VARTYPE(Ox)<>&quot;O&quot; && check for object
RELEASE oX && dump it if it is there
PUBLIC oX
oX=CREATEOBJECT(&quot;EXCEL.APPLICATION&quot
endif
oX.WORKBOOKS.OPEN(cXLS)

FoundSheet = .F. && set up default return

* get sheet count
SheetCount = oX.sheets.count

* go searching for sheet name
FOR n = 1 to SheetCount
IF UPPER(ALLTRIM(oX.sheets.name)) = UPPER(cSht)
FoundSheet = .T.
oX.WORKBOOKS.close() && Quit EXCEL
RELEASE oX
RETURN FoundSheet && YES - FOUND IT
ENDIF
ENDFOR

oX.WORKBOOKS.close() && Quit EXCEL
RELEASE oX
RETURN FoundSheet && Logical Option 2 -- sorry

***********************************************************

Now to make it work in production !!

TQ ...Wayne

 

[DEDMOD]

Good for you. Hope it all works out. Let us know what happens.

Dave Dardinger