how to refer to a range of cells in excel from within Access 1

[davikokar]

Hallo,

I'm using this code to refer to a range of cells in an excel sheet from Access:

Sht.Range(Cells(1, 1), Cells(uRow - 1, 1)).Interior.ColorIndex = "6"

where Sht is:

Set Sht = xlApp.ActiveWorkbook.Sheets(2)

and uRow is an integer variable

I also tried to use this:

Sht.Range(.Cells(1, 1), .Cells(uRow - 1, 1)).Interior.ColorIndex = "6"

but without success...

it is strange because if I use the same sintax within a "WITH/END WITH" sentence it does work:

With Sht
    .Range(.Cells(1, 1), .Cells(uRow - 1, 1)).Interior.ColorIndex = "6"
End With

Any ideas? thanks

 

[jebry]

Hi!

In this case you need the dot operator because Cells is a property of a sheet, that is why your first line of code doesn't work. The second line doesn't work because the .Cells isn't related to an object, which is also why the third line of code does work. An alternative is:

Sht.Range("A1:A" & Format(uRow - 1))

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[davikokar]

thanks jebry for clarifications and suggestion