Results 1 to 3 of 3

Thread: Export to excel

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2006
    Posts
    977

    Export to excel

    Hi
    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?

    THANKS

  2. #2
    PowerPoster
    Join Date
    Feb 2006
    Location
    East of NYC, USA
    Posts
    5,692

    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
    Posts
    222

    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