Help with VBA code please

[baronvont]

I am trying to run a bit of VBA from access to do actions in an Excel sheet. The code goes like this..

Set objXL = GetObject(XLS_LOCATION)
Set objSheet = objXL.Worksheets("sheet1&quot


With objXL

.ActiveSheet.Range("D2&quot.Select
.ActiveSheet.Cells.Find(TheNumber, LookAt:=xlWhole).Activate
TheCell = .ActiveCell.Address

End With

I need to find the address (specifically row number) of the cell containing the number I found. Now the 1st 2 lines in the with section execute fine, but the 3rd give err "Object doesn't support this property or method"

I can run the same bit of code from XLVBA and its fine to I am sure the syntax is ok.

Does anyone have any ideas please?

Thanks
Georg

 

[Chance1234]

try this

Sub dffdf()

Dim objxl As Excel.Workbook
Dim objsheet As Excel.Worksheet
Dim thenumber As Integer
Dim thecell As String



Set objxl = GetObject("worksheetlocation.xls&quot
Set objsheet = objxl.Worksheets("test&quot



thenumber = 1

With objsheet
thecell = .Cells(1, 1).Find(thenumber, LookAt:=xlWhole).Address

MsgBox thecell
End With


End Sub