How to delete the sheet in Excel?
@Anonymous wrote:
Unfortunately, this is not it.
The program went through all the code, but the effect in the XLS file could not be seen.
Jerzy
Jerzy,
it works on this side...
In some OS the c:\Temp is not accessible to all users, try the code in a directory not in c:\, try for instance in desktop.
Hope this helps,
Henrique
@Anonymous wrote:
I replace "c:\temp\test.xls" to 'C:\\Users\\bajj\\Desktop\\test.xls' and still does not work.
I have full permissions on the file.
Jerzy
Jerzy,
if you enter
(findfile "C:\\Users\\bajj\\Desktop\\test.xls")
what is the return?
I don't have any problem with the (vla-delete SelSht)
Before run the code
after run the code
Henrique
Jerzy,
I still think that the problems are permissions...
(defun c:pol (/ Excel XlsFile WorkSheets SelSht ShtName)
(setq Excel (vlax-get-or-create-object "Excel.Application")
XlsFile (vl-catch-all-apply
'vla-open
(list (vlax-get-property Excel "WorkBooks") (findfile "C:\\temp\\Mosty\\proba.xls"))
)
)
(vlax-invoke-method XlsFile "Activate")
(setq WorkSheets (vl-catch-all-apply 'vlax-get-property (list XlsFile "Worksheets")))
(setq SelSht (vl-catch-all-apply 'vlax-get-property (list WorkSheets "Item" 2)))
(setq ShtName (vla-get-Name SelSht))
(if (= (vla-delete SelSht) :vlax-true)
(alert (strcat "\n " ShtName " was deleted... "))
(alert (strcat "\n " ShtName " is write-protected.. "))
)
(vl-catch-all-apply 'vlax-invoke-method (list XlsFile "Save"))
(vl-catch-all-apply 'vlax-invoke-method (list XlsFile "Close"))
(vl-catch-all-apply 'vlax-invoke-method (list Excel "Quit"))
(mapcar
'(lambda (x)
(vl-catch-all-apply
'(lambda ()
(progn (vlax-release-object x) (setq x nil))
)
)
)
(list Excel XlsFile WorkSheets SelSht)
)
)
Henrique
@Anonymous wrote:
I tried a completely 'fresh' file, which I wrote as XLS. And it works.
Thank you.
And the old file was not working, perhaps because the earlier it had an extension XLSX, and I wrote to try it as XLS.Maybe this stuck my problem.
You're welcome, JerzyBajj
Glad you got it sorted.
Henrique
@Anonymous wrote:
Hello
Subject unfortunately returns.
Everything works, provided that cleared layout is empty.
If you are in this layout are any entries that you can not delete it.
How to get around this?
In Excel, it is so that as you delete a blank layout this program do not ask and deletes. How can something tab is before deleting, Excel first asks 'Are you sure'?
Hi JerzyBajj,
I can't test it at the moment, but delete sheet cells, save, delete the sheet, should do the trick...
(defun c:pol (/ Excel XlsFile WorkSheets SelSht ShtName)
(setq Excel (vlax-get-or-create-object "Excel.Application")
XlsFile (vl-catch-all-apply
'vla-open
(list (vlax-get-property Excel "WorkBooks") (findfile "D:\\temp\\Mosty\\proba.xls"))
)
)
(vlax-invoke-method XlsFile "Activate")
(setq WorkSheets (vl-catch-all-apply 'vlax-get-property (list XlsFile "Worksheets")))
(setq SelSht (vl-catch-all-apply 'vlax-get-property (list WorkSheets "Item" 2)))
(setq ShtName (vla-get-Name SelSht))
(vlax-invoke-method (vlax-get-property SelSht 'cells) 'delete) ; <<<<<
(vl-catch-all-apply 'vlax-invoke-method (list XlsFile "Save")) ; <<<<<
(setq SelSht (vl-catch-all-apply 'vlax-get-property (list WorkSheets "Item" 2)))
(if (= (vla-delete SelSht) :vlax-true)
(alert (strcat "\n " ShtName " was deleted... "))
(alert (strcat "\n " ShtName " is write-protected.. "))
)
(vl-catch-all-apply 'vlax-invoke-method (list XlsFile "Save"))
(vl-catch-all-apply 'vlax-invoke-method (list XlsFile "Close"))
(vl-catch-all-apply 'vlax-invoke-method (list Excel "Quit"))
(mapcar
'(lambda (x)
(vl-catch-all-apply
'(lambda ()
(progn (vlax-release-object x) (setq x nil))
)
)
)
(list Excel XlsFile WorkSheets SelSht)
)
)
Hope this helps,
Henrique
@Anonymous wrote:
It works.
Thank you again.
You're welcome, JerzyBajj
Glad I could help
Henrique