Your first problem which generates an error is that you're missing quotes in your last 2 steps. This will throw an error when your path has spaces in it.
ALTER procedure [dbo].[p_CreateExcel]
(
@db_name varchar(100),
@table_name varchar(100),
@file_name varchar(100)
)
as
--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select
@columns=coalesce(@columns+',','')+column_name+' as '+column_name
from
information_schema.columns
where
table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')
--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'
--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp "set fmtonly off select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c -t, -T -S'''
exec(@sql)
--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "set fmtonly off select * from XeProgst01.dbo.'+@table_name+'" queryout "'+@data_file+'" -c -t, -T -S'''
exec(@sql)
--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type "'+@data_file+'" >> "'+@file_name+'"'''
exec(@sql)
--Delete dummy file
set @sql= 'exec master..xp_cmdshell ''del "'+@data_file+'"'''
exec(@sql)
GO
The other problem is that you're creating a csv with xlsx extension which is basically unusable as it is. I found the code posted in here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
but I'm not sure why they claim is an Excel file.
Right now, I don't have the time to test other options as most of the time I do this using SSIS.