Get Data From Excel Worksheet

You can get the data of any Excel worksheet, without launching Excel.

Preparations

Add 1 Command Button to your form.
Add 1 reference to Microsoft Excel X.0 Object Library (From
VB Menu choose Project -> References..., mark the Microsoft Excel X.0 Object Library
check box and press OK).

Form Code

Private Sub Command1_Click()

    Dim xl As New Excel.Application
    Dim xlw As Excel.Workbook

    ' Open the excel file.
    ' replace the "c:\myDir\book1.xls" below with your excel file name.
    Set xlw = xl.Workbooks.Open("c:\myDir\book1.xls")

    ' replace "Sheet1" with the sheet that you want to get the data from it.
    xlw.Sheets("Sheet1").Select

    ' Get the value from cell(2,3) of the sheet.
    MsgBox xlw.Application.Cells(2, 3).Value

    ' Close worksheet without save changes.
    ' If you want to save changes, replace the False below with True 
    xlw.Close False

    ' free memory
    Set xlw = Nothing
    Set xl = Nothing

End Sub

Go Back