行业新闻
vba读取excel文件内容(让vba引用单元格里的值方法)
2022-02-24 13:46  浏览:0

如果想要提取Excel界面的某个图标,可以使用VBA的CommandBars对象将其提取出来:

Application.CommandBars.Controls.intFaceId

Application.CommandBars.Controls.CopyFace

intFaceId从1-18028

Option Explicit

Sub 获取Excel中的所有内置控件的FaceId和图像()
    Dim cbr As CommandBar, ctl As CommandBarButton
    Dim intCol As Integer, intRow As Integer
    Dim intFaceId As Integer
    If Application.WorksheetFunction.CountA(Cells) <> 0 Then
        MsgBox "活动工作表中包含数据,请选择一个空工作表!"
        Exit Sub
    End If
    On Error GoTo errTrap '错误捕捉
    Application.ScreenUpdating = False
    Set cbr = Application.CommandBars.Add(MenuBar:=False, Temporary:=True)
    Set ctl = cbr.Controls.Add(Type:=msoControlButton, Temporary:=True)
    intRow = 1
    Do
        For intCol = 1 To 8
            intFaceId = intFaceId + 1
            Application.StatusBar = "FaceID=" & intFaceId  ' 状态栏提示一下
            ctl.FaceId = intFaceId
            ctl.CopyFace '复制图标图像,如果复制的是不可见图像,会出现编号为1004的错误
            ActiveSheet.Paste Cells(intRow, intCol + 1) '粘贴图标图像
            Cells(intRow, intCol).Value = intFaceId
        Next intCol
        intRow = intRow + 1
    Loop
errTrap: '错误处理
    If Err.Number = 1004 Then Resume Next
    Application.StatusBar = False
    cbr.Delete
    Application.ScreenUpdating = True
End Sub

效果:

最后一页:

-End-