第1个回答 2012-10-06
'打开文件在菜栏有一个"表格汇总"按扭
Sub auto_open()
MenuBars(xlWorksheet).Reset
Set mycommandbar = CommandBars("standard")
Set mybutton = mycommandbar.Controls.Add(Type:=msoControlButton)
With mybutton
.Style = msoButtonCaption
.Caption = "表格汇总"
.Enabled = True
.OnAction = "ABCD"
End With
End Sub
Sub auto_close()
Set mycommandbar = CommandBars("standard")
For Each mybutton In mycommandbar.Controls
If mybutton.Caption = "表格汇总" Then mybutton.Delete
Next
End Sub
Sub ABCD()
Dim lj As String
Dim dirname As String
Dim nm As String
Sheets(1).Select
lj = ActiveWorkbook.Path
nm = ActiveWorkbook.Name
dirname = Dir(lj & "\*.*")
Range("A2:AD200").Select
Selection.Delete Shift:=xlToLeft
Do While dirname <> ""
If dirname <> nm Then
Workbooks.Open Filename:=lj & "\" & dirname
Workbooks(nm).Activate
Workbooks(dirname).Sheets(1).Range("Q5:AD5").Copy _
Sheets(1).Range("a65536").End(xlUp).Offset(2, 0)'自己的表格修改
Workbooks(dirname).Close False
End If
dirname = Dir
Loop
Cells(20, 1).Select
End Sub