the following is some simple, slow, and not terribly bright code, that will work a decent amount of time, will get you started quickly. To do it "right" will cause more questions and problems right now, you can "grow" into the better ways later.
The second routine does all the work. pass it the name of your workbook, the name of your worksheet, and the starting and ending cols.rows.
It prints the results in the debug window.
'-------------- snip--------------------
Sub Main()
Dim retValues As Variant
Dim I As Long, J As Long
' Sample data:
' Open a Workbook called "c:\book1.xls"
' Read from a worksheet called "Sheet1"
' Start Reading At Row 1, Column 2
' End Reading at Row 8, Column 11
retValues = ReadWorkSheetData("c:\book1.xls", "Sheet1", 1, 2, 8, 11)
' Build a string to output data
If IsEmpty(retValues) Then
MsgBox "Problem reading your data: check Debug window"
Exit Sub
End If
' Dump data to Debug.window
For I = 0 To UBound(retValues, 1)
For J = 0 To UBound(retValues, 2)
Debug.Print retValues(I, J),
Next J
Debug.Print
Next I
MsgBox "The Values read from Excel are printed in your debug window"
End Sub
Function ReadWorkSheetData(Filename As String, WorkSheet As String, StartRow As Long, StartCol As Long, EndRow As Long, EndCol As Long)
' reads data out from an excel spreadsheet and returns it as a variant safearray
Dim retValues() As Variant ' returns data in an array of safearrays
Dim I As Long, J As Long
Dim tmpVnt As Variant
Dim NumCols As Long, NumRows As Long
NumCols = EndCol - StartCol + 1: NumRows = EndRow - StartRow + 1
' resize the array on the fly
ReDim retValues(NumRows - 1, NumCols - 1)
' Start Excel
Dim xl As Object, wb As Object, ws As Object
'start excel
Set xl = CreateObject("Excel.application")
If xl Is Nothing Then Debug.Print "Cannot Open Excel": Exit Function
' open the workbook
Set wb = xl.Workbooks.Open(Filename, True, True)
If wb Is Nothing Then Debug.Print "Cannot open WorkBook " & Filename: Exit Function
' look for the worksheet
Set ws = wb.Worksheets.Item(WorkSheet)
If ws Is Nothing Then Debug.Print "Cannot Locate Worksheet " & WorkSheet: Exit Function
' Start Reading the data
For I = 0 To (NumRows - 1)
For J = 0 To (NumCols - 1)
retValues(I, J) = ws.Cells(StartRow + I, StartCol + J).Value
Next J
Debug.Print
Next I
' clean up before we leave
wb.Close (False): Set ws = Nothing: Set wb = Nothing
xl.Quit: Set xl = Nothing
ReadWorkSheetData = retValues
End Function
'----------------- snip--------------------
