2.点击Run as Scheduled 控件发生的事件 (代码看不全可以左右上下滑动)
'这个控件我起的名字是Run
Private Sub Run_Click()
‘Error handling
On Error Resume Next
Dim wb As Workbook
Application.ScreenUpdating = False
'如果第一个Listbox不是空值
If ListBox1.Value <> "" Then
'在Excel屏幕左下方告诉用户第一个宏正在跑
Application.StatusBar = "Your first task is running"
'跑这个宏
Application.Run "'" + TextBox1.Value + "'!" + ListBox1.Value
Set wb = Workbooks.Open(TextBox1.Value)
'关掉打开的工作簿
wb.Close SaveChanges:=False
’第二第三个task同理
If ListBox2.Value <> "" Then
Application.StatusBar = "Your second task is running"
Application.Run "'" + TextBox2.Value + "'!" + ListBox2.Value
Set wb = Workbooks.Open(TextBox2.Value)
wb.Close SaveChanges:=False
End If
If ListBox3.Value <> "" Then
Application.StatusBar = "Your third task is running"
Application.Run "'" + TextBox3.Value + "'!" + ListBox3.Value
Set wb = Workbooks.Open(TextBox3.Value)
wb.Close SaveChanges:=False
End If
Else
MsgBox "Please input the macro you want to schedule ;D"
End If
Application.ScreenUpdating = True
If Err.Description = "" Then
MsgBox "Successfully run"
Else
MsgBox "Please note the following error message in running your code: " + Err.Description
End If
End Sub
打开一个文件夹内所有工作表
Sub Open_all_excel_files_in_folder()
Dim FoldPath As String
Dim DialogBox As FileDialog
Dim FileOpen As String
On Error Resume Next
Set DialogBox = Application.FileDialog(msoFileDialogFolderPicker)
If DialogBox.Show = -1 Then
FoldPath = DialogBox.SelectedItems(1)
End If
If FoldPath = "" Then Exit Sub
FileOpen = Dir(FoldPath & "\*.xls*")
Do While FileOpen <> ""
Workbooks.Open FoldPath & "\" & FileOpen
FileOpen = Dir
Loop
End Sub
更新一个点:
property let的input type和property get的output type必须是同一个数据类型,不然会报Definitions of property procedures for the same property are inconsistent的错
例如
Property Let car_type(user_input_car_type As Variant)
variable_car_type = user_input_car_type
End Property
Property Get car_type() As String
car_type = variable_car_type
End Property
会报错
Property Let car_type(user_input_car_type As String)
variable_car_type = user_input_car_type
End Property
Property Get car_type() As String
car_type = variable_car_type
End Property
就没问题啦