每天自动运行宏，而无需打开任何工作簿 [英] Automatically run macro daily without opening any workbooks

问题描述

我对VBA还是比较陌生，对 Application.OnTime 方法不太有经验.我一直在研究多种资源，但我看不到一种清晰的方法来每天自动运行一个宏而无需打开任何工作簿(假设我先在工作簿中运行该宏).

I am still relatively new to VBA and not too experienced with the Application.OnTime method. I have been looking at multiple resources, and I cannot see a clear way to automatically run a macro daily without opening any workbooks (assuming I run the macro first in my workbook).

这可能吗?我想提高效率，而不是每天早上运行多个宏.

Is this possible? I would like to be more efficient instead of running multiple macros every morning.

如果您需要更多信息，请告诉我，但是我的代码只是一个复制/粘贴过程:

Let me know if you need more info but my code would simply be a copy/paste process:

Sub MyMacro()
Application.ScreenUpdating = False

Dim OH As Workbook
Dim PO As Workbook


Set OH = Workbooks.Open("filepath")
Set PO = Workbooks.Open("filepath2")

'clear sheet
ThisWorkbook.Sheets("OH").Range("A2:O10000").ClearContents
'clear other sheet
ThisWorkbook.Sheets("OP").Range("A2:AG10000").ClearContents


'Paste new data
OH.Sheets("OH").Range("B3:P10000").Copy 
Destination:=ThisWorkbook.Sheets("OH").Range("A2")
PO.Sheets("OP").Range("A3:AG20000").Copy 
Destination:=ThisWorkbook.Sheets("OP").Range("A2")


OH.Close savechanges:=False
PO.Close savechanges:=False


'Refresh all pivot tables
Dim PT As PivotTable
Dim WST As Worksheet
    For Each WST In ThisWorkbook.Worksheets
    For Each PT In WST.PivotTables
        PT.RefreshTable
    Next PT
Next WST


'Clear last sheet
ThisWorkbook.Sheets("Pivot1 paste").Range("A6:E10000").ClearContents

ThisWorkbook.Sheets("Pivot1").Range("A6:D10000").Copy 
Destination:=ThisWorkbook.Sheets("Pivot1 paste").Range("A6")

'Paste variable column to last sheet
Dim cell As Range
For Each cell In ThisWorkbook.Sheets("Pivot1").Range("E3:AZ6")
    If cell.Value = "Out" Then cell.EntireColumn.Copy 
Destination:=ThisWorkbook.Sheets("Pivot1 paste").Columns(5)
Next

'Save with current date and close
ThisWorkbook.SaveAs ("TargetFilepath") 
& ".xlsm")
ThisWorkbook.Close


Application.ScreenUpdating = True
End Sub

推荐答案

VBA可以在Microsoft Office中运行，但是您可以利用VB脚本来打开工作簿并运行宏.

VBA is made to work within Microsoft Office, but you can leverage VB Script to open a workbook and run a macro.

将以下内容放在 .vbs 文件中.您将创建一个时间表来调用和执行此文件.确保设置正确的路径，然后将"test.xlsm！mymacro" 修改为工作簿的名称和要调用的宏.

Place the following in a .vbs file. You will create a schedule to call and execute this file. Make sure to set the correct path and edit "test.xlsm!mymacro" to the name of your workbook and the macro you wish to call.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\path\to\test.xlsm")

objExcel.Application.Run "test.xlsm!mymacro"
objExcel.ActiveWorkbook.Close

objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit

为了对此进行测试，我在 test.xlsm 中创建了一个快速宏，以在同一目录中创建文本文件，并在运行VB脚本后验证该文件是否在该目录中.下面的宏没有什么特别的，您可以调用所需的任何宏.这将使您不必手动打开工作簿并运行宏.

To test this, I created a quick macro in test.xlsm to create a text file in the same directory and verified that it was there after I ran the VB Script. There is nothing special about the macro below, you can call whatever macro you desire. This will save you from having to manually open the workbook and run the macro.

Sub mymacro()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim oFile As Object
    Set oFile = fso.CreateTextFile("C:\Users\bcohan\Downloads\testing.txt")

    oFile.WriteLine "test"
    oFile.Close

    Set fso = Nothing
    Set oFile = Nothing
End Sub

完成上述工作后，您应该能够在Windows中创建计划任务以运行脚本.

Once you have the above working, you should be able to create a scheduled task in windows to run your script.

这篇关于每天自动运行宏，而无需打开任何工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！