Reading an Excel file into VB variables

Posted on 2011-10-13
Last Modified: 2012-06-22
(VB6) The usual:

Open <file> For Input As #1
Line Input #1, <string>
Close #1

Works for .txt files.

How does one open a .xls file and read from it into a VB variable?

I suppose cell-by-cell, but I would expect to be able read an entire row, perhaps into a declared type.

I've never done this.
Question by:NormaPosy
    LVL 17

    Expert Comment

    That method will not work as an excel file is not a flat-file.
    YOu are in VB6 and it has been awhile since I tried that in VB6.

    In the .Net framework, you include the namespaces for the MSOffice and are able to instantiate and open an excel file and read thru it either row by row or cell by cell. YOu can also read a particular cell address.

    It will be helpful for you to open excel and create some macros reading thru the spreadsheet that you want to load. This give you an idea of how to reference and walk thru a spreadsheet.
    LVL 17

    Expert Comment

    This link on the site may be of some use to you.
    It has a coding example in it.

    Author Comment

    Private Sub cmdGo_Click()
    Dim oXL As Object ' Excel.Application
    Dim oWB As Object ' Excel.Workbook
    Dim oWS As Object ' Excel.Worksheet
    Dim sPathName As String
    Dim lngRow As Long
    Dim iI As Integer
    'Start Excel
    Set oXL = New Excel.Application
    'Open a workbook, select a sheet, assign variables to them
    sPathName = App.Path & "\F2011_League_Roster.xls"
    Set oWB = oXL.Workbooks.Open(sPathName)
    Set oWS = oWB.Sheets("Division")
    'Select cell A1
    'Show a messagebox before Excel shows up
    MsgBox "When Excel shows, please select an entire row of data"
    'Show Excel
    oXL.Visible = True
    'Loop until an entire row has been selected
    Do:   Loop Until (oXL.Selection.Columns.Count = 256) _
    And (oXL.Selection.Rows.Count = 1)
    'Hide Excel again
    oXL.Visible = False
    'Store row number of selected row
    lngRow = oXL.Selection.Row
    'Retrieve values from the selected row to textboxes
    For iI = 1 To 14
        txtInput(iI - 1).Text = oWS.Cells(lngRow, iI).Value
    Next iI
    'Close workbook and shut down Excel
    oWB.Close SaveChanges:=False
    Set oXL = Nothing
    Set oWB = Nothing
    Set oWS = Nothing
    End Sub

    Open in new window

    Bombs at:
    Set oXL = New Excel.Application
    "User defined type not defined"
    LVL 17

    Accepted Solution

    Don't forget....For this to work, you must set a reference to the Microsoft Excel Object Library.
    Otherwise you get the User Defined type not defined.

    Author Comment

    How do I do that?
    LVL 17

    Expert Comment


    Author Closing Comment

    Thank you

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now