Append multiple worksheets in an excel file with local macros

[Scott Rick]

My data is in .xls format with multiple worksheets. The worksheets are named by month, for e.g january, february, march, etc. I want to:

1. convert each worksheet into a dta file and then append each file individually
2. name the saved file with the last "_*" from the file name. So in the example below, the final, appended dta file will be "KAR".

However, my code below fails to do so. Any help will be much appreciated

local mylist january february march april may june july august september october november december
foreach x in local `mylist' {
import excel using "${db}\raw data\OCEmr_Jan to Dec2022_KAR.xlsx", sheet("x") firstrow case(lower) clear
save "Originaldata", replace
}

[Clyde Schechter]

-foreach x in local `mylist' {- is mangled syntax. Also, you don't then refer to x properly in your -import excel- code. It should be:

local mylist january february march april may june july august september october november december
foreach x of local mylist { // N.B.: No ` ' allowed here
    import excel using "${db}\raw data\OCEmr_Jan to Dec2022_KAR.xlsx", sheet("`x'") firstrow case(lower) clear // N.B.: ` ' required here
    save "Originaldata", replace
}

[Scott Rick]

Thanks Clyde