自动执行Excel的VBA代码


应用场景

我们写好一段VBA代码,比如批量处理excel文件,我们可以手动执行这段代码,但是有时候我们希望让他定时执行,那么该怎么处理呢?

准备

  • 在Excel中将我们的VBA代码写好,我这里的方法名为main。
  • 新建一个VBS文件,可以新建txt文件,然后另存为.vbs格式
  • 将如下代码写入vbs文件中
sPath = "C:\Users\Tango\Desktop\vba_tool\tool_Ver1.0.xlsm"

sRunTime1 = "7:00"
aTime = Split(sRunTime1,":")
iHour = CInt(aTime(0))
iMinute = CInt(aTime(1))

sRunTime1 = "10:00"
aTime2 = Split(sRunTime1,":")
iHour2 = CInt(aTime2(0))
iMinute2 = CInt(aTime2(1))

sRunTime1 = "12:00"
aTime3 = Split(sRunTime1,":")
iHour3 = CInt(aTime3(0))
iMinute3 = CInt(aTime3(1))

sRunTime1 = "14:00"
aTime4 = Split(sRunTime1,":")
iHour4 = CInt(aTime4(0))
iMinute4 = CInt(aTime4(1))

sRunTime1 = "16:00"
aTime5 = Split(sRunTime1,":")
iHour5 = CInt(aTime5(0))
iMinute5 = CInt(aTime5(1))

sRunTime1 = "18:00"
aTime6 = Split(sRunTime1,":")
iHour6 = CInt(aTime6(0))
iMinute6 = CInt(aTime6(1))

Do While True
 If (Hour(Now)=iHour) And (Minute(Now)=iMinute) Then
  Set oXlsApp = CreateObject("Excel.Application")
  oXlsApp.Application.Visible = False
  oXlsApp.Application.DisplayAlerts = False
  oXlsApp.Application.Workbooks.Open sPath
  oXlsApp.Run "main" 
  oXlsApp.Quit : Set oApp = Nothing
 ElseIf (Hour(Now)=iHour2) And (Minute(Now)=iMinute2) Then
  Set oXlsApp = CreateObject("Excel.Application")
  oXlsApp.Application.Visible = False
  oXlsApp.Application.DisplayAlerts = False
  oXlsApp.Application.Workbooks.Open sPath
  oXlsApp.Run "main" 
  oXlsApp.Quit : Set oApp = Nothing
 ElseIf (Hour(Now)=iHour3) And (Minute(Now)=iMinute3) Then
  Set oXlsApp = CreateObject("Excel.Application")
  oXlsApp.Application.Visible = False
  oXlsApp.Application.DisplayAlerts = False
  oXlsApp.Application.Workbooks.Open sPath
  oXlsApp.Run "main" 
  oXlsApp.Quit : Set oApp = Nothing
 ElseIf (Hour(Now)=iHour4) And (Minute(Now)=iMinute4) Then
  Set oXlsApp = CreateObject("Excel.Application")
  oXlsApp.Application.Visible = False
  oXlsApp.Application.DisplayAlerts = False
  oXlsApp.Application.Workbooks.Open sPath
  oXlsApp.Run "main" 
  oXlsApp.Quit : Set oApp = Nothing
 ElseIf (Hour(Now)=iHour5) And (Minute(Now)=iMinute5) Then
  Set oXlsApp = CreateObject("Excel.Application")
  oXlsApp.Application.Visible = False
  oXlsApp.Application.DisplayAlerts = False
  oXlsApp.Application.Workbooks.Open sPath
  oXlsApp.Run "main" 
  oXlsApp.Quit : Set oApp = Nothing
 ElseIf (Hour(Now)=iHour6) And (Minute(Now)=iMinute6) Then
  Set oXlsApp = CreateObject("Excel.Application")
  oXlsApp.Application.Visible = False
  oXlsApp.Application.DisplayAlerts = False
  oXlsApp.Application.Workbooks.Open sPath
  oXlsApp.Run "main" 
  oXlsApp.Quit : Set oApp = Nothing
 Else
  WSH.Sleep 1000
 End If
Loop

这段代码没有优化,有很多重复内容。 实现功能为,在,7,10,12,14,16时间点,自动运行main函数。

  1. Set oXlsApp = CreateObject("Excel.Application") 这段代码,表示创建一个Excel应用。
  2. oXlsApp.Application.Workbooks.Open sPath 打开指定excel文件,这个文件中有我们写好的vba代码。
  3. oXlsApp.Run "main" 执行main函数