如何在PowerShell中的excel.Worksheet中添加VBA代码？ [英] How to add VBA code in excel.Worksheet in powershell?

问题描述

我需要在我的工作表（1）中包含一个 Private Sub ** Worksheet_BeforeDoubleClick **（ByVal Target As Range，Cancel As Boolean）

我能够正确地打开和写入单元格，但是我不知道如何将VBA代码放在工作表中（而不是在VBA模块中）。

  $ excel =新对象-ComObject Excel.Application 
 $ workbook = $ excel.Workbooks.Add（）
 $工作表= $ workbook.WorkSheets.item（1）
 $ worksheet.range（c1，g6）。value =str
 ... 
 $ workbook.SaveAs $ xlFlie，50）
 $ Excel.Application.Quit（）
  

我已经尝试过：

  $ xlmodule = $ workbook.VBProject.VBComponents.Add（）
 $ xlmodule.CodeModule.AddFromString（$代码）
  

但是我收到这个错误：

< pre class =lang-none prettyprint-override> 无法在表达式Null中调用方法。
Aucaractère.\Build-ADGrpsMembers2Excel.ps1：273：5
+ $ xlmodule = $ workbook.VBProject.VBComponents.Add（1）
+ ~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ InvalidOperation：（:) []，RuntimeException
+ FullyQualifiedErrorId：InvokeMethodOnNull


解决方案

我需要将VBA选项更改为

  $ excel = New-Object -ComObject Excel.Application 
 New-ItemProperty -PathHKCU：\Software\Microsoft\Office\ $（$ excel.Version）\excel\Security-Name AccessVBOM -Value 1 -Force | Out-Null 
 New-ItemProperty -PathHKCU：\Software\Microsoft\Office\ $（$ excel.Version）\excel\Security-Name VBAWarnings -Value 1 -Force | Out-Null 
  

  $ excel = New-Object -ComObject Excel.Application 
 New-ItemProperty -PathHKCU：\Software\Microsoft\Office\ $（$ excel.Version）\excel \Security-Name AccessVBOM -Value 1 -Force | Out-Null 
 New-ItemProperty -PathHKCU：\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 （$ saveName，50）
 
 Write-Verbose关闭$（$ WorkSheetName）
 $ Excel.Workbooks.Close（）
写入详细退出Excel
 $ Excel.Application.Quit 
  

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 correctly but I don't know how to put the VBA code in the sheet (rather than in a VBA module).

$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()

I have tried this:

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

But I got this error:

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

解决方案

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

my working code is :

$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中的excel.Worksheet中添加VBA代码？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！