如何从批处理文件中运行VBA代码？

I have an XLSX file that I modify to copy some data from one cell to another using the following VBA code.

我有一个XLSX文件,我修改了使用以下VBA代码将一些数据从一个单元格复制到另一个单元格。

I then convert this xlsx file to csv format. (I'm okay in doing this)

然后我将此xlsx文件转换为csv格式。 (我可以这样做)

Since this xlsx file is replaced daily with a new file I need to find a way to run my VBA code from a batch file that I can schedule.

由于每天用新文件替换这个xlsx文件,我需要找到一种从我可以安排的批处理文件中运行我的VBA代码的方法。

My code

Option Explicit

Sub copy_Cell_A4()

    Dim RowLocation As Long 'can hold over 32000 if over this many rows
    Dim ws As Worksheet

    Application.ScreenUpdating = False

    For Each ws In Worksheets
        RowLocation = ws.Range("A" & Rows.Count).End(xlUp).Row
        ws.Range("A4").Copy ws.Range("J6:J" & RowLocation)
        ws.Range("A2").Copy ws.Range("K6:K" & RowLocation)
        ws.Range("E2").Copy ws.Range("L6:L" & RowLocation)
    Next ws

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.ThisWorkbook.Close SaveChanges:=True

End Sub

2 个解决方案

#1

You can modify your code a bit and store the same in a .vbs script which will be a standalone script file so can be used independently.

您可以稍微修改一下代码并将其存储在.vbs脚本中,该脚本将是一个独立的脚本文件,因此可以单独使用。

Reference your excel file like this:

像这样引用你的excel文件:

Option Explicit
Dim xlApp, xlBook
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Users\user1\Desktop\samplefile.xlsx, 0, True)

Hope this will help.

希望这会有所帮助。

#2

You could use a VBScript to silently run the MACRO in your workbook.

您可以使用VBScript在工作簿中静默运行MACRO。

In the code below, just replace the following parameters:

在下面的代码中,只需替换以下参数:

"C:\YourFolderPath\" - with your folder path, where you keep your Excel file with the code in it.

“C:\ YourFolderPath \” - 使用您的文件夹路径,您可以在其中保存包含代码的Excel文件。
"SO_VBScript.xlsm" - modify to your Excel file where you keep your "copy_Cell_A4" Sub code.

“SO_VBScript.xlsm” - 修改到您保存“copy_Cell_A4”子代码的Excel文件。
"copy_Cell_A4" - this is the name of the Sub you have in your post.

“copy_Cell_A4” - 这是您帖子中Sub的名称。

VBScript Code

Option Explicit

Dim xlApp
Dim xlBook
Set xlApp = CreateObject("Excel.Application")

Set xlBook = xlApp.Workbooks.Open("C:\YourFolderPath\SO_VBScript.xlsm", 0, True)
xlApp.Run "copy_Cell_A4"
xlApp.DisplayAlerts = False
xlBook.Save
xlBook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing

WScript.Echo "Finished"
WScript.Quit

#1