Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exporting SQL data to Excel in VB6 (VB6)

    What is the best way to export data from a table in SQL to Excel from a VB project?

    I tried AdoCN.Execute "SELECT channelid INTO [Excel 8.0; Database=C:Book2.xls].[Sheet1] FROM tblChannels" but it retured:

    Specified owner name 'Excel 8.0;Database=C:Book2.xls' either does not exist or you do not have permission to used it.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exporting SQL data to Excel in VB6 (VB6)

    The workbook must already exist. Check the path and name carefully - your code works fine if I try it (after modifying the names to suit my situation, of course).

    Also see
    How To Transfer Data from ADO Data Source to Excel with ADO
    Methods for transferring data to Excel from Visual Basic
    How To Transfer Data from an ADO Recordset to Excel with Automation

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting SQL data to Excel in VB6 (VB6)

    Thanks for your reply Hans.

    Just out of interest, when you changed it to suit your situation, was AdoCN still connected to an SQL database or did you connect it to an Access database? I've seen a couple of similar examples online where the poster had said it worked, but both were connecting to Access.

    The Ado Connection i'm using to SQL is:
    Provider=MSDASQL.1;Password=testdb;User ID=sa;Extended Properties="DRIVER=SQL Server;SERVER=(local);UID=sa;PWD=education;APP=Vis ual Basic;WSID=EducationDirect;DATABASE=SpiritDevSmall "

    I checked the select statement to make sure its ok and it runs fine without the Into bit in it.
    The server is local so its definately using the right C:
    Book2.xls exists and I tried it with and without the Sheet1 worksheet existing, neither worked.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exporting SQL data to Excel in VB6 (VB6)

    I tested with a connection to an Access database since I don't have access to SQL Server.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting SQL data to Excel in VB6 (VB6)

    Maybe that syntax just doesnt work with SQL.

    For now I have gone with looping an ADO recordset and adding the records in that way, but i'll keep an eye out for a method of bulk inserting as running an insert statement will probably be quicker than looping an entire recordset!

    Thanks anyway though Hans.

    Regards,

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exporting SQL data to Excel in VB6 (VB6)

    Have you tried it with the Microsoft OLE DB Provider for SQL Server instead of the Microsoft OLE DB Provider for ODBC Drivers?

    If you use Automation to control Excel, you can use the CopyFromRecordset method of the Range object to insert all records from a DAO or ADO recordset.

Posting Permissions

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