CodeName value not available on imported sheets

[StaffsLebowski]

Hi,

1: I have an Access DB that I use to copy Worksheets X, Y, Z from WorkBook 1 to WorkBook 2 (WorkBook 2 already has Sheets (A, B & C).

objWBSource.Worksheets("MySheetName").Copy Before:=objWBDest.Sheets(1)​

2: I save and close both WorkBooks.

3: WorkBook 2 then has sheets A, B & C and the imported X, Y and Z sheets

4: On the 'Workbook_SheetBeforeDoubleClick' event, I have a simple message box:

Msgbox Sh.Name
Msgbox Sh.CodeName

For Sheets A, B & C, when I double click a cell, I'm able to get both the Sheet.Name and the Sheet.CodeName but, for Sheets X, Y & Z, I'm only able to get the Sheet.Name

It's as if the 'sheets collection' has not been updated with the CodeName when the X, Y & Z sheets were imported. However, if I put a break-point on the first message box, and double click a cell on Sheets X, Y & Z then the CodeName is 'refreshed' and I'm able to return a value. It's as if, breaking into the code has forced Excel to refresh it's 'sheets collection'.

Does anyone know why this happens and more usefully, how to make sure the 'sheets collection' is up to date after importing new sheets?

Many thanks for your help,

Staffs Lebowski

 

[Yongle]

Which version of Office are you using?

If the macro is currently in Access then try putting the macro inside an Excel workbook instead and create an Access macro to open the workbook and run the Excel macro
- maybe that will make Excel refresh sheet CodeNames ray:

 

[Yongle]

Something else for you to try
- force CodeName of last sheet to be changed and immediately changed back when you reopen the workbook
- maybe that will make Excel refresh all sheet CodeNames ray:

Private Sub Workbook_Open()
    With ThisWorkbook.VBProject.VBComponents(ThisWorkbook.Sheets(Sheets.Count).CodeName)
        .Name = .Name & "XXXXX"
        .Name = Replace(.Name, "XXXXX", "")
    End With
End Sub

 

[StaffsLebowski]

Hi Yongle,

Thank you for your reply.

1: It is not possible in the current setup to remove the copy operation from Access. However, I appreciate this may be a possible solution.

2: Your second example I will try, thanks for the suggestion.

3: I spent hours on the net and discovered this which for some reason fixed the issue:

Set tablesheet = objWBDest.Worksheets.Add(After:=objWBDest.Worksheets(objWBDest.Worksheets.count))
objWBDest.Sheets(tablesheet.Name).Delete

I'm guessing that, adding the new sheet forced Excel to re-populate things?

Thank you Yongle, your input was much appreciated,

Regards,

StaffsLebowski..

 

[Yongle]

thanks for posting your solution