Access to Excel Using the Worksheet's Codename

[neuk]

I wanted to put whatever data in a specific excel worksheet that I wanted to reference by code name (not by index or it's sheet name on the tab). So I wrote the following code which I swear worked for a couple test runs but now it won't even show the code name of the worksheet anymore in the debug.print screen. Am I just losing my mind or what?



Option Compare Database
Option Explicit

Sub test()


Dim xl As Object
Dim wkb As Object
Dim wks As Object
Dim strPath As String
Dim strWksCode As String


strPath = "C:\Users\Me\Desktop\test.xlsx"


Set xl = CreateObject("Excel.Application")


xl.Visible = True
xl.UserControl = True


Set wkb = xl.Workbooks.Open(strPath)


For Each wks In wkb.Worksheets          'This is the only way I found to refer to a worksheet by its code name...code name doesn't work anymore.
    Debug.Print wks.CodeName
    If wks.CodeName = "Sheet2" Then
        Set wks = xl.Worksheets(wks.Name)
        wks.Select
        Exit For
    End If
Next wks


wks.Range("A2") = "test"
wkb.Save


Set wks = Nothing
'objWbk.Close
Set wkb = Nothing
'objExc.Quit
Set xl = Nothing


End Sub

[Gicu]

Found a thread that might help:
https://stackoverflow.com/questions/...using-codename
Cheers,

[Welshgasman]

So have you walked through the code line by line?

[neuk]

So have you walked through the code line by line?

I have walked through the code. When I use the .Name everything works just fine. The block of code looks like this:

For Each wks In wkb.Worksheets
    Debug.Print wks.Name
    If wks.Name = "Sheet2" Then
        Set wks = xl.Worksheets(wks.Name)
        wks.Select
        Exit For
    End If
Next wks

When I change out the .name with .codename property nothing appears in the immediate window when using debug.print...I'm guessing since nothing is assigned to the property, it errors out once hitting the wks.Range("A2")... since nothing is assigned as the worksheet. The block of code looks like this:

For Each wks In wkb.Worksheets         
    Debug.Print wks.CodeName
    If wks.CodeName = "Sheet2" Then
        Set wks = xl.Worksheets(wks.Name)
        wks.Select
        Exit For
    End If
Next wks

I decided to use the .codename property after reading similar threads like Gicu recommended. What I don't understand is I swear I had the .codename property working in my code before and I was verifying it under the debug.print property but now nothing registers anymore. I'm not sure if anyone has had similar problems or has had experience troubleshooting something like this?

[neuk]

Ok guys. I got it to work. What I did is that I created a new excel application and manually changed the code name to something I wanted instead of the default Sheet1, Sheet2, Sheet3 etc. - it looks like I couldn't read the codename without first changing it from the default codename. Not sure if that's the exact reason, or why...but that's what I found in my testing.

[Gicu]

Thanks for sharing!
Cheers,