Why there's a Type-Mismatch?

[prabha_friend]

Code where the Type-Mismatch occurs:
SourceFile.Worksheets(SlideNshapes!Shape_Sheet)

SlideNShapes is a DAO recordset

Shape_Sheet is a text field

Current value in SlideNshapes!Shape_Sheet is "New Format"

Why SourceFile.Worksheets("New Format").someproperty is working fine but not the
SourceFile.Worksheets(SlideNshapes!Shape_Sheet)

Even a Cstr() does the job but why not from recordset?

 

[spikepl]

Try

SlideNshapes!Shape_Sheet.Value

 

[vbaInet]

I suspect that Shape_Sheet is not a normal worksheet so you will need to use the Sheets collection, not Worksheets:

SourceFile.Sheets(SlideNshapes!Shape_Sheet)

 

[The_Doc_Man]

I'm guessing here, but the fact that the CSTR fixes it makes me think that somehow the recordset field, when used directly, is a VARIANT. I don't know why the Excel Application object would have trouble with variant datatypes, but apparently it does. Using CSTR or storing the value in a String variable first would remove the ambiguity inherent in using ANY variant datatype.

 

[vbaInet]

Ignore my last post. I somehow missed the fact that Shape_Sheet is a field in a recordset. I was thinking that the name "Shape_Sheet" meant that it was perhaps a Chart object.

Following The_Doc_Man's comments I think that the argument passed to CStr() is ByVal and as a result it gets a copy of the value from the field and returns it as a String. It would appear that this same operation is equivalent to:

Dim strShapeSheet As String

strShapeSheet = SlideNshapes!Shape_Sheet

SourceFile.Worksheets(strShapeSheet)

... which should also work. Something to do with the compiler getting things muddled up.

Or it could be the explicit call like spikepl mentioned.

 

[The_Doc_Man]

Usually I find SpikePL's comments spot-on, but adding .Value to the formal argument in this case might not be helpful since .Value is the default property to return from any object that has a .Value property.

Thinking about this more, I realize that in SourceFile.Worksheets(strShapeSheet), the argument HAS to be a variant - because it can be passed as either a numeric index OR a text name. To the best of my knowledge, VBA does not allow you to overdefine a subroutine call based on the datatype of the formal parameter. (Compare to ADA language, where the implied call of .Worksheets(number) is a DIFFERENT ENTRY POINT than the implied call of .Worksheets(string). I.e. it is an overloaded definition.

My question is therefore whether the datatype of the field in the recordset is being properly conveyed to .Worksheets() calls. The fact that using CStr() makes this work tells me that the answer is somehow NO, though I'm baffled at the moment as to how that could be. Is the thing opened by the recordset perhaps a UNION query or dynamic string query? (I.e. the recordset's OPEN is an SQL string?) Or is the field's name declared in the query using AS syntax to rename it?

 

[spikepl]

@The_Doc_Man

but adding .Value to the formal argument in this case might not be helpful since .Value is the default property to return from any object that has a .Value property.

I do not disagree, but there are instances ... Fx if you try to add attachments in Outlook with paths stored in a recordset and using .Attachments.Add(rst!somefield) , it is exactly the supposedly default .Value that makes a difference between failure and success

I know that contradicts the theory ...