Error : System.InvalidCastException: 'Conversion from string "Sheet1" to type 'Intege

[SamDsouza]

Hello

I get the following error when implemeting to change the Worksheet Codename property

VBA Code

 Dim Excl_App As Excel.Application
 Dim Excl_Wbook As Excel.Workbook
 Dim Excl_Sheet As Excel.Worksheet

Set Excl_Wbook = Workbooks.Open("C:\ABC\"Trial.xlsx") 

Excl_App.Visible = True
Set Excl_Sheet = ActiveSheet

Excl_Wbook.VBProject.VBComponents("Sheet1").Name = "Trial1Sam"
Excl_Wbook.Sheets("Sheet1").Name = "SamD"

VBNET19 Code

Public Class Trial
Public Excl_App As New Microsoft.Office.Interop.Excel.Application() 
Public wBK As Excel.Workbook

Sub SheetChangeTrial()
Dim VBProject As Microsoft.Vbe.Interop.VBProject
Dim VBComp As Microsoft.Vbe.Interop.VBComponent

Dim strFilename As String = "C:\ABC\Trial.xlsx"

Excl_App.Visible = True
wBK = Excl_App.Workbooks.Open(strFilename)
VBComp = wBK.VBProject.VBComponents("Sheet1").Name = "SheetTrial1Sam"

''Following Error on above Line
'System.InvalidCastException: 'Conversion from string "Sheet1" to type 'Integer' is not valid.
End Sub

Thanks
SamD
25 Thread 5: 892237

[fubao]

I'm not sure, if two assign operator(=) in one line is prohibited.
so, try two seperate line

 VBComp = "SheetTrial1Sam"
            wBK.VBProject.VBComponents("Sheet1").Name = "SheetTrial1Sam"

[SamDsouza]

Fubao

I'm not sure, if two assign operator(=) in one line is prohibited.
so, try two seperate line

VBComp = "SheetTrial1Sam"
            wBK.VBProject.VBComponents("Sheet1").Name = "SheetTrial1Sam"

Warning BC42322 Runtime errors might occur when converting 'String' to 'VBComponent'.

It displayed the above warning msg before the build

Thanks
SamD
26 Thread 5: 892237

[fubao]

I didn't look into much detail, but now I do.
Base on my vb2010 code, not sure will work on 19, but I think it does.

=> Basically, the vba and vs code is the same, after you include reference.
There is nothing to do with vbcomponent, it can be done by excel_app itself

So, just use this

Excel_app.worksheets("oldname").Name = "newname"

[SamDsouza]

Sorry Fubao

=> Basically, the vba and vs code is the same, after you include reference.
There is nothing to do with vbcomponent, it can be done by excel_app itself

I am not sure of your above statement because It seems this is also not proper syntax

As i am not getting the

[.Name]

in the Editor

ie the syntax provided by you I can't try because i don't want my Xlsx file to get locked for editing and get corrupted.

By The way i had specifically asked to change the Code property Name of Sheet and not the name oF Sheet which we see on Sheet Tab of excel file.
After Changing the Codeproperty Name of Sheet then only i wanted to change the Sheet Tab name of Worksheet
Although this should not make any difference but...

Thanks
SamD
27 Thread 5: 892237

[fubao]

OK, How about this. should be work

VBComp = wBK.VBProject.VBComponents(Excl_App.worksheets("sheet1").CodeName).Name = "SheetTrial1Sam"

[ChrisE]

it really is quit simple to rename a Sheet

 Private Sub Button34_Click(sender As System.Object, e As System.EventArgs) Handles Button34.Click
        Try
            Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
            Dim xlWb As Microsoft.Office.Interop.Excel.Workbook
            xlWb = xlApp.Workbooks.Open("E:\TestFolder\vbexcel.xlsx")

            'get Sheet3
            Dim xlSt As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWb.Worksheets("Sheet3"), Worksheet)

        

            With xlSt
                'set the new name from Sheet3 to ..... 
                .Name = "Renamed Sheet"
            End With

            xlWb.Save()
            xlApp.Quit()
            xlApp = Nothing
        Catch g As Exception
            MessageBox.Show(g.ToString)
        End Try
    End Sub

@sam, don't make the mistake and try to incorpurate...this

Dim VBProject As Microsoft.Vbe.Interop.VBProject
Dim VBComp As Microsoft.Vbe.Interop.VBComponent

you want to program in .NET , right ?

[SamDsouza]

Chris E

Great .it Executes Perfectly to change the Sheet Name which is seen on the Tab of Worksheet Name without xlsx file being locked for editing and other corruption of file.

@sam, don't make the mistake and try to incorpurate...this
Code:
Dim VBProject As Microsoft.Vbe.Interop.VBProject
Dim VBComp As Microsoft.Vbe.Interop.VBComponent

I've removed the above Two lines.as per your suggestion. Is it dangerous to incorprate the above 2 lines.
There should be someway to Change the CodeName of Sheet
ie (Name) = "Sheet1" change to "SheetTrial1Sam"
With your code only sheet Tab name changes ie
Name = "Sheet1" with your code it changes to "SamD"

The reason for doing is to directly i can use as below

With SheetTrial1Sam.Application
etc
End With

you want to program in .NET , right ?

I've no alternative

Thanks
SamD
28 Thread 5: 892237