going thru excel grid

[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

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

[Dalceon]

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

[Armbruster]

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

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?

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

[Dalceon]

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.

[Brandon141]

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

[Armbruster]

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?

[freewilly]

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