VBScript打开Excel，然后添加VBA宏

Question

我需要一个VBScript来打开某个Excel文档，然后在打开时必须添加一个宏并保存。

我可以打开Excel文档，但我不知道如何打开宏屏幕(Alt+F11(，然后添加代码并保存。。。

有办法这样做吗？

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:test.xls")
objExcel.Application.DisplayAlerts = False
objExcel.Application.Visible = True`
'Macro Script
Sub HideRows()
    Dim cell As Range   
    For Each cell In Range("H1:W200")
        If Not isEmpty(cell) Then
            If cell.Value <> "" And cell.Value = 0 Then 
                cell.EntireRow.Hidden = True
                Columns("H").EntireColumn.Hidden = True
                Columns("I").EntireColumn.Hidden = True
                Columns("J").EntireColumn.Hidden = True
                Columns("M").EntireColumn.Hidden = True
                Columns("N").EntireColumn.Hidden = True
                Columns("O").EntireColumn.Hidden = True
                Columns("P").EntireColumn.Hidden = True
                Columns("Q").EntireColumn.Hidden = True
                Columns("S").EntireColumn.Hidden = True
                Columns("T").EntireColumn.Hidden = True
                Columns("V").EntireColumn.Hidden = True
            End If
        End If
    Next
End Sub

Answer 1

按照以下步骤操作：

1. 在Excel中打开VBA编辑器，然后添加一个新模块
2. 将宏代码粘贴到其中
3. 右键单击模块并选择Export...
4. 给它一个文件名并保存在某个地方

5. 在VBScript中，添加以下代码行：

   objWorkbook.VBProject.VBComponents.Import "/path/to/your/module.bas"
   objWorkbook.Save
   

   请注意，在Excel2007+中，不能将宏保存在xlsx文件中。您需要使用SaveAs，并为文件提供xslm扩展名。或者，您可以使用旧的xls格式(这是您在示例中使用的格式(。

Answer 2

您可以使用VBProject对象的VBComponents对象以编程方式添加代码。所以在代码的最后一行后面加上这个：

Set objModule = objworkbook.VBProject.VBComponents.Add(1)       ' 1 = vbext_ct_StdModule
objExcel.Visible = True    ' not necessary if you close Excel anyway
theSource = ""
theSource = theSource & "Sub HideRows()" & vbCrLf
theSource = theSource & "    Dim cell As Range   " & vbCrLf
theSource = theSource & "    For Each cell In Range(""H1:W200"")" & vbCrLf
theSource = theSource & "        If Not isEmpty(cell) Then" & vbCrLf
theSource = theSource & "            If cell.Value <> """" And cell.Value = 0 Then " & vbCrLf
theSource = theSource & "                cell.EntireRow.Hidden = True" & vbCrLf
theSource = theSource & "                Columns(""H"").EntireColumn.Hidden = True" & vbCrLf
theSource = theSource & "                Columns(""I"").EntireColumn.Hidden = True" & vbCrLf
theSource = theSource & "                Columns(""J"").EntireColumn.Hidden = True" & vbCrLf
theSource = theSource & "                Columns(""M"").EntireColumn.Hidden = True" & vbCrLf
theSource = theSource & "                Columns(""N"").EntireColumn.Hidden = True" & vbCrLf
theSource = theSource & "                Columns(""O"").EntireColumn.Hidden = True" & vbCrLf
theSource = theSource & "                Columns(""P"").EntireColumn.Hidden = True" & vbCrLf
theSource = theSource & "                Columns(""Q"").EntireColumn.Hidden = True" & vbCrLf
theSource = theSource & "                Columns(""S"").EntireColumn.Hidden = True" & vbCrLf
theSource = theSource & "                Columns(""T"").EntireColumn.Hidden = True" & vbCrLf
theSource = theSource & "                Columns(""V"").EntireColumn.Hidden = True" & vbCrLf
theSource = theSource & "            End If" & vbCrLf
theSource = theSource & "        End If" & vbCrLf
theSource = theSource & "    Next" & vbCrLf
theSource = theSource & "End Sub" & vbCrLf
objModule.CodeModule.AddFromString theSource
'objExcel.Quit
'Set objExcel = Nothing

Answer 3

这不是直接的，但我要做的是使用SendKeys函数来模拟Alt+F11。

Application.SendKeys "%{F11}", True

然后使用相同的逻辑，使用按键导航到正确的窗口，添加模块，然后使用将宏代码粘贴到正确的位置

Application.SendKeys ""^V"
Application.SendKeys ""^V", True   'Incase that one above does not work

然后你可以保存使用：

Application.SendKeys ""^S", True

你可以在这里和这里阅读更多关于

但另一种方法是使用鼠标和键盘宏记录器(可以编程模拟动作的独立应用程序(。我个人使用KeyText做这类事情已经超过10年了。