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.
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
Solved! Go to Solution.
Solved by ekinsb. Go to Solution.
I may have figuired it out unless there is a better way.
MyXL.Worksheets("SheetName").Range("E2").Value
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
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.
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