Results 1 to 3 of 3

Thread: Export to excel

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2006

    Export to excel

    I have an SQL server 2000 view ([V1]) AND i WANT to export it to an excel file(2003).The excel file contains the same header than the view,I used the DTS but it didn't work,
    Also I tried the following code and also it didn't work

    insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=D:\testing.xls;',
    'SELECT * FROM [Sheet1$]') select * from [V1]

    So what's wrong with it?


  2. #2
    Join Date
    Feb 2006
    East of NYC, USA

    Re: Export to excel

    You want to insert into the sheet, not select from it. There's some very easy "create an Excel worksheet and put data into it" code that was posted here last week, but I didn't save a link to it. (It's about 3 lines of code for the whole thing.) Maybe one of the Excel gurus has a link.

  3. #3
    Addicted Member BestS's Avatar
    Join Date
    Mar 2005

    Re: Export to excel

    VB Code:
    1. Dim oExcel As Object, oBook As Object, oSheet As Object
    2. Dim rsEx As New ADODB.Recordset
    3.     Set oExcel = CreateObject("Excel.Application")
    4.     Set oBook = oExcel.Workbooks.Add
    5.     Set oSheet = oBook.Worksheets(1)
    6. [B]oSheet.Range("a1").CopyFromRecordset rsEx[/B] 'that is all

    You can also do it cell by cell
    oSheet.Range("a1") = rs.fiels(1)
    oSheet.Range("b1") = rs.fiels(2) ....etc
    Last edited by BestS; Apr 14th, 2006 at 01:24 AM.
    Using Visual Basic 6.0, access 2000, Visual Studio 2005

    Rate the post that you approve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width