如何使用PowerShell将VBA代码添加到Excel工作表中? [英] How to add VBA code to Excel worksheet using PowerShell?

问题描述

我需要在工作表(1)中包含一个Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean).

I need to include a Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) in my Sheet(1).

我能够打开和写入单元格.

I'm able to open and write in cells.

$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Add()
$worksheet = $workbook.WorkSheets.item(1)
$worksheet.range("c1","g6").value = "str"
...
$workbook.SaveAs($xlFlie, 50)
$Excel.Application.Quit()

如何将VBA代码放在工作表中(而不是在VBA模块中)?

How do I put the VBA code in the sheet (rather than in a VBA module)?

我尝试过:

$xlmodule = $workbook.VBProject.VBComponents.Add()
$xlmodule.CodeModule.AddFromString($code)

我收到此错误:

无法在表达式Null中调用方法.
Aucaractère.\ Build-ADGrpsMembers2Excel.ps1:273:5
+ $ xlmodule = $ workbook.VBProject.VBComponents.Add(1)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~
+ CategoryInfo:InvalidOperation:(:)[]，RuntimeException
+ FullyQualifiedErrorId:InvokeMethodOnNull

Can not call a method in an expression Null.
Au caractère .\Build-ADGrpsMembers2Excel.ps1:273 : 5
+ $xlmodule = $workbook.VBProject.VBComponents.Add(1)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation : (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

推荐答案

我需要将VBA选项更改为

I need to change VBA option to

$excel = New-Object -ComObject Excel.Application
New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\$($excel.Version)\excel\Security" -Name AccessVBOM -Value 1 -Force | Out-Null
New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\$($excel.Version)\excel\Security" -Name VBAWarnings -Value 1 -Force | Out-Null

我的工作代码是:

$excel = New-Object -ComObject Excel.Application
New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\$($excel.Version)\excel\Security" -Name AccessVBOM -Value 1 -Force | Out-Null
New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\$($excel.Version)\excel\Security" -Name VBAWarnings -Value 1 -Force | Out-Null

$workbook = $excel.Workbooks.Add(1)
$worksheet=$workbook.WorkSheets.item(1)

$excel.Visible=$true
$excel.DisplayAlerts = $true
$excel.ScreenUpdating = $true

#$worksheet.range("c1","f6").ColumnWidth = 4
#$worksheet.range("c1","f6").Orientation = 90

$xlmodule = $workbook.VBProject.VBComponents.item('feuil1')
$code = @"
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
End Sub
"@

$xlmodule.CodeModule.AddFromString($code)

$saveName = "$([Environment]::GetFolderPath('desktop'))\Export-Excel ($( ((Get-Date -Format u ) -replace ":", ".") -replace "Z", '' ) ).xlsb"

# savegarde du fichier
$workbook.SaveAs($saveName, 50)

Write-Verbose "Closing $($WorkSheetName)"
$Excel.Workbooks.Close()
Write-Verbose "Exit Excel"
$Excel.Application.Quit

这篇关于如何使用PowerShell将VBA代码添加到Excel工作表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！