PowerShell to insert code in Excel VBA

Question

I'm creating a script for my Work, where i need to insert some VBA code in Excel. I've all worked out, except how to insert the code in the VBProject. I need 2 codes inserted. I need a code inserted in Sheet1(aka tmpArk1) and in ThisWorkbook. How do i do this?

My code so far:

Function Get-FileName($initialDirectory)
{
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") |   Out-Null

$OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
$OpenFileDialog.initialDirectory = $initialDirectory
$OpenFileDialog.filter = "xlsx (*.xlsx)| *.xlsx"
$OpenFileDialog.ShowDialog() | Out-Null
$OpenFileDialog.filename
}

$inputplace = Get-FileName "P:\" ".xlsx"
$inputname = [System.IO.Path]::GetFileNameWithoutExtension($inputplace)
$inputpath = [System.IO.Path]::GetDirectoryName($inputplace)

########################################
########### Starting Excel #############
########################################


$Excel = New-Object -ComObject Excel.Application
$ExcelVersion = $Excel.Version

New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\$ExcelVersion\Excel\Security" -Name AccessVBOM -Value 1 -Force | Out-Null 
New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\$ExcelVersion\Excel\Security" -Name VBAWarnings  -Value 1 -Force | Out-Null

$Excel.Visible = $true
$Excel.DisplayAlerts = $false

########################################
####### Working with workbook ##########
########################################

#Opens workbook to be transfered from, ReadOnly
$workbook1 = $Excel.Workbooks.Open($inputplace,$null, $true)

#Create workbook to work with
$workbook2 = $Excel.Workbooks.add()


########################################
####### The code to be inserted ########
########################################

#Code Alpha

#$codeAlfa = @"
#    Some code here
#"@

#Code beta

#$codeBeta = @"
#    Some code here
#"@


########################################
####### Working with worksheets ########
########################################

#Add 1 worksheets to workbook
$workbook2.Worksheets.Add()

#Transfer Sheet from opened workbook to new workbook
$SheetToCopy = $workbook1.sheets.item(1)
$TransferTarget = $workbook2.sheets.item(1)
$SheetToCopy.copy($TransferTarget)

#Change name of all 4 worksheets
$workbook2.Worksheets.Item(1).name = "tmpArk1"
$workbook2.Worksheets.Item(2).name = "output"
$workbook2.Worksheets.Item(3).name = "net"
$workbook2.Worksheets.Item(4).name = "data"
$workbook2.Worksheets.Item(5).name = "bilag"

#Close Workbook1
$Workbook1.Close()

#inserting the codes
$ExcelModuleAlfa = $workbook2.VBProject.VBComponents.Add(1)
$ExcelModuleAlfa.CodeModule.AddFromString($codeAlfa)

########################################
######### Clean up enviroment ##########
########################################

#Save workbook to hard drive
Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$workbook2.SaveAs("P:\" + $inputname + "_auto",[Microsoft.Office.Interop.Excel.XlFileFormat]::xlExcel8)

#Close workbook and quit Excel
#$Excel.Workbooks.Close()
#$Excel.Quit()

#Stop Excel process
#Get-Process excel | Stop-Process -Force

#Release com object
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)

When I try to run the script, I get this as my output error.

You cannot call a method on a null-valued expression.
At C:\Users\G017116\Desktop\PowerShell InWork.ps1:995 char:57
+ $ExcelModuleAlfa = $workbook2.VBProject.VBComponents.Add <<<< (1)
+ CategoryInfo          : InvalidOperation: (Add:String) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\Users\G017116\Desktop\PowerShell InWork.ps1:996 char:42
+ $ExcelModuleAlfa.CodeModule.AddFromString <<<< ($codeAlfa)
+ CategoryInfo          : InvalidOperation: (AddFromString:String) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

Answer 1

Try running PowerShell as Administrator.

I was able to replicate the issue when the "Trust access to the VBA project object model" was disabled. Yes, the two New-ItemProperty ... HKCU ... settings should set those options, but access to the registry may need Administrator access in order for it to be correctly set.

Try manually setting/Verifying that the Trust Center Permissions are:

• Select "Enable all macros (not recommended; potentially dangerous code can run"
• Check "Trust access to the VBA project object model"

Try running the minimal set of commands here:

$Excel = New-Object -ComObject Excel.Application
$ExcelVersion = $Excel.Version
New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\$ExcelVersion\Excel\Security" -Name AccessVBOM -Value 1 -Force | Out-Null
New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\$ExcelVersion\Excel\Security" -Name VBAWarnings  -Value 1 -Force | Out-Null
$Excel.Visible = $true
$workbook2 = $Excel.Workbooks.add()
$workbook2.VBProject.VBComponents

The last statement $workbook2.VBProject.VBComponents should return something like:

Saved           : True
Name            : ThisWorkbook
Designer        :
CodeModule      : System.__ComObject
Type            : 100
VBE             : System.__ComObject
Collection      : System.__ComObject
HasOpenDesigner : False
Properties      : System.__ComObject
DesignerID      :

Saved           : True
Name            : Sheet1
Designer        :
CodeModule      : System.__ComObject
Type            : 100
VBE             : System.__ComObject
Collection      : System.__ComObject
HasOpenDesigner : False
Properties      : System.__ComObject
DesignerID      :

If it does not, or errors out as null, then the Trust Center permissions didn't get set. (you should be able to use the opened Excel document to go and verify the permissions)