Couple of thoughts.

Is wksname a string containing the sheet name, or an object with the sheet?
If the latter, try wksname.Name.

Does the sheet name include spaces, if so, try something like

strLastTest = "='" & wksName & "'!A" & lr

Finally, is lr initialised?

If none of these, show the code that initialises the variables, and where
the error occurs.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"deko" wrote in message
...
I'm kind of green with Excel - this may well be an elementary issue...

I add a series of worksheets to a new workbook with automation from
Access.
But the first worksheet in the workbook is a summary worksheet and simply
displays data that's already on the other worksheets - so I figure I can
just point to the cells in the other worksheets with a formula to get
those
values.

Here's what I'm using as formulas:

strLastTest = "=" & wksName & "!A" & lr
strAverage = "=" & wksName & "!B" & lr
strStdDev = "=" & wksName & "!C" & lr
(lr is a variable that holds the last row value)

Here's how I'm trying to insert the formulas:

xlapp.Workbooks(strXlsFile).Worksheets("Summary"). Cells(i + 1, 2).Formula
=
strLastTest
xlapp.Workbooks(strXlsFile).Worksheets("Summary"). Cells(i + 1, 3).Formula
=
strAverage
xlapp.Workbooks(strXlsFile).Worksheets("Summary"). Cells(i + 1, 4).Formula
=
strStdDev
(This code is in a loop so the i + 1 just adds the next summary data in
the
next row)

I keep getting: Error Number1004: Application-defined or object-defined
error

I'm not sure if there's something wrong with my code (probably) or if the
problem is due to the fact that "wksName & "!B & lr" (and the others) is a
cell that gets its value from a formula. Could this cause the problem?
Am
I referencing the cells on the other worksheets correctly?

Thanks in advance.