Renaming an existing sheet in excel via xl() or putexcel

[Adrian Sayers]

Hi im not sure if this is possible or not, but does anyone know if you can rename an existing sheet in an existing excel file, or create copy of an existing sheet with a new name and then delete the old sheet?

Im wondering if its lurking in something that is undocumented.

thanks

Adrian

[Adrian Sayers]

Anyone have any thoughts or suggestions?

[Joseph Coveney]

You can try something like the following for copying an entire existing worksheet into a new one

version 16.1

clear *

quietly sysuse auto
rename foreign nondomestic
export excel using Test.xlsx, sheet(Autos) firstrow(variables)

*
* Begin here
*
local dsn Excel Files;DBQ=Test.xlsx;DefaultDir=`c(pwd)'/;

odbc exec("SELECT * INTO NewAutos FROM [Autos$];"), dsn("`dsn'")

odbc exec("DROP TABLE [Autos$];"), dsn("`dsn'")

exit

I'm not sure that any special cell formatting will be carried over. I'm guessing that it won't be. Also, as I recall, there's a limitation to Excel's ODBC driver in that you cannot delete things, and so that last SQL statement might leave you with a ghost worksheet in the workbook.

[Adrian Sayers]

Hi Joseph,

l'll take a look, i have stumbled across a bit of vbs that might do the trick. I'll post the solution if can wrap it up properly.

bw
Adrian

[Adrian Sayers]

I am going to stick this here for others to find.
Basic solution is to write some visual basic and shell that script to windows.

I butchered some code from the web.

It checks against sheets listed in the workbook otherwise it keeps excel open in the background. You then need to kill it of in task manager.

examples of syntax at the base.

prog define excelwrkshtrename  , rclass
version 15.1
syntax, sheets(string asis) file(string asis) [visible alerts]
if "`visible'"==""{
        local visible "False"
        }    
        else {
            local visible "True"
                }
if "`alerts'"=="" {
    local alerts "False"
    }
    else {
        local alerts "True"    
        }
* get the sheetnames
mata : excelwrksheet("`file'")

    
tempname myvbs
file open vbs using `myvbs'.vbs, write  replace

file write vbs `"Set objExcel = CreateObject("Excel.Application")"' _n
file write vbs `"objExcel.Visible = `visible'"' _n
file write vbs `"objExcel.DisplayAlerts = `alerts'"' _n
file write vbs `"Set objWorkbook = objExcel.Workbooks.Open ("`file'")"' _n

foreach sheet in `sheets' {
    tokenize `sheet'
        local oldsheet `1'
        local newsheet `2'

    if strpos("`r(excelsheets)'", "`oldsheet'")==0 {
        noisily display in red "`oldsheet' not within workbook no rename"
            }
    else {
            file write vbs `"Set objWorksheet = objWorkbook.Worksheets("`oldsheet'")"' _n
            file write vbs `"objWorksheet.Name = "`newsheet'""' _n
            }
    }

file write vbs `"objWorkbook.SaveAs ("`file'")"' _n
file write vbs `"objExcel.Quit"' _n
file write vbs `"Set objWorksheet = Nothing"' _n
file write vbs `"Set objWorkbook =Nothing"' _n
file write vbs `"Set objExcel =Nothing"' _n
file close vbs

* Sheel the VBS to cmd
shell cscript.exe `myvbs'.vbs
*Tidy up
erase `myvbs'.vbs
end

version 15.1
mata
        void excelwrksheet(    string    scalar file    )
                            
 {
 st_rclear()
 /* Excel opening*/
      class xl scalar b
      b =xl()
      b.load_book(file)
      b.set_mode("closed")    
      b.set_keep_cell_format("on")
      sheets = mm_invtokens(b.get_sheets())
        st_global("r(excelsheets)", sheets)


    b.close_book()
} // end excelwrksheet
end
/*
mata : excelwrksheet("C:\Users\Adrian\Desktop\RenameWorksheets\MyWorkbook.xlsx")

excelwrkshtrename ,  file(C:\Users\Adrian\Desktop\RenameWorksheets\MyWorkbook.xlsx) sheets( "Sheet1 Converted1" "Sheet2 Converted2" "Sheet3 Converted3" ) visible alerts
excelwrkshtrename ,  file(C:\Users\Adrian\Desktop\RenameWorksheets\MyWorkbook.xlsx) sheets(  "VBScript_Excel3 Converted3" )

*/