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

--

Ken Snell
<MS ACCESS MVP>

"Dkline" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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?
>
>

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.


"Ken Snell" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> You need to qualify the Cells with the worksheet object too.
>
> rec("xlCOI").Value = objWSVL.Range(objWSVL.Cells(intRowValue, 12)).Value
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
> "Dkline" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > 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?
> >
> >
>
>