VBA: "Evaluate" does not work with closed workbooks, but the same function does on sheet

[kocas]

here is a sample code:

Public Function getest(esttype, yr)
Dim strf As String

strf = "=counta('m:\folder\mds\[rating index.xls]" & esttype & Right(yr, 2) & "'!$1:$1)"

MsgBox Evaluate(strf)



End Function

for "sales" and 2009, what strf reads is:

=counta('m:\folder\mds\[rating index.xls]sales09'!$1:$1)

when i evaluate this string, i get "1" in the msgbox.

the exat same string returns 80 when i put it in a cell.

if i use Vlookup in a similar way, evaluate gives type mismatch error.

Any ideas ?

 

[Andrew Poulsom]

Evaluate cannot resolve a reference to a closed workbook (as you have discovered).

 

[kocas]

well, i was in denial : )

the thing is, i can get around this when i am working in subs. I use a temporary named cell say temp, and i go

[temp].formula = strf

whateverineed = [temp].value

[temp].clearcontents

but as you may know, i cannot use this with udfs, as they dont edit any cell other than they are housed by. Just wondering whether anyone gave this a thought.

Making this work would make life very easy. Closed-workbook data pulling would work like a champ. Well, it kind of already is, if i use subs.

p.s. calling a sub from a func does not work either.


thanks for the reply Andrew.

 

[sailepaty]

Try to adapt John's get a value from a closed file function.

http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/

Regards

 

[moizsherwani]

Evaluate cannot resolve a reference to a closed workbook (as you have discovered).

Hi Andrew, can you give us an example of evaluate referencing an open work book since I cannot get that to work too.

 

[Andrew Poulsom]

What did you try?

 

[moizsherwani]

USCityShort = Evaluate("=match(" & TempString & ",' " & fpath & "[" & fname & "]" & sname1 & "'!$X:$X,0)")

 

[Andrew Poulsom]

The fact that you are using a variable named fpath indicates that the workbook isn't open.

 

[MrBillBenson]

The fact that you are using a variable named fpath indicates that the workbook isn't open.

I don't see it that way. You can put in the fully qualified workbook path using that structure the OP used, and Excel will remove the path if the workbook is opened. It is a little unnerving.

Say Andrew, I don't know much about Excel4Macros but... could you possibly get any of those to give information about a closed workbook?