如何在Excel中重置工作表命名

If I open an Excel workbook, then manually delete "Sheet2" and "Sheet3", when I next click the 'insert worksheet' button it will name the new worksheet "Sheet4".

如果我打开Excel工作簿,然后手动删除“Sheet2”和“Sheet3”,当我下次单击“插入工作表”按钮时,它将命名新工作表“Sheet4”。

However, after deleting "Sheet2" and "Sheet3", if I save and re-open the workbook, when I next click the 'insert worksheet' button it will name the new worksheet "Sheet2".

但是,删除“Sheet2”和“Sheet3”后,如果我保存并重新打开工作簿,当我下次单击“插入工作表”按钮时,它将命名新工作表“Sheet2”。

So Excel is storing a 'highest sheet number' variable somewhere, and this is reset when the worksheet is closed. I want to reset this with VBA.

因此,Excel会在某处存储“最高工作表编号”变量,并在工作表关闭时重置。我想用VBA重置它。

Many thanks and best wishes.

非常感谢和祝福。

3 个解决方案

#1

Well you can try hacking it, I was checking out the memory and it looks like there are two integers that keep the worksheet count. But if you delete some sheets the count also decreases so you probably need to widen your search to find the one you're looking for

好吧,你可以尝试黑客攻击它,我正在检查内存,看起来有两个整数保持工作表计数。但是如果删除一些工作表,计数也会减少,因此您可能需要扩大搜索范围以找到您要查找的工作表

Once you find the right memory address, try zeroing it (it will probably be 4 bytes, but I would try to just zero the byte that matters).

一旦找到正确的内存地址,尝试将其归零(它可能是4个字节,但我会尝试将重要的字节归零)。

Here's the function I used to look through the memory (it gets the first 100 bytes after the memory address for worksheets.count, then adds a sheet, and gets the 100 bytes again)

这是我用来查看内存的函数(它获取了worksheets.count的内存地址之后的前100个字节,然后添加了一个工作表,并再次获取100个字节)

Option Explicit

Private Declare Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
Sub test2()
    'void ZeroMemory(
    '  [in]  PVOID Destination,
    '  [in]  SIZE_T Length
    ');

    'void CopyMemory(
    '  _In_  PVOID Destination,
    '  _In_  const VOID *Source,
    '  _In_  SIZE_T Length
    ');

    Dim b(0 To 99) As Byte
    Call CopyMemory(b(0), VarPtr(ActiveWorkbook.Worksheets.Count), 100)

    Dim output
    Dim i
    For Each i In b
        output = output & " " & Hex(i)
    Next i

    ActiveWorkbook.Worksheets.Add
    output = output & vbNewLine
    output = output & vbNewLine
    DoEvents

    Call CopyMemory(b(0), VarPtr(ActiveWorkbook.Worksheets.Count), 100)
    For Each i In b
        output = output & " " & Hex(i)
    Next i

    MsgBox output
End Sub

#2

No, there is no way to do this "naturally", without saving, closing and reopening the workbook. The sheets.count property is indeed read-only.

不,没有保存,关闭和重新打开工作簿,没有办法“自然地”执行此操作。 sheets.count属性确实是只读的。

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.sheets.count(v=office.15).aspx

But you could change names with vba, as you apparently know. I do agree that it is not really satisfying.

但你可以用vba改变名字,正如你显然知道的那样。我确实同意这并不令人满意。

Complement/correction : In excel a sheet object has two different "name" properties : sheet.Name and sheet.CodeName. The Name property is read/write (i.e. can be read, fine, but changed also) and contains the name that you see, that appears on the sheet tab in excel. The CodeName property is read-only... See :

补充/更正:在Excel中,工作表对象具有两个不同的“名称”属性:sheet.Name和sheet.CodeName。 Name属性是读/写(即可以读取,正常,但也可以更改)并包含您看到的名称,该名称显示在excel的工作表选项卡上。 CodeName属性是只读的...请参阅:

http://msdn.microsoft.com/en-us/library/office/ff837552(v=office.15).aspx

#3

This will change the sheet numbers and leave the names in tact

这将更改工作表编号并保留名称

    Sub BatchChange_WSRefName()
' THIS IS MAINTENANCE CODE ONLY RUN MANUALLY
' Changes the Reference Names for all Worksheets
' in the active Workbook to Sheet + incrementing integer
Dim i As Integer, ws As Worksheet
i = 0
' Change to Temp first to prevent Naming errors
For Each ws In ActiveWorkbook.Worksheets
    i = i + 1
    On Error Resume Next
    ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName") = _
        "Temp" & i
    On Error GoTo 0
Next ws
' Change to Sheet + incrementing integer
i = 0
For Each ws In ActiveWorkbook.Worksheets
    i = i + 1
    On Error Resume Next
    ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName") = _
        "Sheet" & i
    On Error GoTo 0
Next ws
Set ws = Nothing
End Sub

#1