going thru excel grid

Im not sure if i am on the right track at all. What i want to do is loop thru an excel spreadsheet a import the values from each cell. I have searched thru the forum and havent found anything that really helps me, most of it actually really confuses me. So any advice would be much appreciated. Thank

Code:

---

Private Sub cmdUpdate_Click()
Dim lRow As Long, lCol As Long, dValue As Double, dYear As Integer, dID As String
Dim obExcelApp As Excel.Application
Dim obWorkBook As Excel.Workbook
Set obExcelApp = CreateObject("Excel.Application")
Set obWorkBook = obExcelApp.Workbooks.Open("C:\windows\desktop\dfo project\temp.xls")

For lRow = 1 To 'not sure what to put here i know its the last row of the excel sheet
    For lCol = 1 'not sure what to put here
        dValue = Cells(lRow, lCol).Value
        MsgBox dValue ' will do something here later once i get the data in
    Next lCol
Next lRow

Set obWorkBook = Nothing
Set obExcelApp = Nothing
End Sub

---

is the format of this excel file going to be the same everytime??
as in consistent columns and data positions??

Quote:

---

Originally posted by Brandon141
Im not sure if i am on the right track at all. What i want to do is loop thru an excel spreadsheet a import the values from each cell. I have searched thru the forum and havent found anything that really helps me, most of it actually really confuses me. So any advice would be much appreciated. Thank

Code:

---

Private Sub cmdUpdate_Click()
Dim lRow As Long, lCol As Long, dValue As Double, dYear As Integer, dID As String
Dim obExcelApp As Excel.Application
Dim obWorkBook As Excel.Workbook
Set obExcelApp = CreateObject("Excel.Application")
Set obWorkBook = obExcelApp.Workbooks.Open("C:\windows\desktop\dfo project\temp.xls")

For lRow = 1 To 'not sure what to put here i know its the last row of the excel sheet
    For lCol = 1 'not sure what to put here
        dValue = Cells(lRow, lCol).Value
        MsgBox dValue ' will do something here later once i get the data in
    Next lCol
Next lRow

Set obWorkBook = Nothing
Set obExcelApp = Nothing
End Sub

---

---

Do you really want all 63,000 rows?

VB Code:

Private Sub cmdUpdate_Click()
 
Dim lRow As Long, lCol As Long, dValue As Double, dYear As Integer, dID As String
Dim obExcelApp As Excel.Application
Dim obWorkBook As Excel.Workbook
Dim c As Excel.Range
 
    Set obExcelApp = CreateObject("Excel.Application")
    Set obWorkBook = obExcelApp.Workbooks.Open("C:\windows\desktop\dfo project\temp.xls")
    
    For Each c In Excel.ActiveSheet.UsedRange
        dValue = c.Value
        MsgBox dValue ' will do something here later once i get the data in
    Next c
    
    Set obWorkBook = Nothing
    Set obExcelApp = Nothing
 
End Sub

if the format of the file is going to be the same, I would setup an import specification in access (if you have that program) and use vb to activate the macro.

no, i wanna get from column 2 to 13 and from row 3 to 33. Sorry, i should have been more specific. Thanks

VB Code:

Option Explicit
 
Private Sub cmdUpdate_Click()
Dim lRow As Long
Dim lCol As Long
Dim dValue As Double
Dim dYear As Integer
Dim dID As String
Dim obExcelApp As Excel.Application
Dim obWorkBook As Excel.Workbook
Set obExcelApp = CreateObject("Excel.Application")
Set obWorkBook = obExcelApp.Workbooks.Open("C:\windows\desktop\dfo project\temp.xls")
 
For lRow = 3 To 33 ' get rows 3 to 33
    For lCol = 2 To 13 ' get columns 2 to 13
        'I used val and text property to avoid type mismatch
        'if user enters text in a cell
        dValue = Val(ActiveSheet.Cells(lRow, lCol).Text)
        Debug.Pring dValue; ' will do something here later once i get the data in
    Next lCol
Next lRow
 
Set obWorkBook = Nothing
Set obExcelApp = Nothing
End Sub

Is that what you mean?

Brandon141

In my opinion, you are considerably better to use ADO rather than OLE to connect to Excel. This bypasses a lot of issues you have with OLE such as macros !@#$$ up and causing it to to need saving.

It gives you an ADO recordset that that you can uses as like any other recordset.

See the attachment for an example.

FW