read in from Excel to Visual Basic

s1booth
s1booth used Ask the Experts™
on
I am currently building a program that will open an Excel file for the user.  The user then enters data in this .xls file and when they save and close, the data entered in the specified cells needs to be sent back to Visual Basic to then print to a text file.  I can't figure out how read the data in to Visual Basic from the Excel file.  Any advise?
Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Here's some sample code that should show you what to do...

Step 1
------
You need to go to Tools - References in VB and add a check by the microsoft excel object library (it will be something like "Microsoft Excel 9.0 object Library" but the version number may differ)


Step 2
------
Use this code.  Note that you need to change the excel workbook name to your own...

    Public Sub GetExcelData()
        Dim objWorkbook As Excel.Workbook
        Dim objWorkSheet As Excel.Worksheet
        Dim objCell As Object       ' must be object or variant for its later use
       
        Dim sData As String
        Dim lRow As Long
        Dim lCol As Long
       
        Set objWorkbook = Excel.Workbooks.Open("c:\test\junk\Sample.xls")
        Set objWorkSheet = objWorkbook.Worksheets(1)
       
    ' Pick certain cells...
       
        sData = objWorkSheet.Cells(1, 2).Value    ' note that this is row, col so refers to B1
        MsgBox sData
       
    ' Loop through all cells
        For Each objCell In objWorkSheet.Cells
            sData = objCell.Value
            lRow = objCell.Row
            lCol = objCell.col
           
            MsgBox "Data in cell (row:" & lRow & "  col:" & lCol & ") is " & sData
        Next
       
   
    End Sub
   

Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

-  Points for simongv

Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
Venabili
EE Cleanup Volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial