Exporting multiple files into excel workbook

[Collins MCAdoyo]

Hi Team,
I generated some multiple files namely: form_geninfo form1_hhmembers form2_plots form3_cropranking_main form3_cropranking_spouse form20.

so I am trying to export the files into excel workbook each dataset into its sheet. See the section of script below i wrote to loop and write each dataset in each worksheet:

...
local mylist form_geninfo form1_hhmembers form2_plots form3_cropranking_main form3_cropranking_spouse form20;
foreach x of local mylist{;
export excel using data.xls,sh("`x'");
};


Its working well looping naming each sheet as expected but the challenge am facing is that its picking the last dataset "form20" and writing in all the worksheets instead of each dataset in its sheet.

Please what could I be wrong in the above code? Any idea would be highly appreciated.

Collins

[Clyde Schechter]

The problem is that although you have varied the destination sheet in your -export excel- command, you have made no change to the data to be exported! So the same data gets put in each sheet. What you want is:

local mylist form_geninfo form1_hhmembers // etc.
foreach x of local mylist {
    use `x', clear
    export excel using data.xls, sheet("`x'")
}

Notes:
1. Untested. Beware of typos.
2. I didn't feel like writing out the long list of filenames in the local macro; you will need to do that.
3. You seem to be working with line delimiter semi-colon; my code is written with line delimiter carriage-return. Modify accordingly.
4. You probably need to specify the -replace- or -sheetmodify- or -sheetreplace- option here, as you have already created data.xls. (Or just delete data.xls before you run this.)

[Collins MCAdoyo]

Thanks so much Clyde for prompt response. This has fixed my problem.

Collins