Unable to Delete Sheet in Excel File

[dutch]

I've a problem with delete method in excel. I would like to delete a sheet with name "DATA".
it found this sheet in xls file but after this method oExcel:WorkSheets( n ):Delete(). The DATA sheet is still exist in excel file. I don't know what does it wrong?

Regards,
Dutch

Code: Select all  Expand view

#include 'fivewin.ch'

FUNCTION MAIN()
   LOCAL oExcel, oSheet
   LOCAL nRow, nSheets, n
   LOCAL nCounter, nStart, nSeconds, nSecOle, nSecClip, nSecArray
   LOCAL cMemo, cData, aData

   set century on
   set epoch to 1950

   oExcel = CREATEOBJECT( "Excel.Application" )
   oExcel:WorkBooks:Open("d:\fwh\samples\testxls.xls")
   nSheets := oExcel:Sheets:Count()  
   for n := 1 to nSheets
        if upper( oExcel:WorkSheets( n ):Name )=="DATA"
           oExcel:WorkSheets( n ):Delete()
           msginfo('delete')
        end
   next

   oExcel:Visible := .T.
   oExcel:Quit()

return nil
 

[Enrico Maria Giordano]

Code: Select all  Expand view

nSheets := oExcel:Sheets:Count()

Try

Code: Select all  Expand view

nSheets := oExcel:WorkSheets:Count()

EMG

[dutch]

Dear EMG,

oExcel:Sheets:Count() is not a problem, it works fine. oExcel:WorkSheets(n):name is work correctly too. But the oExcel:Worksheets:Delete() do nothing. I don't know why.

Thx EMG

Code: Select all  Expand view

nSheets := oExcel:Sheets:Count()

Try

Code: Select all  Expand view

nSheets := oExcel:WorkSheets:Count()

EMG

[Enrico Maria Giordano]

Replace

Code: Select all  Expand view

oExcel:WorkSheets( n ):Delete()
msginfo('delete')

with

Code: Select all  Expand view

oExcel:WorkSheets( n ):Delete()
msginfo('delete')
exit

It works fine here.

EMG

[hmpaquito]

Dutch,

You need to use :SaveAs() method for to obtain permanent changes on your excel workbook


Regards

[nageswaragunupudi]

I've a problem with delete method in excel. I would like to delete a sheet with name "DATA".
it found this sheet in xls file but after this method oExcel:WorkSheets( n ):Delete(). The DATA sheet is still exist in excel file.

Please insert the statement

Code: Select all  Expand view

oExcel:DisplayAlerts := .f.

before deleting the sheet.

I don't know what does it wrong?

If the sheet is empty, delete works without any problem.
But when the sheet contains some data, Excel displays an YesNo alert to the user to confirm deletion.
You could not see this alert because Excel is still not visible. In such a case, excel simply assumes that the user did not accept the deletion. In case you made excel visible earlier to calling Delete() method, you would have seen the dialog.

By assigning .f. to the property DisplayAlerts, we asked Excel to execute the method without waiting for confirmation.

[dutch]

Dear All,

Thank you so much for your kindness help. All of your idea are correct.
@EMG, I've changed the do while loop instead of for..next,
@hmpaquito, I've inserted oExcel:Save() at the end before :quit()
@Rao, Your statement is True, the DATA sheet has some data. The DisplayAlerts must required.

Code: Select all  Expand view

#include 'fivewin.ch'
#define GTI_CLIPBOARDDATA 15

FUNCTION MAIN()
   LOCAL oExcel, oSheet
   LOCAL nRow, nSheets, n
   LOCAL nCounter, nStart, nSeconds, nSecOle, nSecClip, nSecArray
   LOCAL cMemo, cData, aData

   set century on
   set epoch to 1950

   oExcel = CREATEOBJECT( "Excel.Application" )
   
   oExcel:WorkBooks:Open("d:\fwh\samples\testxls.xls")
   oExcel:DisplayAlerts := .F.
   oExcel:Visible := .F.
   nSheets := oExcel:Sheets:Count()  
   n := 1
   do while n <= nSheets
        if upper( oExcel:WorkSheets( n ):Name )=="DATA"
           oExcel:WorkSheets( n ):Delete()
           exit
        end
        n++
   end
   oExcel:Save()
   oExcel:Quit()
 

Replace

Code: Select all  Expand view

oExcel:WorkSheets( n ):Delete()
msginfo('delete')

with

Code: Select all  Expand view

oExcel:WorkSheets( n ):Delete()
msginfo('delete')
exit

It works fine here.

EMG

Dutch,

You need to use :SaveAs() method for to obtain permanent changes on your excel workbook


Regards

I've a problem with delete method in excel. I would like to delete a sheet with name "DATA".
it found this sheet in xls file but after this method oExcel:WorkSheets( n ):Delete(). The DATA sheet is still exist in excel file.

Please insert the statement

Code: Select all  Expand view

oExcel:DisplayAlerts := .f.

before deleting the sheet.

I don't know what does it wrong?

If the sheet is empty, delete works without any problem.
But when the sheet contains some data, Excel displays an YesNo alert to the user to confirm deletion.
You could not see this alert because Excel is still not visible. In such a case, excel simply assumes that the user did not accept the deletion. In case you made excel visible earlier to calling Delete() method, you would have seen the dialog.

By assigning .f. to the property DisplayAlerts, we asked Excel to execute the method without waiting for confirmation.