locked
Create Dataset Using "Add Data Source" for Excel Spreadsheet in Visual Studio 2008 - VB

    Question

  • Hello,

     

    I am trying to use the "Add Data Source" feature of Visual Studio to create a "dataset" from an existing Excel spreadsheet, similar to the method used in Chapter 18 of the book "Microsoft Visual Basic 2008" which uses a Microsoft Access Database (*.mdb).

     

    However, when selecting the ODBC, which does have the option of picking "Excel", and then the actual Excel file, after clicking on OK, a connection string is created.

     

    The dataset which is created, however always comes up with the error message that the objects will have to be added to the dataset.  I can't figure out how to create the objects from the Excel spreadsheet, either with the wizard or the dataset designer which is the other option.  I have found several code examples for using OLEDB but nothing that apparently works with ODBC. 

     

    I would appreciate it if anyone has a "step-by-step" guide to use the features of Visual Studio to create a "dataset" from an Excel Spreadsheet, rather than writing the code separate from the Wizard or DataSet Designer.

     

    Sincerely

     

    JV

    Tuesday, September 30, 2008 5:32 AM

Answers

All replies

  • Whether you use VB Express edition or not. VB Express edition doesn't supply ODBC option in this scenario. If you use VB.NET Express edition, I would like to recommend that you write the code snippet to implement  your requirement. Read How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET for your reference.
    Wednesday, October 01, 2008 9:16 AM
    Moderator
  • You may want to indicate which version of Excel you are using and the format in which you are saving the Excel files. I would avoid ODBC, if possible, when accessing an Excel file.

     

    Also, it helps to post code if you have it.

    Wednesday, October 01, 2008 11:54 AM
  • I tried to create a odbc connection to excel file in the designer and was able to connect but not able to create a new datasource.  Just kept getting an error -   selecting objects of type 'procedure' is not supported      Not sure what that is all about.  I don't have any interest in using the designer anyway but i can see how some might like to use it.  I had issues with datasets in the designer when i first started using databases and gave up on the designer after a short while.  Code may be your best bet anyway using an oledb connection.  Or you can use a progam called spreadsheetgear for .net.  It is very good.  Either way you can connect and get the information you need.  I have never had the issues with code that i did with the desiger.

     

    If you look in your database explorer you should have a connection to the excel file.  It may not have any tables listed though.   

     

    Take it for what it's worth.

    Jeff

     

    Wednesday, October 01, 2008 1:35 PM
  • I inadvertently got into the Express Edition of Visual Studio, although I was using a 90 day trial of Visual Studio 2008.

    I have no code snippets as I was trying to use the Designer within Visual Studio to create the database code.

     

    I'm using version 8.0 of Excel from Microsoft Office 2000

     

    Jeff has apparently run into the same problems that I encountered.  I also was unable to read anything from the tables.

     

    JV

    Wednesday, October 01, 2008 5:06 PM
  • I got the error using 2008 pro also.  Code may be the best choice.???

     

    Wednesday, October 01, 2008 5:26 PM
  • If I remember correctly some of the file based database systems are not supported by the Designer. Check back at Riquel's post for a link to code examples.

     

    Wednesday, October 01, 2008 6:47 PM