Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

SQL Server - Excel question

kel1981bkel1981b Member Posts: 852
Question may sound like stupid. I need to pass some data from SQL Server to Ecel. I wrote VB program to do that but it works extremly slow and working with VB - Exel object is very painfull. So I was thinking may be there is way to pass data from SQL Server to Excel using T-SQL or may be some internal Server functions?
Any advise/help will be highly appriciated.

Comments

  • DaedaliusDaedalius Member Posts: 30
    You'll have to do a bit of searching around (or some foot work) but I have seen posted on the internet the solution to your problem. The solution involves creating a temporary linked server (which is actually the excel spreadsheet) and the use of sp_OA (OLE Automation) to create and populate the data into the spreadsheet. Good luck.

    : Question may sound like stupid. I need to pass some data from SQL Server to Ecel. I wrote VB program to do that but it works extremly slow and working with VB - Exel object is very painfull. So I was thinking may be there is way to pass data from SQL Server to Excel using T-SQL or may be some internal Server functions?
    : Any advise/help will be highly appriciated.
    :

  • kel1981bkel1981b Member Posts: 852
    : You'll have to do a bit of searching around (or some foot work) but I have seen posted on the internet the solution to your problem. The solution involves creating a temporary linked server (which is actually the excel spreadsheet) and the use of sp_OA (OLE Automation) to create and populate the data into the spreadsheet. Good luck.
    :
    Thank you, I'll try
  • slaha11slaha11 Member Posts: 28
    [b][red]This message was edited by slaha11 at 2003-10-26 23:35:38[/red][/b][hr]
    Hi,

    Why don't you use DTS?
    It can do that very efficiently.

    You can even schedule a DTS package to do that for you.

    -surajIT




  • kel1981bkel1981b Member Posts: 852
    [b][red]This message was edited by kel1981b at 2003-10-31 6:19:37[/red][/b][hr]
    : [b][red]This message was edited by slaha11 at 2003-10-26 23:35:38[/red][/b][hr]
    : Hi,
    :
    : Why don't you use DTS?
    : It can do that very efficiently.
    :
    : You can even schedule a DTS package to do that for you.
    :
    : -surajIT
    :
    :
    Thank for your reply. I am not that experienced in SQL Server. Could you explain me how I can do that? Is there some good tutorial online for SQL Server - DTS?



  • kel1981bkel1981b Member Posts: 852
    : Hi,
    :
    : Follow the link:
    :
    : http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_basic_5zg3.asp
    :
    : This should suffice.
    :
    : -surajIT
    :
    :
    :
    Thanks a lot. I'll try.

  • monstormonstor Member Posts: 1
    : Question may sound like stupid. I need to pass some data from SQL Server to Ecel. I wrote VB program to do that but it works extremly slow and working with VB - Exel object is very painfull. So I was thinking may be there is way to pass data from SQL Server to Excel using T-SQL or may be some internal Server functions?
    : Any advise/help will be highly appriciated.
    :

    Have you considered using VBA within Excel to access SQL via ADO objects? I do this alot and it works very well. The process is
    1. Create an ADO connection & open it
    2. (optional) Create an ADO command object & build an SQL string using data from the Excel sheet (e.g. dates or account numbers)
    3. Open an ADO recordset & parse the recoredset, copying data into Excel worksheet cells.
    4. Close & discard the ADO objects

    One other similar option is to use VBA to manage the QueryTable object in Excel. Go to MSDN & search for "QueryTable Object" for artcles on how to do this.

    The ADO approach is good for importing data when you need control over the formatting & destination of that data.

    QueryTable approach is good if you need a refreshable link to some data in SQL
  • kel1981bkel1981b Member Posts: 852
    : : Question may sound like stupid. I need to pass some data from SQL Server to Ecel. I wrote VB program to do that but it works extremly slow and working with VB - Exel object is very painfull. So I was thinking may be there is way to pass data from SQL Server to Excel using T-SQL or may be some internal Server functions?
    : : Any advise/help will be highly appriciated.
    : :
    :
    : Have you considered using VBA within Excel to access SQL via ADO objects? I do this alot and it works very well. The process is
    : 1. Create an ADO connection & open it
    : 2. (optional) Create an ADO command object & build an SQL string using data from the Excel sheet (e.g. dates or account numbers)
    : 3. Open an ADO recordset & parse the recoredset, copying data into Excel worksheet cells.
    : 4. Close & discard the ADO objects
    :
    : One other similar option is to use VBA to manage the QueryTable object in Excel. Go to MSDN & search for "QueryTable Object" for artcles on how to do this.
    :
    : The ADO approach is good for importing data when you need control over the formatting & destination of that data.
    :
    : QueryTable approach is good if you need a refreshable link to some data in SQL
    :
    Right now I am using VB 6 Application to do that and I do not like it at all. IMO, working with VB 6 - Excel object is real paint of the ass. I need to run this process automaticaly at the and of the mounth without user intervantion. Seem to me it's not possible to do that using Excel VBA.
Sign In or Register to comment.