problem with type mismatch error

[John Keith]

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
(e-mail address removed)

 

[Dave Patrick]

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

 

[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/eee/issue_no_9_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.

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
(e-mail address removed)

 

[John Keith]

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
(e-mail address removed)

 

[John Keith]

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

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
(e-mail address removed)

 

[JLGWhiz]

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.

 

[Dave Peterson]

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

 

[Dave Peterson]

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:

 

[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.

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
(e-mail address removed)

 

[JLGWhiz]

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.

 

[Rick Rothstein]

String is also the name of a built in function in VB, so it should doubly be
avoided as a variable name. By way of information, the String function
returns repeated characters; for example String(10,"@") will return 10 "at"
symbols.

 

[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:

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
(e-mail address removed)

 

[Rick Rothstein]

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)

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
(e-mail address removed)

 

[John Keith]

On Sun, 12 Oct 2008 22:47:35 -0400, "Rick Rothstein"

Rick,

A few comments...

Thank you for the tutorial!

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

Hey eagle eyes, right on! That solved the problem with that test case.
Now back to the others to see if I can find what wrong with them.

Thank you Rick.


John Keith
(e-mail address removed)

 

[John Keith]

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
(e-mail address removed)

 

[Dave Peterson]

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.)

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
(e-mail address removed)

 

[Dave Peterson]

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

Ignore the first half of my reply.

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.)

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
(e-mail address removed)

 

[John Keith]

On Mon, 13 Oct 2008 06:59:31 -0500, Dave Peterson

Dave,

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.

With my poor typing skills a good suggestion.

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.

I've wondered about a network issue as well, but the problem remaining
with the local file is confusing. I'm going to concentrate on the
local file and see if I canfix it then try the network file. BTW, my
simplified code to experiment with has a message box asking if I want
local or network source and I select the path varialbe accordingly,
all the rest of the code is identical (and yes, I did check the
apostrophe!)

It's almost like the first time through the loop excel catches the
error and then says, oh, I knlw what you mean and works OK after that.

Another piece of data for anyone's consideration:

The original code actualy reads data from two different network files
(for comparison using later code, which all works great at this
point!) usig the same process to download the data, just different
path, etc. The behavior of the orginal code is:

1) open file
2) run macro
3) get error on first attempt to download from first file
4) run macro again, successfully download data from first file but get
error message on second file
5) run macro again and all data is downloaded and processing of info
proceeds successfully
6) at this point edits/changes can be made to the code and rerunning
the macro continues to work perfectly

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.

I'll try these tonight (bummer I have to go to work soon). I've also
thought about trapping on the error and then entering the loop but I
haven't succeeded with that process yet.

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.)

My very first version of a workbook to compare the data between the
two network files did have a formula in every necessary cell to read
the data and then a macro was started to do the analysis. But the
opportunity to all the work in a macro was very attractive to me for a
lot of reasons (not the least of which is this is a huge learning
opportunity).

Thank you for continuing to follow up with me!


John Keith
(e-mail address removed)

 

[John Keith]

On Mon, 13 Oct 2008 06:59:31 -0500, Dave Peterson


Dave,

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.

All good suggestions but no success.

I removed the .value = .value statement and put it in a second loop to
be executed after putting the formulas in the cells with no success,
even with adding a pause between the execution of the two loops. I'm
suspicious there is some timing issue here but I don't see a way to
work around it yet.

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.)

I'm leaning this way now, but there are a couple of questions since
this will put me in some uncharted territory.

1) Is there a way to open a file with only the target sheet there?
(There are a LOT of sheets in the workbook)

2) Lacking #1 is there an easy way to delete all but the target sheet?
(Something like select the target sheet, toggle the selection then
delete?)

3) I am actually loading data from two different files, I can open the
first file, extract all the data I need, but then what happens when I
open the second file, what do I need to learn about having two files
open, or is that even possible?


John Keith
(e-mail address removed)

 

[Dave Peterson]

I don't have any more suggestions for the formula evaluation problem.

But...

#1. Nope. You open the entire workbook. You open it in readonly mode and
delete all the other worksheets, but I'm not sure what that would save.

#2. Nope. Make sure that the target sheet is visible (at least one sheet has
to be visible), then loop through the worksheets and delete them if the name
doesn't match.

Dim wks as worksheet
dim myName as string
myName = "Target"
set wks = nothing
on error resume next
set wks = otherworkbook.worksheets(myname)
on error goto 0
if wks is nothing then
msgbox "no sheet named target"
exit sub
end if
wks.visible = xlsheetvisible
for each wks in otherworkbook.worksheets
if lcase(wks.name) = lcase(myname) then
'skip it
else
application.displayalerts = false
wks.delete
application.displayalerts = true
end if
next wks

========
An alternative would be to just copy (temporarily) the target sheet into a new
workbook and close the otherworkbook.

otherworkbook.worksheets(myname).copy 'to a new workbook
otherworkbook.close savechanges:=false

(or copy it to your current workbook (and delete it later???)

#3. Use variables to represent both workbooks.

Dim Wkbk1Name as string
dim Wkbk2Name as string
dim Wkbk1 as workbook
dim wkbk2 as workbook

wkbk1name = "C:\somepath\somename.xls"
wkbk2name = "c:\someotherpath\someothername.xls"

'the names must be different (disregarding the drive/path)
set wkbk1 = workbooks.open(filename:=wkbk1name, readonly:=true)
set wkbk2 = workbooks.open(filename:=wkbk2name, readonly:=true)

for each wks in wkbk1.wks
.....