how to read Excel file

[rrrprogramer]

I am using VBA and Excel 2003.
I have an Excel file that contains data that could be 10 or 100 rows of data or more.

In a text file, I usually check for EOF and read the line
ex: Do While Not EOF(1) )
Line Input #1, strRec
Loop
However, I'm not sure the code for Excel .

Basically, how do I read an Excel file and how does the loop look like for Excel file? Any ideas?

After I open the Excel file using VBA code,

dim wbk as Workbook
Set wbk = Workbooks.Open("Myfile.xls", False)
wbk.Activate
wbk.Worksheets("Sheet1").Activate

[GTO]

Just by example, this presumes MyFile is in the same folder that ThisWorkbook is in. In Col A, it starts at row 2 and loops thru ea cell until the last cell with data.


Option Explicit

Sub exa()
Dim wkb As Workbook
Dim wks As Worksheet
Dim rngData As Range
Dim rngCell As Range
Dim vntCellValue As Variant

Set wkb = Workbooks.Open(Filename:=ThisWorkbook.Path & "\MyFile.xls", _
UpdateLinks:=False, _
ReadOnly:=True)
Set wks = wkb.Worksheets("Sheet1")
Set rngData = Range(wks.Range("A2"), wks.Cells(Rows.Count, "A").End(xlUp))

For Each rngCell In rngData
vntCellValue = rngCell.Value
Next

wkb.Close False
End Sub


Hope that helps,

Mark

[rrrprogramer]

Mark

I noticed in the code that it assumes the Excel file has only multiple rows of data and 1 column of data. what if there are multiple rows and multiple columns of data? How would the code would change?

Set wks = wkb.Worksheets("Sheet1")
Set rngData = Range(wks.Range("A2"), wks.Cells(Rows.Count, "A").End(xlUp))

For Each rngCell In rngData
vntCellValue = rngCell.Value
Next

[RolfJ]

The UsedRange property captures all rows and columns in a worksheet. Give this a try:


Set wks = wkb.Worksheets("Sheet1")
Set rngData = wks.UsedRange