Oops. I didn't see Rick's response.

Ignore the first half of my reply.

Dave Peterson wrote:

First, "Option Explicit" does require you to declare your variables. So if you
make a typing mistake and mistype one of the variable names, then your code
won't even compile. If I were you I'd always use it.

Second, when you do this:
Dim r, i As Long
Dim a, Answer As String
Dim test_string, both As String

You are actually doing this:

Dim r as variant, i As Long
Dim a as variant, Answer As String
Dim res As Variant
Dim test_string as variant, both As String

I bet that's not what you meant.

Third, you dropped the leading apostrophe in this statement:
myPath = "C:\Documents and Settings\keithjo\My Documents\"
should be:
myPath = "'C:\Documents and Settings\keithjo\My Documents\"

Fourth (and it's just a guess since it doesn't explain the problem with the
local "sending" file)--maybe there's a network delay.

Maybe you could add:
application.calculate '.calculatefull 'depending on your version
before you convert the range to values.

Or even do an edit|replace in that range:
change = to =
to see if excel will recalc.

If that doesn't work, I think I'd open the "sending" file and just copy|paste
values--or build a formula to the cells in the open file. (Depending on how
many of those formulas you're building, this may even work more quickly.)

John Keith wrote:

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


--

Dave Peterson

--

Dave Peterson