none
Reading data from Excel into VB 2008 RRS feed

  • Question

  •  

    Hi there, I'm a new VB programmer, although I have a decent background in java.  Anyways, I'm learning VB and I'm trying to read data from an Excel file using VB 2008 and can't figure out how to accomplish this.  I can't seem to get any data from the Excel spreadsheet to display in a simple Windows form.  Also, there's a decent amount of data in the spreadsheet so I was wondering if it makes sense to just read data as needed from the excel spreadsheet or if there was some type of data structure that I could import the entire Excel spreadsheet into and do my analysis from there.  I was reading about the DataGridView, but that seems more for display purposes.  Just looking for some direction.  Thanks.
    Monday, March 10, 2008 9:03 PM

Answers

  • You can either use pure VB.Net code or you can do a Project > AddReference > Com > Microsoft Excel to add the Excel DLL to your project (the Excel object model). I don't have any examples of how to use that.

     

    I have a pure .net example (some of it is C# though)  - you can read through my various scattered notes to get some idea of how to get started.

     

     

     

    A pain in the butt problem with reading Excel data into a datatable is that if a column has numbers for the first few entries/rows, and then text in the remaining rows, excel thinks it's a numeric column, and hence won't import any of the text (or rather, imports it as null). The reason for this is that Excel is typeless so ADO.Net uses the first 8 rows of a column to "guess" the datatype of the whole column. The only solution I have found so far is to set the IMEX property in the connection string (where IMEX apparently means Import-Export):

    string strConnectionToSheet = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathToSpreadSheet + "; Extended Properties='Excel 8.0; IMEX=1; HDR=YES'";

    Apparently IMEX controls exporting and importing where 0=export, 1=import, 2=linked. In this case I was importing data from excel rather than exporting data to excel. Anyway, the above only worked for 16 rows, to get it to work for more I had to set HDR="NO" which means that (1) the datatable returns with no column names, in fact the first row is invalid because it is the column names (2) You have to do a select *, you cannot select individual columns because column HDR=NO meaning no columns to select. (3) You can copy the column names on the first row into the column name fields of the datatable.

    And the complete working code was this:

    OleDbDataAdapter da = new OleDbDataAdapter("Select * From [Customers$]", strConnectionToSheet);

    DataTable dt = new DataTable();

    da.Fill(dt);

    The website with this suggestion didn't explain the solution. Two websites that mentioned the problem (but offered a different solution) were:

    http://support.microsoft.com/kb/316934

    http://www.codeproject.com/office/excel_using_oledb.asp

    These websites mentioned TypeGuessRows and MaxScanRows (because Excel looks at the first 8 rows to GUESS what datatype is being used).

     

     

    Use the OLE DB Jet provider to create, access, and modify data stored in an Excel workbook.Although I haven't tried it on Excel tables, here's how to use that provider on an Access tables, and below we'll see that Excel syntax is probably similar:

    Dim pathToDatabase As String = "C:\northwind.mdb"

    Dim conectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " & pathToDatabase & ";Jet OLEDB:Engine Type=5"

    Dim query As String = "Delete * From Table1"

    Dim cn As New OleDb.OleDbConnection(connectionString)

    cn.Open()

    Dim cmd As New OleDb.OleDbCommand(query, cn)

    cmd.ExecuteNonQuery()

    cn.Close()

    But you cannot delete records from the Excel sheet. You might be able to link the table to Access and then delete directly from the Access, but you cannot send a delete command to an excel spreadsheet/table. See the Excel_0115_ConnectString in the XML connecition strings. Use the OleDbDataAdapter.Fill method on a a select statement such as this: "SELECT * FROM [Sheet1$]" You can use Insert, Update as usual (see below) but NOT Delete (well, you can delete from Access useing this same OleDB provider, although I hav en't tried excel). Keep in mind that Excel is not type-safe, any type of data can be stored in any column as to result in invalid data in some rows.

    Excel 2000 and 2002 are supported with the Excel 8.0 source database type as shown in the following example:

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myBook.xls;

    Extended Properties="Excel 8.0;HDR=YES";

     

    The Extended Properties attribute can, in addition to the ISAM version property, specify whether or not tables include headers as field names in the first row of a range using an HDR attribute. IN other words the above:

    Extended Properties="Excel 8.0;HDR=YES"

    ends with HDR=Yes indicating that the first row of a given table has column names.

    There are three ways in which you can reference Excel workbook data within a SQL statement:

    (1) Specify the worksheet name followed by a dollar sign to access the entire range used in the worksheet:

    SELECT * FROM [MySheet$]

    (2) Specify a range explicitly using cells:

    SELECT * FROM [MySheet$A1:E5]

    (3) Specify a range with a defined name, as shown in the solution:

    SELECT * FROM MyRange

    You can issue the following command to the OleDbDataAdapter to create a table in an Excel workbook. The workbook for the connection will be created if it does not exist. For example:

    CREATE TABLE MySheet (Field1 char(10), Field2 float, Field3 date)

     

    Create data

    You can use the INSERT command, either static or parameterized, to insert data into a worksheet or range:

    INSERT INTO [MySheet$] (Field1, Field2, Field3)

    VALUES ('testdata', 1.234, '09/28/1979');

     

    Retrieve data

    Use either a DataAdapter or a DataReader to retrieve data from an Excel workbook. Create a SQL SELECT statement referencing a worksheet or a range in an Excel workbook and execute the statement to fill a DataSet using a DataAdapter or to create a DataReader. For example:

    SELECT * FROM [MySheet$]

     

    Update data

    The UPDATE command, either static or parameterized, can update data in a worksheet or range. For example:

    UPDATE [MySheet$]

    SET Field2 = '2.345',

    Field3 = '10/18/1964'

    WHERE

    Field1 = 'testdata'

     

    Monday, March 10, 2008 9:55 PM
  •  SupermanTB wrote:

    Dim objExcel As New Excel._ExcelApplication  ' should be Excel.Application

    Dim objWrkBk As Excel.Workbook

    Dim objSht As Excel.Worksheet

    Dim objRng As Excel.Range

    Dim strCol, strCell As String

    Dim maxCol, maxRow As Integer

    Dim iRow, iCol As Integer

    Dim testdata As String

    maxRow = 2

    maxCol = 2

    iRow = 1

    objWrkBk = GetObject("c:\test.xls")

    objSht = objWrkBk.Worksheets(1)

    testdata = objSht.Cells(1, 1).ToString() ' should be objSht.Cells(1, 1).Value.ToString()

    TextBox1.Text = testdata

     

    Thank you Jal2 for your great help.

     

    Hi SupermanTB,

     

    Here is correct code sample:

    Firstly Add Reference to COM component: Microsoft Excel Object Library.

    Code Snippet

    Imports Microsoft.Office.Interop.Excel

     

    Public Class Form1

     

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

     

            Dim objExcel As New Excel.Application

            Dim objWrkBk As Excel.Workbook

            Dim objSht As Excel.Worksheet

            Dim testdata As String

     

            objWrkBk = GetObject("c:\test.xls")

             ' or objWrkBk = objExcel.Workbooks.Open("C:\test.xls")

            objSht = objWrkBk.Worksheets(1)

     

            testdata = objSht.Cells(1, 1).Value.ToString()

              ' or testdata = objSht.Rows(1).Cells(1).Text.ToString()

              ' or testdata = objSht.Range("A1").Value.ToString

     

            TextBox1.Text = testdata

     

    End Sub

     

    End Class

     

     

    There are many code samples about how to manipulate Excel Spreadsheet data in VB.NET.

     

    1. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2919624&SiteID=1

       How to read and write cells in Excel Spreadsheet

     

        Store each Array element to Excel in consecutive cells

     

    3. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2950911&SiteID=1

       Sum those consecutive cells horizontally or vertically

     

    4. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2912407&SiteID=1

       Retrieve all WorkSheet names in Excel file and binding specific WorkSheet content to DataGridView

     

    5. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2983726&SiteID=1

       Retrieve a range of cells and bind to DataGridView

     

     

    Regards,

    Martin

    Wednesday, March 19, 2008 7:26 AM

All replies

  • You can either use pure VB.Net code or you can do a Project > AddReference > Com > Microsoft Excel to add the Excel DLL to your project (the Excel object model). I don't have any examples of how to use that.

     

    I have a pure .net example (some of it is C# though)  - you can read through my various scattered notes to get some idea of how to get started.

     

     

     

    A pain in the butt problem with reading Excel data into a datatable is that if a column has numbers for the first few entries/rows, and then text in the remaining rows, excel thinks it's a numeric column, and hence won't import any of the text (or rather, imports it as null). The reason for this is that Excel is typeless so ADO.Net uses the first 8 rows of a column to "guess" the datatype of the whole column. The only solution I have found so far is to set the IMEX property in the connection string (where IMEX apparently means Import-Export):

    string strConnectionToSheet = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathToSpreadSheet + "; Extended Properties='Excel 8.0; IMEX=1; HDR=YES'";

    Apparently IMEX controls exporting and importing where 0=export, 1=import, 2=linked. In this case I was importing data from excel rather than exporting data to excel. Anyway, the above only worked for 16 rows, to get it to work for more I had to set HDR="NO" which means that (1) the datatable returns with no column names, in fact the first row is invalid because it is the column names (2) You have to do a select *, you cannot select individual columns because column HDR=NO meaning no columns to select. (3) You can copy the column names on the first row into the column name fields of the datatable.

    And the complete working code was this:

    OleDbDataAdapter da = new OleDbDataAdapter("Select * From [Customers$]", strConnectionToSheet);

    DataTable dt = new DataTable();

    da.Fill(dt);

    The website with this suggestion didn't explain the solution. Two websites that mentioned the problem (but offered a different solution) were:

    http://support.microsoft.com/kb/316934

    http://www.codeproject.com/office/excel_using_oledb.asp

    These websites mentioned TypeGuessRows and MaxScanRows (because Excel looks at the first 8 rows to GUESS what datatype is being used).

     

     

    Use the OLE DB Jet provider to create, access, and modify data stored in an Excel workbook.Although I haven't tried it on Excel tables, here's how to use that provider on an Access tables, and below we'll see that Excel syntax is probably similar:

    Dim pathToDatabase As String = "C:\northwind.mdb"

    Dim conectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " & pathToDatabase & ";Jet OLEDB:Engine Type=5"

    Dim query As String = "Delete * From Table1"

    Dim cn As New OleDb.OleDbConnection(connectionString)

    cn.Open()

    Dim cmd As New OleDb.OleDbCommand(query, cn)

    cmd.ExecuteNonQuery()

    cn.Close()

    But you cannot delete records from the Excel sheet. You might be able to link the table to Access and then delete directly from the Access, but you cannot send a delete command to an excel spreadsheet/table. See the Excel_0115_ConnectString in the XML connecition strings. Use the OleDbDataAdapter.Fill method on a a select statement such as this: "SELECT * FROM [Sheet1$]" You can use Insert, Update as usual (see below) but NOT Delete (well, you can delete from Access useing this same OleDB provider, although I hav en't tried excel). Keep in mind that Excel is not type-safe, any type of data can be stored in any column as to result in invalid data in some rows.

    Excel 2000 and 2002 are supported with the Excel 8.0 source database type as shown in the following example:

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myBook.xls;

    Extended Properties="Excel 8.0;HDR=YES";

     

    The Extended Properties attribute can, in addition to the ISAM version property, specify whether or not tables include headers as field names in the first row of a range using an HDR attribute. IN other words the above:

    Extended Properties="Excel 8.0;HDR=YES"

    ends with HDR=Yes indicating that the first row of a given table has column names.

    There are three ways in which you can reference Excel workbook data within a SQL statement:

    (1) Specify the worksheet name followed by a dollar sign to access the entire range used in the worksheet:

    SELECT * FROM [MySheet$]

    (2) Specify a range explicitly using cells:

    SELECT * FROM [MySheet$A1:E5]

    (3) Specify a range with a defined name, as shown in the solution:

    SELECT * FROM MyRange

    You can issue the following command to the OleDbDataAdapter to create a table in an Excel workbook. The workbook for the connection will be created if it does not exist. For example:

    CREATE TABLE MySheet (Field1 char(10), Field2 float, Field3 date)

     

    Create data

    You can use the INSERT command, either static or parameterized, to insert data into a worksheet or range:

    INSERT INTO [MySheet$] (Field1, Field2, Field3)

    VALUES ('testdata', 1.234, '09/28/1979');

     

    Retrieve data

    Use either a DataAdapter or a DataReader to retrieve data from an Excel workbook. Create a SQL SELECT statement referencing a worksheet or a range in an Excel workbook and execute the statement to fill a DataSet using a DataAdapter or to create a DataReader. For example:

    SELECT * FROM [MySheet$]

     

    Update data

    The UPDATE command, either static or parameterized, can update data in a worksheet or range. For example:

    UPDATE [MySheet$]

    SET Field2 = '2.345',

    Field3 = '10/18/1964'

    WHERE

    Field1 = 'testdata'

     

    Monday, March 10, 2008 9:55 PM
  • Awesome, thanks so much for the advice.  I'll be giving it a shot this evening.  I'll post back in a few days as I'll be out of town for the next couple of days.  Thanks again.

     

    Thursday, March 13, 2008 6:33 PM
  • Hi there, I read over your post and a few others and started writing some code and have gotten stuck.  I'm including my code below.  I was trying to baby step my way into things by displaying one cell of data in a form text box.  I'm running into errors on the objSht = objWrkBk.Worksheets(1) line.  This error occurs during runtime:  Member not found. (Exception from HRESULT: 0x80020003 (DISP_E_MEMBERNOTFOUND))

     

    Any ideas?

     

    Dim objExcel As New Excel._ExcelApplication

    Dim objWrkBk As Excel.Workbook

    Dim objSht As Excel.Worksheet

    Dim objRng As Excel.Range

    Dim strCol, strCell As String

    Dim maxCol, maxRow As Integer

    Dim iRow, iCol As Integer

    Dim testdata As String

    maxRow = 2

    maxCol = 2

    iRow = 1

    objWrkBk = GetObject("c:\test.xls")

    objSht = objWrkBk.Worksheets(1)

    testdata = objSht.Cells(1, 1).ToString()

    TextBox1.Text = testdata

    Thursday, March 13, 2008 8:58 PM
  •  SupermanTB wrote:

    Dim objExcel As New Excel._ExcelApplication  ' should be Excel.Application

    Dim objWrkBk As Excel.Workbook

    Dim objSht As Excel.Worksheet

    Dim objRng As Excel.Range

    Dim strCol, strCell As String

    Dim maxCol, maxRow As Integer

    Dim iRow, iCol As Integer

    Dim testdata As String

    maxRow = 2

    maxCol = 2

    iRow = 1

    objWrkBk = GetObject("c:\test.xls")

    objSht = objWrkBk.Worksheets(1)

    testdata = objSht.Cells(1, 1).ToString() ' should be objSht.Cells(1, 1).Value.ToString()

    TextBox1.Text = testdata

     

    Thank you Jal2 for your great help.

     

    Hi SupermanTB,

     

    Here is correct code sample:

    Firstly Add Reference to COM component: Microsoft Excel Object Library.

    Code Snippet

    Imports Microsoft.Office.Interop.Excel

     

    Public Class Form1

     

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

     

            Dim objExcel As New Excel.Application

            Dim objWrkBk As Excel.Workbook

            Dim objSht As Excel.Worksheet

            Dim testdata As String

     

            objWrkBk = GetObject("c:\test.xls")

             ' or objWrkBk = objExcel.Workbooks.Open("C:\test.xls")

            objSht = objWrkBk.Worksheets(1)

     

            testdata = objSht.Cells(1, 1).Value.ToString()

              ' or testdata = objSht.Rows(1).Cells(1).Text.ToString()

              ' or testdata = objSht.Range("A1").Value.ToString

     

            TextBox1.Text = testdata

     

    End Sub

     

    End Class

     

     

    There are many code samples about how to manipulate Excel Spreadsheet data in VB.NET.

     

    1. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2919624&SiteID=1

       How to read and write cells in Excel Spreadsheet

     

        Store each Array element to Excel in consecutive cells

     

    3. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2950911&SiteID=1

       Sum those consecutive cells horizontally or vertically

     

    4. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2912407&SiteID=1

       Retrieve all WorkSheet names in Excel file and binding specific WorkSheet content to DataGridView

     

    5. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2983726&SiteID=1

       Retrieve a range of cells and bind to DataGridView

     

     

    Regards,

    Martin

    Wednesday, March 19, 2008 7:26 AM
  • With the code above that Martin has provided, it does not seem to work.  I'm using VB2008 express edition and have wrttien the code in a console application.  When the debugger reaches the "objSht = objWrkBk.Worksheets(1)" line the debugger says: "Member not found. (Exception from HRESULT: 0x80020003 (DISP_E_MEMBERNOTFOUND))".

    I have added the COM reference and the console imports Excel.  The prgram apparently opens the workbook but can't find any worksheets.

    If anyone could help me on this it would be greatly appreciated.
    Sunday, May 24, 2009 5:31 AM
  • "When the debugger reaches the "objSht = objWrkBk.Worksheets(1)" line the debugger says: "Member not found. (Exception from HRESULT: 0x80020003 (DISP_E_MEMBERNOTFOUND))"."


    -> Please try to reference to a sheet via explicit sheet name.

           
    Dim objExcel As New Excel.Application

            Dim objWrkBk As Excel.Workbook

            Dim objSht As Excel.Worksheet

            objWrkBk = objExcel.Workbooks.Open("C:\test.xls")

            objSht = objWrkBk.Worksheets("Sheet1") 'Reference it via Sheet name

            objSht = objWrkBk.Worksheets(1) ''Reference it via Sheet index


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Send us any feedback you have about the help from MSFT at fbmsdn@microsoft.com.
    • Proposed as answer by Han_BFY Monday, November 1, 2010 2:34 AM
    Monday, May 25, 2009 7:52 AM
  • With the code above that Martin has provided, it does not seem to work.  I'm using VB2008 express edition and have wrttien the code in a console application.  When the debugger reaches the "objSht = objWrkBk.Worksheets(1)" line the debugger says: "Member not found. (Exception from HRESULT: 0x80020003 (DISP_E_MEMBERNOTFOUND))".

    I have added the COM reference and the console imports Excel.  The prgram apparently opens the workbook but can't find any worksheets.

    If anyone could help me on this it would be greatly appreciated.

    I have the exact same phenomenon using VB2008/NET3.5 with Office 11

    Ofcourse you can get round Workbooks.Add by doing a GetObject(), but it doesnt allow you to construct an entire workbook from scratch.
    However when I do use GetObject() it also strands with 0x80020003 on "Objsht = objWrkBk.Worksheets(1)" even when I explicitely reference the sheet by its name "Sheet 1".

    -> Reference to Microsoft Excel Object Library 11.0 done
    -> Imports of ns Microsoft.Office.Interop.Excel  >> this I can't do (only Microsoft.Office.Core is available)

    >> Imports Excel >> instead of above

    PS:  "Dim objExcel As New Excel.Application" also doesn't work, "New Can't be used on an interface"

    Help would be greatly appreciated

    Edit: Sorry I could have been clearer. If I use the COM reference it doesnt work, if I use the .NET (Microsoft.Office.Interop.Excel) it does work, hope that helps a bit
    • Proposed as answer by wmuselle Friday, June 12, 2009 9:25 AM
    Friday, June 12, 2009 9:17 AM
  • Hi,

    with this Excel .NET component you can easily import Excel to DataTable and then do your analysis in DataTable. Also, you can easily bind any Windows Forms grid control to DataTable to display your data.

    Here is a Excel VB.NET code how to import Excel to DataTable:

    Dim ef = New ExcelFile()
    ef.LoadXls("ExcelData.xls")
    
    ' Initialize DataTable (skip this if you have DataTable definition)
    Dim dt = New DataTable()
    dt.Columns.Add("name", GetType(String))
    dt.Columns.Add("birth", GetType(DateTime))
    
    Dim ws = ef.Worksheets(0)
    
    ' Extract data to DataTable
    ws.ExtractToDataTable(dt, ws.Rows.Count, ExtractDataOptions.StopAtFirstEmptyRow, ws.Rows(0), ws.Columns(0))
    

    Friday, July 9, 2010 8:56 AM
  • Give a try

    Actually this from martin, i just edit Dimension part because i using vb 2008 and 'Microsoft.Office.Interop.Excel' as reference in tab .NET.

    If you already have an answer then OK.

    Imports Microsoft.Office.Interop.Excel

    Public Class Form1

     

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRead.Click

     

            Dim objExcel As New Application

            Dim objWorkbook As Workbook

            Dim objWorksheet As Worksheet

            Dim Data As String

     

            objWorkbook = GetObject("E:\test.xlsx")

     

            ' or objWrkBk = objExcel.Workbooks.Open("E:\test.xlsx")

            objWorksheet = objWorkbook.Worksheets(1)

     

            Data = objWorksheet.Cells(1, 1).Value.ToString()

     

            ' or testdata = objSht.Rows(1).Cells(1).Text.ToString()

            ' or testdata = objSht.Range("A1").Value.ToString

     

            TextBox1.Text = Data

     

        End Sub

    End Class

    Thanks to martin for great job.


    Regards, BFY_VB Beginner

    • Proposed as answer by AJ.iitm Wednesday, April 18, 2012 3:16 PM
    Monday, November 1, 2010 2:50 AM
  • You also may want to look at EPPLUS at Codeplex (http://epplus.codeplex.com/).  I use it mainly to write Excel files but it can also read them.  It might have what you need.
    Thursday, December 23, 2010 1:49 PM
  •  SupermanTB wrote:

    Dim

    objExcel As New Excel._ExcelApplication  ' should be Excel.Application

    Dim objWrkBk As Excel.Workbook

    Dim objSht As Excel.Worksheet

    Dim objRng As Excel.Range

    Dim strCol, strCell As

    String

    Dim maxCol, maxRow As

    Integer

    Dim iRow, iCol As

    Integer

    Dim testdata As

    String

    maxRow = 2

    maxCol = 2

    iRow = 1

    objWrkBk = GetObject("c:\test.xls")

    objSht = objWrkBk.Worksheets(1)

    testdata = objSht.Cells(1, 1).ToString() ' should be objSht.Cells(1, 1).Value.ToString()

    TextBox1.Text = testdata

     

     

      

    Thank you Jal2 for your great help.

      

    Hi SupermanTB,

     

    Here is correct code sample:

    Firstly Add Reference to COM component: Microsoft Excel Object Library.

    Code Snippet

    Imports Microsoft.Office.Interop.Excel

     

    Public Class Form1

     

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

     

            Dim objExcel As New Excel.Application

            Dim objWrkBk As Excel.Workbook

            Dim objSht As Excel.Worksheet

            Dim testdata As String

     

            objWrkBk = GetObject("c:\test.xls")

             ' or objWrkBk = objExcel.Workbooks.Open("C:\test.xls")

            objSht = objWrkBk.Worksheets(1)

     

            testdata = objSht.Cells(1, 1).Value.ToString()

              ' or testdata = objSht.Rows(1).Cells(1).Text.ToString()

              ' or testdata = objSht.Range("A1").Value.ToString

     

            TextBox1.Text = testdata

     

    End Sub

     

    End Class

    I hate to drag up an old thread, but I am trying to write similar code and am having a problem. I've searched many forums and can't find a similar issue. If I try to write the code above, everything works fine until I begin referencing the sheet. In the last 3 lines which begin testdata = , I begin to type the line, when I get to objSht. and type in the dot, I can choose Cells, Rows, or Range from the list and it enters as it should. Then I enter the paramaters and type the second period. At that point, neither Value or Cells is an option. The only options are Equals, GetHashCode, GetType, ReferenceEquals, and ToString. Similarly, if I enter objSht.Range("A1") and enter a period hoping to use .Select, I get the same options. Have tried both objSht = objWrkBk.Worksheets(1) and objSht = objWrkBk.ActiveWorksheet with same results. I used to do this a lot in VB6 and never had any problems. It would seem that it would all work or none if I'm not referencing the com object correctly. Even within the worksheet object, it accepts the range, rows, or cells, but not the part after that. Any help appreciated.
    Wednesday, June 15, 2011 1:24 PM
  • Give a try

    Actually this from martin, i just edit Dimension part because i using vb 2008 and 'Microsoft.Office.Interop.Excel' as reference in tab .NET.

    If you already have an answer then OK.

    Imports Microsoft.Office.Interop.Excel

    Public Class Form1

     

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRead.Click

     

            Dim objExcel As New Application

            Dim objWorkbook As Workbook

            Dim objWorksheet As Worksheet

            Dim Data As String

     

            objWorkbook = GetObject("E:\test.xlsx")

     

            ' or objWrkBk = objExcel.Workbooks.Open("E:\test.xlsx")

            objWorksheet = objWorkbook.Worksheets(1)

     

            Data = objWorksheet.Cells(1, 1).Value.ToString()

     

            ' or testdata = objSht.Rows(1).Cells(1).Text.ToString()

            ' or testdata = objSht.Range("A1").Value.ToString

     

            TextBox1.Text = Data

     

        End Sub

    End Class

    Thanks to martin for great job.


    Regards, BFY_VB Beginner

    This code worked perfectly fine. Thanks for the answer.
    Wednesday, April 18, 2012 3:17 PM
  • May I know why I am getting the following errors, I already declare Imports Microsoft.Office.Interop.Excel from the top of my page and also even add the referece "Microsoft Office 11 or 12 Object Library". Thank you very much. Ü

    1. Excel.application is not defined
    2. Excel.Application is not defined
    3. Excel.Application is not defined

    Dim objExcel As New Excel.Application --> Excel.Application is not defined
    Dim objWrkBk As Excel.Workbook --> Excel.Application is not defined
    Dim objSht As Excel.Worksheet --> Excel.Application is not defined

    Friday, August 3, 2012 7:32 AM
  • May I know why I am getting the following errors, I already declare Imports Microsoft.Office.Interop.Excel from the top of my page and also even add the referece "Microsoft Office 11 or 12 Object Library". Thank you very much. Ü

    1. Excel.application is not defined
    2. Excel.Application is not defined
    3. Excel.Application is not defined

    Dim objExcel As New Excel.Application --> Excel.Application is not defined
    Dim objWrkBk As Excel.Workbook --> Excel.Application is not defined
    Dim objSht As Excel.Worksheet --> Excel.Application is not defined

    If you import Microsoft.Office.Interop.Excel you must write As New Application.

    If you import Microsoft.Office.Interop you must write As New Excel.Application.


    Armin

    Saturday, August 4, 2012 1:28 PM
  • Ok, i'll try. Thank you very much! Ü
    Monday, August 6, 2012 3:48 AM
  • Good Day Everyone! May I know how open an EXCEL file without coding the exact location?  

    Hard Coded ---> String = XL.Workbooks.Open("C:\TEST.XLS")

    Not Hard Coded ---> String = XL.Workbooks.Open App.Path ??????????

    Thank you very much.

    Friday, August 10, 2012 3:57 AM
  • Good Day Everyone! May I know how open an EXCEL file without coding the exact location?  

    Hard Coded ---> String = XL.Workbooks.Open("C:\TEST.XLS")

    Not Hard Coded ---> String = XL.Workbooks.Open App.Path ??????????

    Thank you very much.

    If the initial question of this thread is solved, please open a new thread for a new question. A better forum to ask is this: http://social.msdn.microsoft.com/Forums/en-US/vbinterop/threads

    Armin

    Friday, August 10, 2012 4:05 AM
  • Hi there, I read over your post and a few others and started writing some code and have gotten stuck.  I'm including my code below.  I was trying to baby step my way into things by displaying one cell of data in a form text box.  I'm running into errors on the objSht = objWrkBk.Worksheets(1) line.  This error occurs during runtime:  Member not found. (Exception from HRESULT: 0x80020003 (DISP_E_MEMBERNOTFOUND))

     

    Any ideas?

     

    Dim objExcel As New Excel._ExcelApplication

    Dim objWrkBk As Excel.Workbook

    Dim objSht As Excel.Worksheet

    Dim objRng As Excel.Range

    Dim strCol, strCell As String

    Dim maxCol, maxRow As Integer

    Dim iRow, iCol As Integer

    Dim testdata As String

    maxRow = 2

    maxCol = 2

    iRow = 1

    objWrkBk = GetObject("c:\test.xls")

    objSht = objWrkBk.Worksheets(1)

    testdata = objSht.Cells(1, 1).ToString()

    TextBox1.Text = testdata

    Hello I am interested in the topic...

    how if I have to import data from range A1:B10 to 2 different listboxes?

    LB1 shall contain all raws for column A (1 row for each listbox item), LB2 shall contain all rows for column B (still 1 row for each listbox item).

    Thanks in advance


    +++ Alex +++

    Friday, January 12, 2018 1:43 PM
  • Here is one of many methods.

    The code presented below is in a class as it's best to keep data operations out of forms.

    Note the description of each parameter for ReadData indicates what they are for. Note the SELECT e.g. SELECT F1, F2, if you were asking for column B and C we would need to adjust the F1 and F2 to F3 and F4. There are ways to modify this but at this point all you asked for is columns A and B. 

    Imports System.Data.OleDb
    Imports System.IO
    
    Public Class ExcelOperations
        Private mHasException As Boolean
        Public ReadOnly Property HasException As Boolean
            Get
                Return mHasException
            End Get
        End Property
        Private mException As Exception
        Public ReadOnly Property LastException As Exception
            Get
                Return mException
            End Get
        End Property
        ''' <summary>
        ''' This creates a connection string where the first row in the sheet
        ''' is data, not field names. To change this set HDR from No to Yes
        ''' </summary>
        ''' <param name="FileName"></param>
        ''' <returns></returns>
        Public Function ConnectionString(ByVal FileName As String) As String
            Dim Builder As New OleDbConnectionStringBuilder
            If Path.GetExtension(FileName).ToUpper = ".XLS" Then
                Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
                Builder.Add("Extended Properties", "Excel 8.0;IMEX=1;HDR=No;")
            Else
                Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
                Builder.Add("Extended Properties", "Excel 12.0;IMEX=1;HDR=No;")
            End If
    
            Builder.DataSource = FileName
    
            Return Builder.ConnectionString
        End Function
        ''' <summary>
        ''' 
        ''' </summary>
        ''' <param name="pFileName">Full path and Excel file name</param>
        ''' <param name="pSheetName">Sheet to read e.g. Sheet1</param>
        ''' <param name="pRange">Range e.g. A1:B10</param>
        ''' <returns></returns>
        Public Function ReadData(ByVal pFileName As String, ByVal pSheetName As String, ByVal pRange As String) As DataTable
            Dim dt As New DataTable
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = ConnectionString(pFileName)}
                    Dim cmd As OleDbCommand = New OleDbCommand($"SELECT F1,F2 FROM [{pSheetName}${pRange}]", cn)
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                End Using
            Catch ex As Exception
                mHasException = True
                mException = ex
            End Try
    
            Return dt
        End Function
    
    End Class
    

    Form code for two ListBoxs. In this case the Excel file resides in the same folder as the executable. Adjust the path for targeting your file.

    Imports System.IO
    
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim ops As New ExcelOperations
    
            Dim dt As DataTable = ops.ReadData(
                Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "SomeFile.xlsx"),
                "Sheet1",
                "A1:B10")
    
            If Not ops.HasException Then
    
                ListBox1.DataSource = dt
                ListBox1.DisplayMember = "F1"
    
                ListBox2.DataSource = dt.Copy
                ListBox2.DisplayMember = "F2"
            Else
                MessageBox.Show($"Failed to read sheet: {ops.LastException.Message}")
            End If
        End Sub
    End Class
    

    Other methods range from Open XML, third party libraries such as SpreadSheetLight which is free to Excel automation which is overkill.

    Going back to A and B column with F1 and F2, the following extension method can help with this but I don't have time to show you as I'm off to work now.

    Public Module ExcelExtensions
        <System.Runtime.CompilerServices.Extension()> _
        Public Function ExcelColumnName(ByVal Index As Integer) As String
            Dim chars = New Char() _
                {
                    "A"c, "B"c, "C"c, "D"c, "E"c, "F"c, "G"c, "H"c, "I"c,
                    "J"c, "K"c, "L"c, "M"c, "N"c, "O"c, "P"c, "Q"c, "R"c,
                    "S"c, "T"c, "U"c, "V"c, "W"c, "X"c, "Y"c, "Z"c
                }
    
            Index -= 1
    
            Dim columnName As String
            Dim quotient = Index \ 26
    
            If quotient > 0 Then
                columnName = ExcelColumnName(quotient) + chars(Index Mod 26)
            Else
                columnName = chars(Index Mod 26).ToString()
            End If
    
            Return columnName
        End Function
    End Module


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, January 12, 2018 2:11 PM
    Moderator