EXCEL表格中建立目录

#IfWinActive,ahk_exe EXCEL.EXE

F11::

oExcel :=Excel_Get()        ;创建对象

;创建gui

Gui,Destroy

Gui Add, ListView, x1 y2 w209 h129 +Grid +NoSortHdr, <样式>

Gui Add, Text, x5 y135 w200 h23 +0x200, 注：[目录] 为 目的单元格

Gui Add, CheckBox, x5 y160 w162 h23 vaddrow Checked , 是否新增行 > 返回首页

Gui Add, Button, x12 y185 w80 h23 g提示框取消, 取消

Gui Add, Button, x124 y185 w80 h23 g提示框确认, 确认

LV_Add(,"          [目录]")

LV_Add(,"        <此为空行>")

LV_Add(,"        第1张表")

LV_Add(,"        第2张表")

LV_Add(,"        第3张表")

Gui, +AlwaysOnTop +ToolWindow          ;-Caption

;Gui Color, 0x4DA6FF

Gui Show, w212 h210, 提示框

return

提示框取消:

oexcel :=""

  Gui,Destroy

return

提示框确认:

  Gui,Submit

  Gui,Destroy

  offset:=oExcel.ActiveCell.row()

  addr:=oExcel.ActiveCell.address(,0) ;column() 获取列名字

  StringSplit,addr,addr,$

  oExcel.ActiveCell.value:="[目录]"

  for Sheet in oExcel.Sheets

  {

    If (Sheet.name=oExcel.ActiveSheet.Name)

        continue

    oExcel.ActiveSheet.Hyperlinks.Add(oExcel.ActiveCell.Offset(A_Index,0),"","'"Sheet.name "'!A1","单击到 " Sheet.name,Sheet.name)

    if !addrow      ;是否勾选需要增加行，来添加  '返回首页'

    Sheet.Hyperlinks.Add(oExcel.Sheets(A_index).cells(1,1),"","'"oExcel.ActiveSheet.Name "'!" addr1 A_Index+offset,"单击到 " oExcel.ActiveSheet.Name,"返回首页") ;这里的“首页!B”要和你所命名的一致。

    else

    oExcel.Sheets(A_index).Rows("1:1").EntireRow.Insert Sheet.Hyperlinks.Add(oExcel.Sheets(A_index).cells(1,1),"","'"oExcel.ActiveSheet.Name "'!" addr1 A_Index+offset,"单击到 "  oExcel.ActiveSheet.Name,"返回首页")        ;单独新增一行  返回首页，若不新增用上一行代码

  }

oExcel :=""

return

; Excel_Get by jethrow (modified)

; Forum:    https://autohotkey.com/boards/viewtopic.php?f=6&t=31840

; Github:  https://github.com/ahkon/MS-Office-COM-Basics/blob/master/Examples/Excel/Excel_Get.ahk

Excel_Get(WinTitle:="ahk_class XLMAIN", Excel7#:=1) {

    static h := DllCall("LoadLibrary", "Str", "oleacc", "Ptr")

    WinGetClass, WinClass, %WinTitle%

    if !(WinClass == "XLMAIN")

        return "Window class mismatch."

    ControlGet, hwnd, hwnd,, Excel7%Excel7#%, %WinTitle%

    if (ErrorLevel)

        return "Error accessing the control hWnd."

    VarSetCapacity(IID_IDispatch, 16)

    NumPut(0x46000000000000C0, NumPut(0x0000000000020400, IID_IDispatch, "Int64"), "Int64")

    if DllCall("oleacc\AccessibleObjectFromWindow", "Ptr", hWnd, "UInt", -16, "Ptr", &IID_IDispatch, "Ptr*", pacc) != 0

        return "Error calling AccessibleObjectFromWindow."

    window := ComObject(9, pacc, 1)

    if ComObjType(window) != 9

        return "Error wrapping the window object."

    Loop

        try return window.Application

        catch e

            if SubStr(e.message, 1, 10) = "0x80010001"

                ControlSend, Excel7%Excel7#%, {Esc}, %WinTitle%

            else

                return "Error accessing the application object."

}