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