Delete rows, columns, or cells in Excel sheet

[Lau Amdi]

Hi all,

I have a lot of Excel documents with many sheets within each. However, for some sheets, I need to delete specific elements that are no longer needed (at least for now). I would rather not delete them manually in Excel.
I have previously used mata to manipulate the Excel files, but I can't figure out how to delete specific columns, rows, or cells.

My approach for manipulating in terms of e.g. column width and cell merging is e.g.:

mata: b = xl()
mata: b.load_book("my file")
mata: b.set_sheet("sheet")
mata: b.set_column_width(1,5,15)

Is is possible to use this approach to delete specific elements within the excel sheet?
(Note: I apply 'mata:' before each command as I apply these within a loop).

Thank you.

Best,
Lau

[Joseph Coveney]

I have previously used mata to manipulate the Excel files, but I can't figure out how to delete specific columns, rows, or cells.

Is is possible to use this approach to delete specific elements within the excel sheet?

I don't see mention in the documentation of any functions in the Mata xl() class that perform any of those tasks.

Python has a few packages (some commercial) that deal with Excel workbooks and can be called from Stata. You might want to look into what's doable via Python.

I don't know what specific requirements are imposed on your project that necessitate using Excel as the transfer medium, but it might be worth seeking alternatives to Microsoft Excel for a longer-term solution.

[Lau Amdi]

I don't see mention in the documentation of any functions in the Mata xl() class that perform any of those tasks.

Python has a few packages (some commercial) that deal with Excel workbooks and can be called from Stata. You might want to look into what's doable via Python.

I don't know what specific requirements are imposed on your project that necessitate using Excel as the transfer medium, but it might be worth seeking alternatives to Microsoft Excel for a longer-term solution.

Thank you Joseph. I was not able to find anything in the documentation either - but there was a chance is could have missed it.
I will look into the possibilities calling Python from Stata. Unfortunately, I am working on a secure server, and I believe that I am limited to Word or Excel.

[Joseph Coveney]

Unfortunately, I am working on a secure server, and I believe that I am limited to Word or Excel.

If you're limited to Microsoft Office products, then in order to accomplish these deletions programmatically you might be limited to writing a Visual Basic for Applications (VBA) module (or recording a macro) in a standalone workbook that opens a another workbook deletes specified rows, columns or cell ranges of a specified worksheet in it, and then saves it. You can have the function take the names of target workbooks, worksheets and worksheet components as arguments.