Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Wednesday, March 21, 2012 8:11 PM | 1 vote
The following script does not work any more after my system is upgraded.
25: $excelApp = New-Object -COM Excel.Application
26: $excelApp.DisplayAlerts = $false
27: $excelApp.Visible = $true
28: $book = $excelApp.Workbooks.Open($raw_data_file)
29: $ExcelModule = $book.VBProject.VBComponents.Add(1)
Here is the error message I received.
You cannot call a method on a null-valued expression.
At C:\Users\fut\Desktop\PRScript\PRStatistic.ps1:29 char:48
- $ExcelModule = $book.VBProject.VBComponents.Add <<<< (1)
+ CategoryInfo : InvalidOperation: (Add:String) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
The system upgrade includes OS upgrade and MS Office upgrade from 2007 to 2010. The script worked fine before the system upgrade. What could cause this?
All replies (7)
Thursday, March 22, 2012 5:20 AM ✅Answered
Hi,
I searched the web and found the below thread, please refer to it:
Microsoft Excel - How to write vba-code to excel with powershell
Hope this helps.
Best Regards,
Yan Li
Yan Li
TechNet Community Support
Friday, March 23, 2012 7:05 AM ✅Answered | 1 vote
Most of the time this error suggest that you are trying to access property/method of the null object. Same thing is true in this case. Looking at out put of $Book variable, it suggest that value VBProject is null.
KeepChangeHistory : True
ListChangesOnNewSheet : False
VBProject :
IsInplace : False
PublishObjects : System.__ComObject
I suspect that PS is not able to identify VBProject details due to enhance security model in Office 2010. I can give you some pointer which you might find helpful.
- Check Macro setting
- Go to File >> Options >> Trust Center >> Trust Center settings >> Macro Settings
- Go to File >> Options >> Trust Center >> Trust Center settings >> Trusted Locations
Hope this helps...!!!
Thanks & Regards
Bhavik Solanki
Please click “Mark as Answer” if this post answers your question and click "Vote as Helpful if this Post helps you.
Wednesday, March 21, 2012 9:09 PM
I find the phrase "You cannot call a method on a null-valued expression" to be somewhat less than descriptive, but it seems to apply in a lot of cases ;-(
That said, I'd be surprised if an office upgrade was not in part responsible. But it occurs to me to ask what the value of the $raw_data_file variable is.
Al Dunbar
Wednesday, March 21, 2012 9:46 PM
Check $book (line 28) to see if it contains anything. You can simply type $book and hit [Enter]. If it is empty the Exception simply means nothing was assigned on which you could call the VBProject properties. I find when I get these exceptions the issue lies upstream where I attempted to assign the object a value and I didn't even realize it.
Wednesday, March 21, 2012 10:40 PM
Thanks for the replies. Here are $raw_data_file and $book. $raw_data_file looks right to me, but I have no clue if $book is right. It supposed to be the Excel file, C:\Users\fut\Desktop\PRScript\All.xls. I was able to open the Excel file using Excel though. It seems more like an Excel issue, doesn't it? Any help is appreciated.
[DBG]: PS Z:\>> $raw_data_file
C:\Users\fut\Desktop\PRScript\All.xls
[DBG]>>> Hit Line breakpoint on 'C:\Users\fut\Desktop\PRScript\PRStatistic.ps1:29'
[DBG]: PS Z:\>> $book
Application : Microsoft.Office.Interop.Excel.ApplicationClass
Creator : 1480803660
Parent : Microsoft.Office.Interop.Excel.ApplicationClass
AcceptLabelsInFormulas : False
ActiveChart :
ActiveSheet : System.__ComObject
Author :
AutoUpdateFrequency : 0
AutoUpdateSaveChanges :
ChangeHistoryDuration : 0
BuiltinDocumentProperties : System.__ComObject
Charts : System.__ComObject
CodeName :
_CodeName :
CommandBars :
Comments :
ConflictResolution : 1
Container :
CreateBackup : False
CustomDocumentProperties : System.__ComObject
Date1904 : False
DialogSheets : System.__ComObject
DisplayDrawingObjects : -4104
FileFormat : -4158
FullName : C:\Users\fut\Desktop\PRScript\All.xls
HasMailer : False
HasPassword : False
HasRoutingSlip : False
IsAddin : False
Keywords :
Mailer :
Modules : System.__ComObject
MultiUserEditing : False
Name : All.xls
Names : System.__ComObject
OnSave :
OnSheetActivate :
OnSheetDeactivate :
Path : C:\Users\fut\Desktop\PRScript
PersonalViewListSettings : True
PersonalViewPrintSettings : True
PrecisionAsDisplayed : False
ProtectStructure : False
ProtectWindows : False
ReadOnly : False
_ReadOnlyRecommended : False
RevisionNumber : 0
Routed : False
RoutingSlip : System.__ComObject
Saved : True
SaveLinkValues : True
Sheets : System.__ComObject
ShowConflictHistory : False
Styles : System.__ComObject
Subject :
Title :
UpdateRemoteReferences : True
UserControl :
UserStatus : {Fu, Tim, 3/21/2012 15:30, 1}
CustomViews : System.__ComObject
Windows : System.__ComObject
Worksheets : System.__ComObject
WriteReserved : False
WriteReservedBy : Fu, Tim
Excel4IntlMacroSheets : System.__ComObject
Excel4MacroSheets : System.__ComObject
TemplateRemoveExtData : False
HighlightChangesOnScreen : False
KeepChangeHistory : True
ListChangesOnNewSheet : False
VBProject :
IsInplace : False
PublishObjects : System.__ComObject
WebOptions : System.__ComObject
HTMLProject :
EnvelopeVisible : False
CalculationVersion : 0
VBASigned : False
ShowPivotTableFieldList : True
UpdateLinks : 1
EnableAutoRecover : True
RemovePersonalInformation : False
FullNameURLEncoded : C:\Users\fut\Desktop\PRScript\All.xls
Password : ********
WritePassword : ********
PasswordEncryptionProvider : Office
PasswordEncryptionAlgorithm : OfficeStandard
PasswordEncryptionKeyLength : 40
PasswordEncryptionFileProperties : False
ReadOnlyRecommended : False
SmartTagOptions :
Permission : System.__ComObject
SharedWorkspace :
Sync : System.__ComObject
XmlNamespaces : System.__ComObject
XmlMaps : System.__ComObject
SmartDocument : System.__ComObject
DocumentLibraryVersions :
InactiveListBorderVisible : True
DisplayInkComments : True
ContentTypeProperties :
Connections : System.__ComObject
Signatures : System.__ComObject
ServerPolicy :
DocumentInspectors : System.__ComObject
ServerViewableItems : System.__ComObject
TableStyles : System.__ComObject
DefaultTableStyle : System.__ComObject
DefaultPivotTableStyle : System.__ComObject
CheckCompatibility : False
HasVBProject : False
CustomXMLParts : System.__ComObject
Final : False
Research : System.__ComObject
Theme : System.__ComObject
Excel8CompatibilityMode : False
ConnectionsDisabled : False
ShowPivotChartActiveFields : False
IconSets : System.__ComObject
EncryptionProvider :
DoNotPromptForConvert : False
ForceFullCalculation : False
SlicerCaches : System.__ComObject
ActiveSlicer :
DefaultSlicerStyle : System.__ComObject
AccuracyVersion : 0
Thursday, March 22, 2012 4:15 AM
I have to ask, Why are you adding a VBA module here?
The next question is;
Did MS change the name of VBProject.VBComponents in Excel 2010
If so, that would cause this problem
Tuesday, May 16, 2017 6:18 PM
I don't remember where I found it, but I use this at the top of my scripts to turn on macros as needed.
Still works for me all these years later. Maybe it can help someone else.
#Enable Excel macros in registry.
New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\($excel.Version)\excel\Security" -Name AccessVBOM -PropertyType DWORD -Value 1 -Force | Out-Null
New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\($excel.Version)\excel\Security" -Name VBAWarnings -PropertyType DWORD -Value 1 -Force | Out-Null