The simplest way is to record a macro and DO IT!
You'll see that when you open the CSV file, it will
open in a new workbook, with the sheet  name the same
as the file  name. Rename the sheet, then move it to
your workbook. The macro should work the same.
You might need to write a code snippet to
look to see if the sheet name exists first and delete it.

let me know if you need more help.

Thanks for the help..

but recording wont solve my purpose..

actually the scenario is that where in a folder I have multiple csv   files
say text1.csv ,
text 2.csv etc & corresponding  excel  templates say text1.xls , text 2.xls
..

now i want to write a macro which opens a text1.csv and import  its content to
text2.xls in the first tab  and like wise for other files..

Not sure how to proceed if u cansuggest something would be great.

OK, you've got several csv  files.
do you know the names? or are they variable?
variable number of files?
You said:
text1.csv -to-> text2.xls
did you mean:
text1.csv -to-> text1.xls
text2.csv -to-> text2.xls

??
You said that the .xls files are "templates"
and you want to "import" the contents of the csv file.

Can you describe, step by step EXACTLY what you would
do if you were doing this manually?

As in:
Open test1.xls
select sheet  "data"
Open test1.csv
select all cells
return to test1.xls
paste-special values into sheet "data"
save .xls file
close .xls file
close .csv file

rinse
repeat

Or is the "template" something more than just formatting?

It's a simple matter to have the excel  macro find all the .csv
files in a folder and open them along with a corresponding .xls file.
but I can't help  you do what you want unless you can tell me what that is...