Refer to excel sheet's codename from access

[SteveApa]

I am trying to refer to an excel file's sheet code name in vba from access... does not let me. I can open the workbook that has a sheet named "Sheet1" and "Sheet1" code name is sheet1. This is what I have..

GlobalVariables Module:
Public GLOBAL_XL As Object
Public GLOBAL_WB As Object
Public GLOBAL_WS As Object

PublicFunctions Module:
Public Sub Open_Excel_Global()


    If GLOBAL_XL Is Nothing Then
        Set GLOBAL_XL = CreateObject("excel.Application")
    End If
    
End Sub


Form report module:
Private Sub CostHistoryElementHistoryExport_Click()


    Open_Excel_Global ' this sets GLOBAL_XL   excel opens fine
    Set GLOBAL_WB = GLOBAL_XL.Workbooks.Open(COST_ELEMENT_REPORT_PATH_AND_FILENAME) ' file open's fine
    Set GLOBAL_WS = GLOBAL_XL.Sheet1 ' This is where the error is. 
    BLA
    BLA
   BLA
End Sub

If I refer to GLOBAL_XL.ACTIVESHEET.codename in the watch window, I the correct name. If I use GLOBAL_WB.SHEETS("SHEET1").name in the watch window, I get the correct name, but I cannot refer to the sheet as GLOBAL-XL.sheet1.name I get error.



Thanks.
Steve

[CJ_London]

no time to check but think this

=GLOBAL_XL.Sheet1

should be

=GLOBAL_XL.Worksheets("Sheet1")

[SteveApa]

That is my point! I don't want to use the sheet name, I want to use code name. So, Worksheets("Sheet1").activate = sheet1.activate in excel by the use of code name - and code name never changes!!!! So, one would think that GLOBAL_XL.Worksheets("Sheet1").activate = GLOBAL_XL.sheet1.activate BUT IT DOES NOT!

[SteveApa]

    Set GLOBAL_WS = GLOBAL_XL.worksheets("sheet1") ' works
    Set GLOBAL_WS = GLOBAL_XL.Sheet1 ' does not work

[CJ_London]

so what's your point? or is this solved now?

So, one would think that GLOBAL_XL.Worksheets("Sheet1").activate = GLOBAL_XL.sheet1.activate BUT IT DOES NOT!

for the record, I don't think that - worksheets is a collection within a workbook. Just as in access, forms are in a collection - and you refer to them as currentdb.forms("myForm") or in a short form forms("myForm"), forms.myform or currentdb.myform would not work.

you could try as an experiment

Worksheets("Sheet1")

[Micron]

GLOBAL_XL.Sheet1 seems incorrect to me. Sheet1 is not a member of the application object. Nor is worksheets a member of the application object
application > workbook > worksheets > name or index #
Set GLOBAL_WS = GLOBAL_XB.Sheet1 maybe...

However, I still think that's an incorrect reference to the specific sheet.
If it's always #1, you could use the index number (can't recall if that is 0 or 1), or WS = WB.Worksheets ("the name")
I was thinking at first the problem was trying to use the name property instead of the caption property, but for now I'm going with what seems to be the fact that your object hierarchy is incorrect.

[SteveApa]

Here is the answer. In excel you can refer to codename, sheet1.activate (works great) which is the same as worksheets("Sheet1").activate (sheet name not codename used). now, code name only works on the active workbook, not on a referenced workbook. So, for this case, since I am referencing excel from acces, I cannot use codename!!

Codename cannot be used to directly access a sheet from access.

Thanks for responding1

[Micron]

Interesting. Before this, I never knew of that property. Not sure why you'd want to use it - maybe in case a sheet is added before the referenced sheet when the index is used, or in case the sheet name is unknown or gets changed. It's possible to change the codename, but I suppose that's far less likely in most cases.

I will have to test your answer out of curiosity because the M$ pages I read don't say anything about it having to be activated, but mostly because I'm curious about this property.
Thanks for posting the answer. If you're happy with the outcome, maybe mark this thread as solved?

[Micron]

so I tested it, and I can reference the codename property from Access without activating the worksheet. The sheet name is "Sheet1". The message box for the codename is Sheet1111. I still say your problem is the hierarchy of your object referencing. This works because worksheets (thus .worksheets("sheet1") is a member of the application object GLOBAL_XL

Set GLOBAL_WS = GLOBAL_XL.worksheets("sheet1") ' works

This does not work because Sheet1 is a member of worksheets, not the application object GLOBAL_XL

Set GLOBAL_WS = GLOBAL_XL.Sheet1 ' does not work

It matters not if you attempt to use the index, sheet name, or sheet codename if you skip over a member of a hierarchy. Think of it as a chain. If you remove a link, the chain is broken.

EDIT

for the record, I don't think that - worksheets is a collection within a workbook

Sorry to disagree, but had to look it up in order to quote it.

The Worksheet object is a member of the Worksheets collection. The Worksheets collection contains all the Worksheet objects in a workbook.