Refer to another workbook's worksheet codename

[tiredofit]

Is it possible to refer to a worksheet's codename if it's in another workbook?

If the workbook I am trying to open contains a worksheet called "Data" and its codename is "wksData" then this works:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Workbooks.Open C:\MyLocation\MyFile.xlsm[/FONT]

Dim wb As Workbook
Set wb = Activeworkbook

Dim i As String

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]i = wb.Worksheets("Data").Range("A1").Value

but this fails:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Workbooks.Open C:\MyLocation\MyFile.xlsm​
[/FONT]

Dim wb As Workbook
Set wb = Activeworkbook

Dim i As String​
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
i = wb.wksData.Range("A1").Value 

​

How can I refer to the "other" workbook's worksheet using its codename?

Thanks


​

[/FONT]_{^{<strike>
</strike>}}[/FONT]

 

[dmt32]

Hi,

code names as far as I am aware, are not qualifiable which may explain why your code fails

have a read here:https://yoursumbuddy.com/using-worksheet-codenames-in-other-workbooks/

and see if solution offered will help you


Dave

 

[RoryA]

If you set a reference to the other workbook's project, then you can refer to the other sheet using projectname.codename syntax (you will need to have changed the project name from the default VBAProject in order to set a reference). Otherwise you'll need a looping function such as the one Dave linked to.

 

[tiredofit]

Thanks for the suggestions.