Solved: Trouble reading Excel data

[GrandpaB]

I'm having trouble reading data from Excel into Word using the Cells method. I have been able to read data using Range. Here is my code:
Option Explicit
Public MyExcel As Excel.Workbook
Public XX As String

Private Sub Document_Open()
Dim Path As String
Path = "C:\My Documents\LDSInvoices\"
Set MyExcel = GetObject(Path & "LDSInvoices.xls")
' XX = MyExcel.Application.Cells(2, 2).value
' XX = MyExcel.Application.Range(.Cells(2, 2)).value
XX = MyExcel.Application.Range("B2").value
End Sub
The XX gets the value contained in the B2 cell of the spreadsheet, but unfortunately I need the Cells method to work so that I can itterate through the spreadsheet. I have commented out several of my failed attempts to solve this problem. I get a compile error - Method 'Cells' of object '_Application' failed.

Your help in my VBA education would be greatly appreciated, thanks.

[matthewspatrick]

Try qualifying with the worksheet:





Option Explicit
Public MyExcel As Excel.Workbook
Public MyWS As Excel.Worksheet
Public XX As String

Private Sub Document_Open()
Dim Path As String
Path = "C:\My Documents\LDSInvoices\"
Set MyExcel = GetObject(Path & "LDSInvoices.xls")
Set MyWS = MyExcel.Worksheets("ws name")
XX = MyWS.Cells(2, 2).Value
End Sub

[GrandpaB]

Patrick,

Thanks, that solved my problem! I'm new to VBA and its syntax; your assistance was greatly appreciated. Do you know any good references? By the way I'm using Office 2003.

[matthewspatrick]

Glad to help! Make sure you mark the thread as solved :)

As for references...

Most of the time, I think the VBA help file does a decent job of explaining things. If you ever wonder about syntax or whether something is possible, try to go through the help file first
For Excel, I keep a copy of Walkenbach's Power Programming for Excel and Excel Formulas books at my desk, and refer to them often. For beginner or intermediate users, Jelen's Guerilla Data Analysis in Excel and his PivotTables books are very good
For Word, Anne Troy's (aka Dreamboat) Dreamboat on Word has place of honor in the bookshelf. Of course, Anne will be the first to tell you that she doesn't really do VBA, but it's still a must for anyone who uses Word professionally
For Access, I have a bunch of books but they are really geared toward serious users. Are you looking for Access materials?
General Office: Office Macros You Can Use Today from Holy Macro Press. Written by some of the brightest lights out there in the Office developer community
Participate at sites like this, Experts Exchange, MrExcel.com, etc.

[GrandpaB]

Patrick,

Thanks again for all the references and recommendations. I do have some facility with VB and I find VBA vaguely familiar, but accessing the Office objects is (for me) like stepping into the deep end of the pool.