VBA: Easiest way to retrieve values from excel

[CadUser46]

Im really struggling with understanding how to get a handle into excel.  I can see in the help it declares the hook as an object, but when i do this it does not allow me to use intellisense so i then struggle to write what i need because ti doesnt help me navigate the objects.

In another here here he declares it as 'Excel.Application'.  This failed on me but some more reasearch lead me to add the Microsoft Excel 14.0 Object Library as a reference, after which point intellisense works. This make sense.

http://forums.autodesk.com/t5/Inventor-Customization/VBA-Excel-error/m-p/4914538/highlight/true#M491...

I also see most examples refer to iLogic 'GoExcel' (which looks very handy and easy to use) but this is not an option for me. It must be VBA.

What is the easiest way for me to open a file on a specific tab, loop through lines refering to a specific cells?

Thanks

[CadUser46]

I may have figuired it out unless there is a better way. 

MyXL.Worksheets("SheetName").Range("E2").Value

[ekinsb · Accepted solution]

Here's some VBA code that accesses Excel, opens a spreadsheet and reads values from a specified sheet.  As you already found out, you need to reference the Excel libary in your project.  Hopefully this will help.

Public Sub GetExcelData()
    Dim excelApp As Excel.Application
    ' Try to connect to a running instance of Excel.
    On Error Resume Next
    Set excelApp = GetObject(, "Excel.Application")
    
    If Err Then
        Err.Clear
        
        ' Couldn't connect so start Excel.  It's started invisibly.
        Set excelApp = CreateObject("Excel.Application")
        
        If Err Then
            MsgBox "Cannot access excel."
            Exit Sub
        End If
    End If
    
    ' You can make it visible if you want.  This is especially
    ' helpful when debugging.
    excelApp.Visible = True
    
    ' Open the spreadsheet.
    Dim wb As Workbook
    Set wb = excelApp.Workbooks.Open("C:\Temp\Thing.xlsx")
    If Err Then
        MsgBox "Unable to open the Excel document."
        Exit Sub
    End If
    
    ' Access a certain sheet.
    Dim ws As Worksheet
    Set ws = wb.Worksheets.Item("Numbers")
    If Err Then
        MsgBox "Unable to get the worksheet."
        Exit Sub
    End If
    
    ' Read some values from the sheet.
    Dim row As Integer
    Dim col As Integer
    For row = 1 To 5
        For col = 1 To 3
            Debug.Print "Row: " & row & ", Col: " & col & " = " & ws.Cells(row, col)
        Next
    Next
End Sub

[CadUser46]

Cheers Brian.  Im going to adapt my code slightly to your method as it allows intellisense to function as your creating the object, then opening the file.

You should include this in the API help file to improve on what is there.

[gerrardhickson]

Old post I realise, but I'm struggling through this problem now. I realised there's a "On Error Goto 0" missing.

Public Sub GetExcelData()
    Dim excelApp As Excel.Application
    ' Try to connect to a running instance of Excel.
    On Error Resume Next
    Set excelApp = GetObject(, "Excel.Application")
    
    If Err Then
        Err.Clear
        
        ' Couldn't connect so start Excel.  It's started invisibly.
        Set excelApp = CreateObject("Excel.Application")
        
        If Err Then
            MsgBox "Cannot access excel."
            Exit Sub
        End If
    End If

    'REVERT TO DEFAULT ERROR HANDLING
    On Error Goto 0
    
    ' You can make it visible if you want.  This is especially
    ' helpful when debugging.
    excelApp.Visible = True
    
    ' Open the spreadsheet.
    Dim wb As Workbook
    Set wb = excelApp.Workbooks.Open("C:\Temp\Thing.xlsx")
    If Err Then
        MsgBox "Unable to open the Excel document."
        Exit Sub
    End If
    
    ' Access a certain sheet.
    Dim ws As Worksheet
    Set ws = wb.Worksheets.Item("Numbers")
    If Err Then
        MsgBox "Unable to get the worksheet."
        Exit Sub
    End If
    
    ' Read some values from the sheet.
    Dim row As Integer
    Dim col As Integer
    For row = 1 To 5
        For col = 1 To 3
            Debug.Print "Row: " & row & ", Col: " & col & " = " & ws.Cells(row, col)
        Next
    Next
End Sub