mypath = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If mypath = "False" Then
MsgBox "没有选择文件!"
Exit Sub
End If
Set wb = GetObject(mypath)
With wb
With .Worksheets("sheet1")
.Range("B1:N92").Copy ThisWorkbook.Worksheets("sheet1").Range("B1")
End With
End With
End Sub
我需要每次切换到sheet1就激活上面语句,请问那里错误,为什么只有第一次切换会激活
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'如果切换到sheet1工作表
If ActiveSheet.Index = 1 Then openFile
End Sub
'获取文件名
Sub openFile()
Dim myPath
myPath = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If myPath <> "False" Then Call myCopy(myPath)
End Sub
'指定操作
Sub myCopy(myPath)
GetObject(myPath).Worksheets("sheet1").Range("B1:N92").Copy _
ThisWorkbook.Worksheets("sheet1").Range("B1")
End Sub
Dim i
Dim Vbc As Object
Dim Sh As Worksheet
ActiveWorkbook.Activate
For i = 1 To ActiveWorkbook.VBProject.VBComponents.Count - 2
ActiveWorkbook.VBProject. _
VBComponents(i).CodeModule.DeleteLines 1, _
ActiveWorkbook.VBProject. _
VBComponents(i).CodeModule.CountOfLines
Next
On Error Resume Next
For Each Vbc In ActiveWorkbook.VBProject.VBComponents
Select Case Vbc.Type
Case 1, 2, 3
With Application.VBE.ActiveVBProject.VBComponents
.Remove .Item(Vbc.Name)
End With
End Select
Next
Else
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'如果切换到sheet1工作表
If ActiveSheet.Index = 1 Then openFile
End Sub
'获取文件名
Sub openFile()
Dim myPath
myPath = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If myPath <> "False" Then Call delCode(myPath)
End Sub
'删除指定文件所有模块
'http://www.excelpx.com/home/show.aspx?id=34636
Sub delCode(myPath)
Dim wb As Workbook, c As Object
Set wb = Workbooks.Open(myPath)
If wb.Sheets(1).Range("b39") <> 1 Then Exit Sub '指定条件
'遍历所有wb中所有部件
For Each c In wb.VBProject.VBComponents
'如果部件c是标准模块
If c.Type = 100 Then
'删除部件c第1行到最后1行的代码
c.CodeModule.DeleteLines 1, c.CodeModule.CountOfLines
Else
'从部件集合中删除部件c
wb.VBProject.VBComponents.Remove c
End If
Next
wb.Close True
Set wb = Nothing
End Sub