Can Not Rename Sheet By Invoke Code

Help
1

For some reason, we can not use [BalaReva.Excel.Activities.] to rename sheet.
So I wrote a invoke code to resolve this problem.
But I encounter a [: Error compiling code ] mentioned that :

error BC30574: Option Strict On では、遅延バインディングを使用できません。 At line 30

I tried to work on this by many method,but no result.
Please help.

Here is my code:::

Dim InvokeProp As Func(Of Object, String, Object(), Object) = _
    Function(target As Object, ParamName As String, Args As Object()) As Object
        Return target.GetType().InvokeMember(ParamName, Reflection.BindingFlags.GetProperty Or Reflection.BindingFlags.Instance, Nothing, target, Args)
    End Function

Dim InvokeMethod As Func(Of Object, String, Object(), Object) = _
    Function(target As Object, ParamName As String, Args As Object()) As Object
        Return target.GetType().InvokeMember(ParamName, Reflection.BindingFlags.InvokeMethod Or Reflection.BindingFlags.Instance, Nothing, target, Args)
    End Function

Dim InvokePropSet As Func(Of Object, String, Object(), Object) = _
    Function(target As Object, ParamName As String, Args As Object()) As Object
        Return target.GetType().InvokeMember(ParamName, Reflection.BindingFlags.SetProperty Or Reflection.BindingFlags.Instance, Nothing, target, Args)
    End Function

 
 Dim ReleaseObj As Action(Of Object) = _
    Sub(target As Object)
        Runtime.InteropServices.Marshal.ReleaseComObject(target)
    End Sub

Dim xlApp As Object = CreateObject("Excel.Application")
Dim xlBooks As Object = InvokeProp(xlApp, "WorkBooks", Nothing)
Dim xlBook As Object = InvokeMethod(xlBooks, "Open", New Object(0) {TargetFile})

Dim targetWorkSheets  As Object = InvokeProp(xlBook, "WorkSheets", Nothing)

Dim paraName As Object = New Object(0){TargetSheetName}

targetWorkSheets(0).InvokeMember("Name",Reflection.BindingFlags.SetProperty, Nothing,paraName)

ReleaseObj(targetWorkSheets)

InvokeMethod(xlBook, "Close", New Object(0) {False})
ReleaseObj(xlBook)
ReleaseObj(xlBooks)

InvokeMethod(xlApp, "Quit", Nothing)
ReleaseObj(xlApp)
2

Hello,

It would be easier to help if you attach the workflow with the Invoke Code. I tried the code you put in the snipped but I get many other errors, I guess something else is missing.

Regards
Silviu

3

try this VB.ney code which [aksh1yadav] has mentioned:

4

You want to rename first excel sheet, right?

Try this VB Code.

Dim InvokeProp As Func(Of Object, String, Object(), Object) = _
    Function(target As Object, ParamName As String, Args As Object()) As Object
        Return target.GetType().InvokeMember(ParamName, Reflection.BindingFlags.GetProperty Or Reflection.BindingFlags.Instance, Nothing, target, Args)
    End Function

Dim InvokeMethod As Func(Of Object, String, Object(), Object) = _
    Function(target As Object, ParamName As String, Args As Object()) As Object
        Return target.GetType().InvokeMember(ParamName, Reflection.BindingFlags.InvokeMethod Or Reflection.BindingFlags.Instance, Nothing, target, Args)
    End Function

Dim InvokePropSet As Func(Of Object, String, Object(), Object) = _
    Function(target As Object, ParamName As String, Args As Object()) As Object
        Return target.GetType().InvokeMember(ParamName, Reflection.BindingFlags.SetProperty Or Reflection.BindingFlags.Instance, Nothing, target, Args)
    End Function

 
 Dim ReleaseObj As Action(Of Object) = _
    Sub(target As Object)
        Runtime.InteropServices.Marshal.ReleaseComObject(target)
    End Sub

Dim xlApp As Object = CreateObject("Excel.Application")
Dim xlBooks As Object = InvokeProp(xlApp, "WorkBooks", Nothing)
Dim xlBook As Object = InvokeMethod(xlBooks, "Open", New Object(0) {TargetFile})

Dim targetWorkSheets  As Object = InvokeProp(xlBook, "WorkSheets", Nothing)

'NOTE: Array is "1 origin" in Excel Object Model.
Dim targetWorkSheet As Object = InvokeProp(targetWorkSheets, "Item", New Object(){1})

InvokePropSet(targetWorkSheet, "Name", New Object(){TargetSheetName})

InvokeMethod(xlBook, "Save", Nothing)

ReleaseObj(targetWorkSheet)
ReleaseObj(targetWorkSheets)

InvokeMethod(xlBook, "Close", New Object(0) {False})
ReleaseObj(xlBook)
ReleaseObj(xlBooks)

InvokeMethod(xlApp, "Quit", Nothing)
ReleaseObj(xlApp)

And argument setting is:

image
image700×200 10.6 KB

However, I think using COM and reflection are last resort.
It should be deprecated, because to use reflection often causes bugs or troubles like memory leak, bindings error.

Yes, this code works with same architecture with VBA, but you need learn how .NET invokes COM methods by reflection.
Don’t forget that these architectures are very sensitive and difficult, not easy like Excel VBA.

1 Like
5

Thanks for your response.

I just run the code by using a single Activity : [Invoke Method] .
But in one machine ,it got a error like

Invoke code : Error compiling code
error BC30456: ‘BindingFlags’ は ‘Reflection’ のメンバーではありません。 At line 3

And other maybe will running without errors.
Have you been the same error?

6

Thanks for your response.

Actually , I check that issue and also run the resolving robot,
But it never make any change to my problem.
So I had to went into help.

7

Thanks for your response and code , and it did worked.

However,as you have mentioned above, using COM and reflection are the last resort.
I’m trying to do another way to resolve this issue without COM and reflection.
Thanks for your advise.

8

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.