Hi Kishor,
This is the call that works. (Names changed)
oWorkSheet = oExcel.ActiveSheet
MsgBox (oWorkSheet.Cells.Range("F18").Value)
This is the call that fails:
MsgBox (CallByName (oWorkSheet, "Cell", CallType.Get, "F18"))
The reason is very simple. There is no method of oWorkSheet called 'Cell'.
If you look at the call that works, you'll see that it calls 'Cells' - plural.
That clears up <that> problem but it does nothing to solve your original
question - how can you call an Excel function that has been defined as a
string. It's fine if it's just a single function like 'Cells' because
CallByName will work perfectly with <that>. But you can't call
'Cells.Range("F18").Value' as this is <series> of calls.
Given
Dim O As Object
then
O = oWorkSheet.Cells.Range("F18").Value
is the equivalent of
O = oWorkSheet.Cells
O = O.Range("F18")
O = O.Value
and CallByName cannot do that for you.
What is needed is a way to call each of these in turn, providing the
arguments as appropriate. One way is for you to store your function string as
an array of separate functions and execute these in turn - using the result of
one as the object of the next. For example, 'Cells', 'Range("F18")', 'Value'.
The other is to have a function which wll accept the whole string,
'Cells.Range("F18").Value', and break it down into the required steps,
returning the final result.
Well, it just so happens that I've written it for you and it will accept
your string straight from the database.
You can call it like so:
O = CallByNameSeries (oWorkSheet, "Cells.Range(""A1"").Value")
It has limitations though. The only arguments that it allows are a single
string per function - Range("F18:G25") is fine, but Range("F18", "G25") is
not. And all functions must <be> functions - not methods.
Give it a go, anyhow, and see if it's of any use to you..
Regards,
Fergus
ps. For anyone else reading this - it will work for any object - not just
Excel objects, and is therefore a (simple) extension to CallByName.
<code>
'An extension of CallByName which accepts a series of
'function calls each with a single optional string argument.
'Functions can be actual Functions or Properties.
'
'Spaces between parts are removed.
'Spaces within an argument are retained.
'
'Examples:
'O = CallByNameSeries (oWorkSheet, "Cells.Range (""A1"").Value")
'O = CallByNameSeries (oFoo, "Parent.Name")
'O = CallByNameSeries (oBar, "Item (""The Elephant"").Size")
'
'Note that "Item (1)" is the same as "Item (""1"")" - ie. a string arg.
'
Public Function CallByNameSeries (oStartObj As Object, _
sFuncString As String) As Object
'Let's start at the very beginning.
Dim oObj As Object = oStartObj
Dim PosOfDot As Integer
Do
'Get the next function name.
Dim sFunc As String = sFuncString
Dim sArg As String = Nothing
'Extract the first function if it's a series.
PosOfDot = sFunc.IndexOf (".")
If PosOfDot >= 0 Then
sFunc = sFuncString.Substring (0, PosOfDot)
sFuncString = sFuncString.Substring (PosOfDot + 1)
End If
'Get any argument as indicated by an opening bracket.
Dim PosOfArg As Integer = sFunc.IndexOf ("(")
If PosOfArg >= 0 Then
'Get the arg and remove the brackets.
sArg = sFunc.Substring (PosOfArg + 1)
sArg = sArg.Replace (")", "")
'Remove any quotes from the argument.
sArg = sArg.Replace("""", "")
'Remove the argument from the function name.
sFunc = sFunc.Substring (0, PosOfArg)
End If
'Call the function.
If sArg Is Nothing Then
oObj = CallByName (oObj, sFunc.Trim, CallType.Get, Nothing)
Else
oObj = CallByName (oObj, sFunc.Trim, CallType.Get, sArg)
End If
'Repeat for each function part.
Loop Until PosOfDot < 0
'Send back the final result.
Return oObj
End Function
</code>
