Method or data member not found 2

[newguy86]

I am writing this macro to start in IBM Reflection and go to Excel to get info from a list and input it into a Reflection Session. And once its done it will move down one cell to the next one and start over. But I am getting this error message when I try to run the macro.

"Method or data member not found"

And it singles out this part of the macro.

.Sheets

Here is the full macro in case you need it.

Dim CellContents As String
Dim LocationCount As Integer
Dim ExcelApp As Excel.Application
Dim ExcelWorkbook As Excel.Worksheet
Set ExcelApp = New Excel.Application
Set ExcelWorkbook = ExcelApp.Workbooks.Open("R:\My Documents\Location Codes To Delete Rates.xls")
LocationCount = 2
StartPos = ExcelWorkbook.Sheets("Sheet1").Range("A & LocationCount")
If StartPos = ExcelWorkbook.Sheets("Sheet1").Range("A & LocationCount") Then
Do While CellContents <> ""
.RunMacro "NewMacros.Location_Code_Input", ""
Let LocationCount = LocationCount + 1
Loop
End If

What am I doing wrong?

 

[macropod]

Hi newguy86,

You've dimmed:
ExcelWorkbook As Excel.Worksheet
which is why you're getting the error. You might have intended:
ExcelWorkbook As Excel.Workbook

Cheers

[MS MVP - Word]

 

[newguy86]

Thank you. That seemed to fix the problem. Now I seem to be getting this error message.

"Application-defined or object-defined error"

For this string.

StartPos = ExcelWorkbook.Sheets("Sheet1").Range("A & LocationCount")

And I have tried everything I could think of to get it to work. I have changed the Dim statements that affect the string and even added in,

Dim StartPos as (attempted various items),

but it still doesn't seem to take. I have even tried searching through Google to find an answer but nothing has worked so far.

Do you have any suggestions?

 

[combo]

StartPos = ExcelWorkbook.Sheets("Sheet1").Range("A[!]"[/!] & LocationCount)

combo