Using Excel "Cells" function in Access Module

  • Thread starter Thread starter Dkline
  • Start date Start date
D

Dkline

My database is using Excel as its calculation engine. When returning a
calculated value to the recordset, I have to get the value from a cell based
on input e.g. return the value from year 5, month 1.

Basically the row in the spreadsheet from which I am to get the value
changes based on the user input. In Excel I would normally use the
Cells(RowIndex, ColumnIndex) function.

My code is
intRowValue = intRowOffset + ((intYear - 1) * 12) + intMonth
'example - year 2, month 1 is in row 20 on the worksheet
rec("xlCOI").Value = objWSVL.Range(Cells(intRowValue, 12)).Value

If I use the above I get this error "<Method 'Cells' of object '_Global'
failed>

If I use just Range it works fine e.g. rec("xlCOI").Value =
objWSVL.Range("L57")).Value

Does this mean I can't use Cells?
 
You need to qualify the Cells with the worksheet object too.

rec("xlCOI").Value = objWSVL.Range(objWSVL.Cells(intRowValue, 12)).Value
 
That was the problem.

What I ended up with is:
rec("xlCOI").Value = objWSVL.Range(objWSVL.Cells(intRowValue, 12),
objWSVL.Cells(intRowValue, 12)).Value

You had to dilenate the range by specifing the range as Range(cell1, cell2).
It didn't work using just (cell1) - had to specifiy both and include the
object with each Cell(row,column).

Thanks for the help.
 
Back
Top