PROCEDURE Delete_Excel_Sheet
LPARAMETERS tcFile , tcSheet_to_Delete , tnFlag , tlShowBook ,tlShowInfo
*!* Method_Name Delete_Excel_Sheet
*!* Propósito Quitar una Hoja de un libro Excel ®
*!* Respuesta boolean: si quitó la hoja del libro.
*!*
*!* tcFile: ADDBS(path)+archivo+extension
*!* tcSheet_to_ Delete Nombre de la hoja a borrar
*!* tnFlag * Default= Maximized
*!* * tnFlag = 0 Maximized (-4137)
*!* * tnFlag = 1 Minimezed (-4140)
*!* * tnFlag = 2 Normal (-4143)
*!* tlShowBook Si muestra el libro de Microsoft Excel ®
*!* tlShowInfo Si muestra el archivo log
*!* -------------------------------------------------------------
tnFlag=EVL(tnFlag,0)
lnWinSta = ICASE(tnFlag=1,-4140,;
tnFlag=2,-4143,;
-4137)
LOCAL i, ;
loFso,;
lnSheets ,;
loExcel as Object ,;
lcString,;
lcDeletedSheet,;
lcFileInfo
lcFileInfo = ADDBS(FULLPATH(""))+"ExcelOpen.txt"
lcString = ""
lcDeletedSheet = ""
loExcel = null
TRY
LOCAL loErr as Exception
loFso=NEWOBJECT("Scripting.FileSystemObject")
IF NOT EMPTY(tcFile) AND loFso.FileExists(tcFile)
*Determinar si hay una instancia de Excel creada.
*Puede no existir por lo que se ignora el error
TRY
LOCAL LoExisOb as Exception
loExcel = GETOBJECT(, "Excel.Application")
CATCH TO loexisOb
ENDTRY
TRY
LOCAL loex as Exception
IF VARTYPE(loExcel)#"O"
loExcel = CREATEOBJECT("Excel.Application")
ENDIF
loExcel.Workbooks.Open(tcFile)
lnSheet=loExcel.Sheets.Count
* Informe
lcString = m.lcString ;
+"<ExcelOpen>"+CHR(13);
+CHR(9)+"<File>"+tcFile+"</File>" + CHR(13) ;
+CHR(9)+"<Sheets>"+CHR(13)
* Busca y elmina la hoja 'tcSheet_To_Delete'
FOR i= m.lnSheet TO 1 STEP -1
lcString = m.lcString ;
+REPLICATE(CHR(9),2) + loExcel.Sheets(m.i).name + CHR(13)
IF LOWER(loExcel.Sheets(i).name) == LOWER(tcSheet_To_Delete)
lcDeletedSheet=loExcel.Sheets(m.i).name
loExcel.sheets(i).Delete
ENDIF
NEXT
* Informe
lcString = m.lcString ;
+CHR(9)+"</Sheets>"+CHR(13)+CHR(13) ;
+CHR(9)+"<Deleted>"+m.lcDeletedSheet+"</Deleted>"+CHR(13)+CHR(13) ;
+"</ExcelOpen>"+CHR(13)
* Si borró alguna hoja, grabar el libro
IF NOT EMPTY(m.lcDeletedSheet)
loExcel.Save()
ENDIF
IF tlShowBook
loExcel.WindowState= m.lnWinSta
ENDIF
loExcel.visible=tlShowBook
CATCH TO loex
loex.UserValue=PROGRAM()+ CHR(13);
+IIF(VARTYPE(m.loExcel)#"O","Microsoft Excel ® no está instalado","")
ShowError(loEx) && Mostrar error
FINALLY
ENDTRY
ELSE
lcString="No se encontró el archivo "+tcFile + CHR(13)
ENDIF
CATCH TO loErr
loErr.UserValue=PROGRAM()
ShowError(LoErr) && Mostrar error
FINALLY
loFso=null
IF !EMPTY(lcString)
STRTOFILE(m.lcString,m.lcFileInfo)
IF tlShowInfo
MODIFY FILE &lcFileInfo NOEDIT NOWAIT IN SCREEN
ENDIF
ENDIF
ENDTRY
RETURN !EMPTY(lcDeletedSheet)
PROCEDURE ShowError
*****************************
LPARAMETERS toExcep,tlNotShow,tcCaption
tcCaption=EVL(tcCaption,"Mensaje de Error")
LOCAL lcMens
lcMens="Fecha "+TRANSFORM(DATETIME());
+CHR(13)+"Mensaje: "+toExcep.message;
+CHR(13)+"ErrorNo: "+TRANSFORM(toExcep.Errorno);
+CHR(13)+"StackLevel: " + transform(toExcep.StackLevel);
+CHR(13)+"Llamada: "+toExcep.Uservalue
IF _vfp.StartMode=0
lcMens=lcMens+CHR(13)+"linea "+TRANSFORM(toExcep.lineno)
ENDIF
STRTOFILE(lcMens+CHR(13)+CHR(13),"MyErrors.log",1)
lcMens="Se ha producido un error:"+chr(13)+lcMens+chr(13)
IF !tlNotShow
MESSAGEBOX(lcMens,0,tcCaption)
ENDIF