Running the following code produces a type mismatch error when the
line with the ExecuteExcel4Macro is reached. But if the code is re-run
again with no changes the error does not occur.

I've done some web searching and found some suggestions that I've
tried (Dim arg as Variant and double quotes for example) but nothing
has eliminated the error yet.

If all else fails I'll do an on error resume next but first I'd like
to see if the problem can be corrected. Any ideas?

Thanks



Dim arg As Variant

Application.ScreenUpdating = False

Max_Row = 500
Path = "'C:/"
file = "[Analysis.xls]"
Sheet = "Pn Summary'!"
string = Path & file & Sheet

source_c = 1
dest_c = 3
For r = 1 To Max_Row
a = Cells(r + 4, source_c).Address
arg = string & Range(a).Range("A1").Address(, , xlR1C1)
Cells(r, dest_c) = UCase(ExecuteExcel4Macro(arg))
If Cells(r, dest_c) = 0 Then Cells(r, dest_c).ClearContents
Next r


John Keith

The "'C:/" is incorrect. Should be "'C:\" No clue otherwise.



--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect


"John Keith" wrote:
Running the following code produces a type mismatch error when the
line with the ExecuteExcel4Macro is reached. But if the code is re-run
again with no changes the error does not occur.

I've done some web searching and found some suggestions that I've
tried (Dim arg as Variant and double quotes for example) but nothing
has eliminated the error yet.

If all else fails I'll do an on error resume next but first I'd like
to see if the problem can be corrected. Any ideas?

Thanks



Dim arg As Variant

Application.ScreenUpdating = False

Max_Row = 500
Path = "'C:/"
file = "[Analysis.xls]"
Sheet = "Pn Summary'!"
string = Path & file & Sheet

source_c = 1
dest_c = 3
For r = 1 To Max_Row
a = Cells(r + 4, source_c).Address
arg = string & Range(a).Range("A1").Address(, , xlR1C1)
Cells(r, dest_c) = UCase(ExecuteExcel4Macro(arg))
If Cells(r, dest_c) = 0 Then Cells(r, dest_c).ClearContents
Next r


John Keith

On Sun, 12 Oct 2008 09:27:03 -0600, "Dave Patrick"
wrote:

The "'C:/" is incorrect. Should be "'C:\" No clue otherwise.

Sorrt, I actually edited my code before posting so as not to publish
the lengthy path which was actually to an internet location which
didn't need to be broadcast to the group.

But thanks for looking.

John Keith

xl2003 and winXP home didn't mind the slash in place of the backslash.



John Keith wrote:

On Sun, 12 Oct 2008 09:27:03 -0600, "Dave Patrick"
wrote:

The "'C:/" is incorrect. Should be "'C:\" No clue otherwise.

Sorrt, I actually edited my code before posting so as not to publish
the lengthy path which was actually to an internet location which
didn't need to be broadcast to the group.

But thanks for looking.

John Keith


--

Dave Peterson

Your code worked ok for me.

It was based on the GetValue function from John Walkenbach's site:
http://j-walk.com/ss/excel/eee/eee009.txt
or
http://spreadsheetpage.com/index.php..._july_15_1999/

I'm not sure if your code is a skinnied down example of what you're doing, but
if you're just plopping the value into a cell, it may be easier to find the
problem if you just put a formula to return the value directly into your cell:

For r = 1 To Max_Row
a = Cells(r + 4, Source_C).Address
arg = myString & Range(a).Range("A1").Address(, , xlR1C1)
With Cells(r, Dest_C)
.numberformat = "General" '???? just not text!
.Formula = "=" & arg
'.Value = .Value
'If .Value = 0 Then
' .ClearContents
'End If
End With
Next r

When you're done debugging, you can remove the commented lines.

John Keith wrote:

Running the following code produces a type mismatch error when the
line with the ExecuteExcel4Macro is reached. But if the code is re-run
again with no changes the error does not occur.

I've done some web searching and found some suggestions that I've
tried (Dim arg as Variant and double quotes for example) but nothing
has eliminated the error yet.

If all else fails I'll do an on error resume next but first I'd like
to see if the problem can be corrected. Any ideas?

Thanks

Dim arg As Variant

Application.ScreenUpdating = False

Max_Row = 500
Path = "'C:/"
file = "[Analysis.xls]"
Sheet = "Pn Summary'!"
string = Path & file & Sheet

source_c = 1
dest_c = 3
For r = 1 To Max_Row
a = Cells(r + 4, source_c).Address
arg = string & Range(a).Range("A1").Address(, , xlR1C1)
Cells(r, dest_c) = UCase(ExecuteExcel4Macro(arg))
If Cells(r, dest_c) = 0 Then Cells(r, dest_c).ClearContents
Next r


John Keith


--

Dave Peterson

On Sun, 12 Oct 2008 10:29:03 -0500, Dave Peterson
wrote:

Dave,

Your code worked ok for me.

My code works fine the second time it is run after I get the erro
rmessage :-(

It was based on the GetValue function from John Walkenbach's site:
http://j-walk.com/ss/excel/eee/eee009.txt

You're very astute! The url looks different from what I remember but
yes, I did pick up the tip from John W.

I'm not sure if your code is a skinnied down example of what you're doing,

The code is the same as what I am running with the exception of
changing the path, file and string variables for a little privacy as I
noted in the reply to the other post in this string.

if you're just plopping the value into a cell

Yes, that is all I am doing.

it may be easier to find the
problem if you just put a formula to return the value directly into your cell:

I'll play with your suggestion. If it works why wouldn't I just
continue to use this process?

Thanks!
John Keith

It could be balking at the variable "string" since that is a reserved word.
Try changing it to "strg" or "myPath" or anything not reserved for VBA
constants or data types nad see if it still hiccups on the first loop.

"John Keith" wrote:

On Sun, 12 Oct 2008 10:29:03 -0500, Dave Peterson
wrote:

Dave,

Your code worked ok for me.

My code works fine the second time it is run after I get the erro
rmessage :-(

It was based on the GetValue function from John Walkenbach's site:
http://j-walk.com/ss/excel/eee/eee009.txt

You're very astute! The url looks different from what I remember but
yes, I did pick up the tip from John W.

I'm not sure if your code is a skinnied down example of what you're doing,

The code is the same as what I am running with the exception of
changing the path, file and string variables for a little privacy as I
noted in the reply to the other post in this string.

if you're just plopping the value into a cell

Yes, that is all I am doing.

it may be easier to find the
problem if you just put a formula to return the value directly into your cell:

I'll play with your suggestion. If it works why wouldn't I just
continue to use this process?

Thanks!
John Keith

On Sun, 12 Oct 2008 12:31:00 -0700, JLGWhiz
wrote:

It could be balking at the variable "string" since that is a reserved word.
Try changing it to "strg" or "myPath" or anything not reserved for VBA
constants or data types nad see if it still hiccups on the first loop.

My bad.

My original code had very long variable names that I shortened before
posting. I changed "blah_blah_blah_string" to just "string".

I'm still experimenting but without success so far. I'm still
perplexed by the observation that the code will fail when I first open
the workbook and call the macro, and then when called again
immediately following the failure it will pass.

( I had been doing all my development with the workbook open
continuously and I didn't see this problem until I closed and
re-opened the workbook.)


John Keith

that is what I was suggesting, that the word "string" might be the cause of
the message popping up. String is a reserved word for data type. Sometimes
when reserved words are used and are not capitalized, the compiler sees it as
a mistake and sends what it believes to be the appropriate error message.
Other times it picks it up as the variable that the writer intended it to be
and runs without the message. I don't know that it is happening in this
case, but I don't see anything else that would cause the problem.

"John Keith" wrote:

On Sun, 12 Oct 2008 12:31:00 -0700, JLGWhiz
wrote:

It could be balking at the variable "string" since that is a reserved word.
Try changing it to "strg" or "myPath" or anything not reserved for VBA
constants or data types nad see if it still hiccups on the first loop.

My bad.

My original code had very long variable names that I shortened before
posting. I changed "blah_blah_blah_string" to just "string".

I'm still experimenting but without success so far. I'm still
perplexed by the observation that the code will fail when I first open
the workbook and call the macro, and then when called again
immediately following the failure it will pass.

( I had been doing all my development with the workbook open
continuously and I didn't see this problem until I closed and
re-opened the workbook.)


John Keith

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:

Option Explicit
Sub testme()
Dim arg As Variant
Dim myString
Dim Max_Row As Long
Dim Source_C As Long
Dim Dest_C As Long
Dim myPath As String
Dim mySheet As String
Dim myFile As String
Dim r As Long
Dim a As String
Dim res As Variant

Application.ScreenUpdating = False

Max_Row = 500
myPath = "'C:/"
myFile = "[Analysis.xls]"
mySheet = "Pn Summary'!"
myString = myPath & myFile & mySheet
'myString = "'C:\my documents\excel\[book1.xls]sheet1'!"

Source_C = 1
Dest_C = 3
For r = 1 To Max_Row
a = Cells(r + 4, Source_C).Address
arg = myString & Range(a).Range("A1").Address(, , xlR1C1)
With Cells(r, Dest_C)
.Formula = "=" & arg
'.Value = .Value
'If .Value = 0 Then
' .ClearContents
'End If
End With
Next r

Application.ScreenUpdating = True

End Sub

John Keith wrote:
<<snipped

if you're just plopping the value into a cell

Yes, that is all I am doing.

it may be easier to find the
problem if you just put a formula to return the value directly into your cell:

I'll play with your suggestion. If it works why wouldn't I just
continue to use this process?

Thanks!
John Keith


--

Dave Peterson

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

A few comments...

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?

One major benefit is it helps you to spot typing errors in your variable
names. For example, if you declared this...

Dim AdjustmentFactor As Double

and then later on mistyped it in an assignment operation like this...

AdjutsmentFactor = 12.34

then VB would flag your use of AdjutsmentFactor as an error helping you to
spot the mistyping. Without Option Explicit, the variable would default to
zero... if you never noticed the typing error, then (depending on how the
variable is used) there is a strong possibility that your code would
generate incorrect results and you might never know it.

Dim r, i As Long
Dim a, Answer As String

The above two lines are not doing what you think. Only i and Answer are
declared like you expect... r and a are both declared as Variants. In VB,
you have to individually declare the type of a variable. So, either this...

Dim r As Long, i As Long
Dim a As String, Answer As String

or this...

Dim r As Long
Dim i As Long
Dim a As String
Dim Answer As String

myPath = "C:\Documents and Settings\keithjo\My Documents\"
myFile = "[test.xls]"
mySheet = "Sheet1'!"

See the apostrophe after Sheet1 in mySheet... it needs a companion in front
of the C: in myPath.

--
Rick (MVP - Excel)


"John Keith" wrote in message
...
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

On Sun, 12 Oct 2008 20:19:39 -0600, John Keith wrote:

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.

In my playing with this issue I just learned anothe interesting piece
of info. Let me first reiterate the behavior I tried to describe
above:

1) open file
2) run macro - cells are filled with #REF
3) change nothing, run macro again, get correct data in cells

(This behavior is the same if the file is on my local disk or accessed
across the network.)

The new peice of data is:

I put a breakpoint at the end of the loop (next r statement) and here
is what comes out:

1) open file
2) run macro
3) stops at first pass through loop, first cell filled with #REF
4) hit continue - next cell filled with correct data
5) hit continue - all remaining cells filled with correct data

(Again, this behavior is the same if the file is on my local disk or
accessed across the network.)



John Keith

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