Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Reading Excel in VB

Options
  • 25-05-2006 12:27pm
    #1
    Surrender
    Registered Users Posts: 211 ✭✭


    Hi I'm trying to read an Excel sheet into a VB program, does anyone have any idea where to start with this, Any help greatly appreciated


Comments

  • Options
    #2
    Gosh
    Registered Users Posts: 683 ✭✭✭Gosh


    Version of VB? what do you want to do with the sheet?


  • Options
    #3
    EthanCleary
    Closed Accounts Posts: 17 ✭EthanCleary


    I found this link to be very useful with office applications development:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoxpta/html/vsofficedev.asp


  • Options
    #4
    FSL
    Registered Users Posts: 1,456 ✭✭✭FSL


    If you have SQL server you can use the opendatasource function to read an Excel file
    establish a connection to the server.
    assign a recordset
    then execute the sql transaction

    e.g.

    recordsetname.open "Sql statement",connection,cursor type,locking type
    the following will read a sheet from an excel file into a dataset

    datasetname.open "SELECT * FROM OpenDataSource(Microsoft.Jet.OLEDB.4.0',
    'Data Source="full path to the excel file";Extended properties=Excel 8.0')...[Sheet name in the worksheet$]",connection name,cursor,locking type

    e.g reading sheet1 from myworksheet.xls into tmpfile using connection cnn1 as a forward only dataset opened as read only

    tmpfile.open "SELECT *
    FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
    'Data Source="E:\ExcelFiles\mysubfolder\myworksheet.xls";Extended properties=Excel 8.0')...[sheet1$]",cnn1,adOpenForwardOnly, adLockReadOnly


Advertisement