wow..coding yg benar2 menarik.. terima kasih pak edy atas sharingnya.. best regrads,
story On Wed Aug 4th, 2010 10:50 AM EDT Edy WIYONO wrote: >Bisa di baca di > >http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/71915/Excel- >Automation-sp-OACreate-Excel-Application > > > >Using the simple example below is there a way to change @Value from a single > >value to a recordset result like using CopyFromRecordset > >declare @xl integer >declare @rs integer >declare @Value integer >set @Value = 50 > >execute @rs = master.dbo.sp_OACreate 'Excel.Application', @xl output >execute @rs = master.dbo.sp_OASetProperty @xl, 'ScreenUpdating', 'False' >execute @rs = master.dbo.sp_OASetProperty @xl, 'DisplayAlerts', 'False' > >declare @xlWBs integer >execute @rs = master.dbo.sp_OAMethod @xl, 'Workbooks', @xlWBs output > >declare @xlWB integer >Create new file. >execute @rs = master.dbo.sp_OAMethod @xlWBs, 'Add', @xlWB output, -4167 > >declare @xlWS integer >execute @rs = master.dbo.sp_OAMethod @xlWB, 'Sheets(Sheet1)', @xlWS output > >declare @xlCell integer >execute master.dbo.sp_OAGetProperty @xlWS, 'Cells', @xlCell output, 4,2 > >execute @rs = master.dbo.sp_OASetProperty @xlCell, 'Value', @Value > >execute @rs = master.dbo.sp_OADestroy @xlCell > >declare @FileName varchar(100) >set @FileName = 'C:\\NewFile_' + CONVERT(Char(10),GETDATE(),110) + '.xls' > >execute @rs = master.dbo.sp_OAMethod @xlWB, 'SaveAs', Null, @FileName > >execute @rs = master.dbo.sp_OASetProperty @xl, 'ScreenUpdating', 'True' >execute @rs = master.dbo.sp_OASetProperty @xl, 'DisplayAlerts', 'True' > >execute @rs = master.dbo.sp_OAMethod @xlWB, 'Close' >execute @rs = master.dbo.sp_OAMethod @xl, 'Quit' >execute @rs = master.dbo.sp_OADestroy @xlWS >execute @rs = master.dbo.sp_OADestroy @xlWBs >execute @rs = master.dbo.sp_OADestroy @xlWB >execute @rs = master.dbo.sp_OADestroy @xl > > > >atau di > >http://www.siccolo.com/Articles/SQLScripts/how-to-create-sql-to-convert-Exce >l_to_table.html > > > >drop procedure sp_excel_Convert_Excel_To_Table > >go > > > >create procedure sp_excel_Convert_Excel_To_Table > >( > > @excel_full_file_name varchar(666) > > > > ,@convert_to_table_name varchar(50) > > > > ,@transfer_to_table bit=1 > > ,@clear_existing_records_first bit=1 > > > > ,@good int =null output > > ,@error_code int =null > > ,@error_description varchar(255) = null output > >) > >as > > set nocount on > > > > > > declare @command varchar(8000) > > > > > > -- copy excel file under temp and change worksheet name > > set @good=0 > > set @error_description = '' > > set @error_code=0 > > > > declare @object int > > ,@hr int > > ,@src varchar(255) > > > > exec @hr = master.dbo.sp_OACreate 'Excel.Application', @object out > > if @hr <> 0 > > begin > > exec master.dbo.sp_OAGetErrorInfo @object, @src out, >@error_description out > > set @error_description = '1. ' + >isnull(@error_description,'N/A') > > select hr=convert(varbinary(4),@hr), sour...@src, >descripti...@error_description > > set @error_code=1 > > set @good=0 > > goto error > > end > > > > exec @hr = master.dbo.sp_OASetProperty @object, 'DisplayAlerts', >'false' > > if @hr <> 0 > > begin > > exec master.dbo.sp_OAGetErrorInfo @object, @src out, >@error_description out > > set @error_description = '2. ' + >isnull(@error_description,'N/A') > > select hr=convert(varbinary(4),@hr), sour...@src, >descripti...@error_description > > set @error_code=2 > > set @good=0 > > goto error > > end > > > > declare @workbook int > > ,@workbook_path_save_as varchar(666) > > set @workbook_path_save_as = 'c:\temp.xls' > > exec @hr = master.dbo.sp_oaMethod @Object,'WorkBooks.Open',@workbook >out,@excel_full_file_name > > if @hr <> 0 > > begin > > exec master.dbo.sp_OAGetErrorInfo @object, @src out, >@error_description out > > set @error_description = '3. ' + >isnull(@error_description,'N/A') > > select hr=convert(varbinary(4),@hr), sour...@src, >descripti...@error_description > > set @error_code=3 > > set @good=0 > > goto error > > end > > > > > > exec @hr = master.dbo.sp_OASetProperty @object, >'Workbooks(1).Worksheets(1).Name','excel_data' > > if @hr <> 0 > > begin > > exec master.dbo.sp_OAGetErrorInfo @object, @src out, >@error_description out > > set @error_description = '4. ' + >isnull(@error_description,'N/A') > > select hr=convert(varbinary(4),@hr), sour...@src, >descripti...@error_description > > set @error_code=4 > > set @good=0 > > goto error > > end > > > > exec @hr = master.dbo.sp_oaMethod @workbook >,'SaveAs',null,@workbook_path_save_as > > if @hr <> 0 > > begin > > exec master.dbo.sp_OAGetErrorInfo @object, @src out, >@error_description out > > set @error_description = '5. ' + >isnull(@error_description,'N/A') > > select hr=convert(varbinary(4),@hr), sour...@src, >descripti...@error_description > > set @error_code=5 > > set @good=0 > > goto error > > end > > > > -- get full file name > > exec @hr = master.dbo.sp_OAGetProperty @object, >'Workbooks(1).FullName',@workbook_path_save_as output > > if @hr <> 0 > > begin > > exec master.dbo.sp_OAGetErrorInfo @object, @src out, >@error_description out > > set @error_description = '6. ' + >isnull(@error_description,'N/A') > > select hr=convert(varbinary(4),@hr), sour...@src, >descripti...@error_description > > set @error_code=6 > > set @good=0 > > goto error > > end > > > > exec @hr = master.dbo.sp_oaMethod @Object,'Application.Quit' >--,@workbook out,@workbook_path > > if @hr <> 0 > > begin > > exec master.dbo.sp_OAGetErrorInfo @object, @src out, >@error_description out > > set @error_description = '7. ' + >isnull(@error_description,'N/A') > > select hr=convert(varbinary(4),@hr), sour...@src, >descripti...@error_description > > set @error_code=7 > > set @good=0 > > goto error > > end > > > > if @transfer_to_table = 1 > > begin > > if @clear_existing_records_first=1 > > begin > > set @command = 'delete from ' + >@convert_to_table_name > > exec (@command) > > end > > > > -- copy records from excel into table: > > set @command = 'insert into ' + @convert_to_table_name + > > ' select * from ' + > > ' OpenRowSet(''MSDASQL'' '+ > > ', ''Driver={Microsoft Excel Driver >(*.xls)};'+ > > 'DBQ=' + @workbook_path_save_as + ''''+ > > ',''SELECT * FROM [excel_data$]'')' > > print @command > > > > exec (@command) > > > > set @command = 'select * from ' + @convert_to_table_name > > exec (@command) > > end > > > > > >error: > > > > select @error_description as 'Error', @error_code as 'Error Code' > > > > exec @hr = master.dbo.sp_OADestroy @workbook > > if @hr <> 0 > > begin > > set @error_description = '7. ' + >isnull(@error_description,'N/A') > > select hr=convert(varbinary(4),@hr), sour...@src, >descripti...@error_description > > set @error_code=7 > > set @good=0 > > end > > > > exec @hr = master.dbo.sp_OADestroy @object > > if @hr <> 0 > > begin > > set @error_description = '8. ' + >isnull(@error_description,'N/A') > > select hr=convert(varbinary(4),@hr), sour...@src, >descripti...@error_description > > set @error_code=8 > > set @good=0 > > end > > > >go > > > > > > > > > >From: programmer-vb@yahoogroups.com [mailto:programmer...@yahoogroups.com] >On Behalf Of Anez Story >Sent: Wednesday, August 04, 2010 21:24 >To: programmer-vb@yahoogroups.com >Subject: Re: [programmer-vb] Transfer data SQL server 2005 ke excel > > > > > >Dear edy, > >hmm..blh di kasih contoh tidak pak?yg dr sql server saja.. >soale saya ndak ngerti apa itu late binding or early binding.. > >best regrads, > >story > >On Wed Aug 4th, 2010 10:05 AM EDT Edy WIYONO wrote: > >>Sekedar nambahin : >> >> >> >>Untuk Office Automation >> >>- Bisa dengan menggunakan kepustakaan (library excel) >> >> >> >>Jika dijalankan dari Client. >> >>- Klien mesti menginstall ms excel >> >>- Untuk mengatasi perbedaan versi Microsoft excel, gunakan teknik >>late binding dibandingkan early binding. >> >>- File hasilnya bisa dijalankan taruh di client (local) / di folder >>sharing (server). >> >> >> >>Jika dijalankan dari SQL Server. >> >>- Kalau pakai TSQL , anda bisa membuat instance ms Excel dengan >>sp_OCreate >> >>- Untuk mengatasi perbedaan versi Microsoft excel, gunakan teknik >>late binding dibandingkan early binding. >> >>- File Hasilnya bisa dijalankan / ditaruh di local (server) atau di >>Client. >> >>- Apabila ingin dijalankan di client (folders sharing di client), >>manfaatkan xp_cmdShell 'net send .' >> >>(silahkan dieksplorasi lebih jauh) >> >> >> >>Jika ingin mengambil data recordset sekaligus, gunakan copyfromrecordset >>kepunyaan excel, >> >>Atau getrows. >> >> >> >>Jika menggunakan ms access sebagai aplikasi, bisa digunakan perintah >>docmd.outputto atau docmd.transferspreadsheet >> >> >> >>Untuk formatting hasil transfer data, biasanya saya gunakan cara, data di >>export ke sheet1, sedangkan formatnya di buat di sheet2. >> >>Nantinya data di sheet2 mengambil raw-nya dari Sheet1. >> >> >> >>Mudah-mudahan berguna, >> >> >> >>Salam hangat dan jabat erat, >> >>Edy WIYONO >> >>From: programmer-vb@yahoogroups.com ><mailto:programmer-vb%40yahoogroups.com> >[mailto:programmer-vb@yahoogroups.com ><mailto:programmer-vb%40yahoogroups.com> ] >>On Behalf Of Anez Story >>Sent: Wednesday, August 04, 2010 07:40 >>To: programmer-vb@yahoogroups.com <mailto:programmer-vb%40yahoogroups.com> >>Subject: Re: [programmer-vb] Transfer data SQL server 2005 ke excel >> >> >> >> >> >>O seperti itu toh.. >>hmm..bnr jg,kl dts file outputnya ada di server.. >>makasih pak johan atas penjelasan nya..mslhnya sudah bs diatasi.hehe >> >>On Mon Aug 2nd, 2010 12:04 AM EDT johan max wrote: >> >>> >>> >>>Ole Automation itu menghubungi applikasi lain lewat mekanisme OLE. >>>Gampangnya memanggil excel dari vb. Kemudian dari vb datanya diinjectkan >>>ke xcel. Banyak kok contohnya.. >>>Masalah utama pake pendekatan ini adalah : >>>1. xcelnya harus ada di pc applikasi diinstall >>>2. suka tidak kompatibel antara xcel versi lama 97-2003 dan 2007 >>>3. lambat banget bisa diakalin pake varian tapi ini bikin boros memori. >>> >>>Kalo pake ssis/dts masalahnya : >>>1. xcelnya outputnya hanya bisa diletakan di server >>>2. tidak bisa diformat (font segala macem) >>>3. format xcelnya harus dibuat dulu (headernya nggak bisa sembarangan on >>the >>>run) >>> >>> >>> >>> >>> >>> >>> >>> >>>________________________________ >>>From: Anez Story <anez_st...@yahoo.com <mailto:anez_story%40yahoo.com> ><mailto:anez_story%40yahoo.com> > >>>To: programmer-vb@yahoogroups.com <mailto:programmer-vb%40yahoogroups.com> ><mailto:programmer-vb%40yahoogroups.com> >>>Sent: Sat, July 31, 2010 11:49:38 PM >>>Subject: Re: [programmer-vb] Transfer data SQL server 2005 ke excel >>> >>> >>>ole automation tuh maksudnya bagaimana ya pak? >>> >>> >>> >>> >>> >>>________________________________ >>>From: johan max <johans...@yahoo. com> >>>To: programmer-vb@ yahoogroups. com >>>Sent: Thu, July 29, 2010 8:18:17 PM >>>Subject: Re: [programmer- vb] Transfer data SQL server 2005 ke excel >>> >>> >>>Pake ole automation. Lagi di warnet jadi nggak bisa kasih contoh... >>> >>> >>> >>> >>> >>> >>>________________________________ >>>From: Anez Story <anez_st...@yahoo. com> >>>To: Programmer VB <programmer-vb@ yahoogroups. com>; programmer-vb. >>>n...@yahoogroups. com; programmer-kristen@ yahoogroups. com >>>Sent: Wed, July 28, 2010 3:37:07 PM >>>Subject: [programmer- vb] Transfer data SQL server 2005 ke excel >>> >>> >>>dear all, >>> >>> >>>ada yang tau cara transfer data dari SQL Server 2005 ke excel melalui >>coding vb >>>6? >>> >>> >>>best regrads, >>> >>> >>>story >>> >>> >>> >>> >>> >>> >>> >>> >> >> >> > > >
