On Sun, 12 Oct 2008 15:48:28 -0500, Dave Peterson
wrote:

I would continue to just plop the value into cell. I'd drop the old xlm code
completely.

Ps. I did change some of the variables (like JLGWhiz suggested).

I should have shared the modified code:


Dave,

Thank you for the follow up. I've learned a couple new things which
I'm always thankful for.

I have tried your code and have a few comments and questions. I did
rip out all the unnecessary code (like clearing the cell contents if
0) just to simplify the isolation of the cause of any error, and I
shortened the loop to speed up the process of getting results, and
finally I eliminated some variables and put explicit values in, again
just to simplify the code down to bare minimum.

The Option Explicit, this appears to require the need for every
variable to be declared in a Dim statement? What was the usefulness of
this?

Your code inserts the formula to reference the external workbook. And
I think I understand that .value = .value will replace the formula
with the value of the cell.

But here's the interesting result. When I run this code I get #REF in
each cell. Each time I subsequently run the macro the right contents
are inserted into the cell. Is there some command needed to cause the
formula inserted into the cell to be evaluated? BTW, the results were
the same with the source file located at its url over the internet or
with a copy of the file located in My Documents on my local drive.

Wanting simplify a little more I made a small spreadsheet called test
with data in a1 through A10 and put it in My Documents. Then I ran the
following code (which is identical to the simplifid code I used above
but now references this simple spreadsheet) and I get a different
error. The error is 1004, application or obect defined error on the
..formula statement.

I'm baffled by what is going on.

Here's the code for the last experiement I described.

Option Explicit
Sub Build_Report()

Dim arg As Variant
Dim myString As String
Dim myPath As String
Dim mySheet As String
Dim myFile As String
Dim r, i As Long
Dim a, Answer As String
Dim res As Variant
Dim test_string, both As String


myPath = "C:\Documents and Settings\keithjo\My Documents\"
myFile = "[test.xls]"
mySheet = "Sheet1'!"
myString = myPath & myFile & mySheet
MsgBox myString
For r = 1 To 10
a = Cells(r, 1).Address
arg = myString & Range(a).Range("A1").Address(, , xlR1C1)
With Cells(r, 1)
.Formula = "=" & arg
.Value = .Value
End With
Next r
End Sub


John Keith