I am using the attached script to perform an inventory export then as excel file. I have used LucDtechnique from this post to store the information to a temp CSV file and later save them as a XLS file. I need to perform a some formatting in the report, like changing font size of the header, adding some background colours.
As I am exporting them to a CSV file it doesn’t take the formatting. Is there any other way to achieve the formatting?
You might want to have a look at my Export-Xlsx, the sequel, and ordered data post.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
You might want to have a look at my Export-Xlsx, the sequel, and ordered data post.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
I did check the post but I am not quite sure how to use them with the existing script which I am using.
Also I want to have only header color ,autofit of the columns nd in the first sheet add a title in the first row . If you could let me know how to run the script, I will take a look further..
The function should be quite easy to use, there are some examples in the post.
What exactly is giving you problems ?
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
This is how i am trying to run the script
1) Imported the Export-XLSx to the current powerCLI session using Import-Module
2) Ran the .\Excel-Report.ps1 | .\Export-XLSx.PS1 -Path "C:\Aravind\Scripts\report.xlsx", it prompts me with my vCenter credentials and look like its running.
But it doesn't product any output file and throws with the below error
Exception calling "SaveAs" with "1" argument(s): "Microsoft Excel cannot access the file 'C:\Scripts\DB1B9200'. There are several possible reasons:
The file name or path does not exist.
The file is being used by another program.
The workbook you are trying to save has the same name as a currently open workbook."
At C:\Aravind\Scripts\Excel-Report.ps1:284 char:62
+ $workbook.SaveAs("C:\Scripts\$($vCenter)-vAssessmentReport-$($Date).xls")
+ ~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ComMethodTargetInvocation
There is a saveAS in my existing script , should be this removed ?
I have used the attached script to create worksheet which call the function when saving the file but getting the below error
Unable to find type [Microsoft.Office.Interop.Excel.XLFileFormat]. Make sure that the assembly that contains this type is loaded.
At C:\Aravind\Scripts\report.PS1:169 char:5
+ $xlFixedFormat = [Microsoft.Office.Interop.Excel.XLFileFormat]::xlWorkbookNo ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (Microsoft.Offic...el.XLFileFormat:TypeName) [], RuntimeException
+ FullyQualifiedErrorId : TypeNotFound
Unable to find type [Microsoft.Office.Interop.Excel.XLFileFormat]. Make sure that the assembly that contains this type is loaded.
At C:\Aravind\Scripts\report.PS1:175 char:9
+ $xlFixedFormat = [Microsoft.Office.Interop.Excel.XLFileFormat]::xlWorkbo ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (Microsoft.Offic...el.XLFileFormat:TypeName) [], RuntimeException
+ FullyQualifiedErrorId : TypeNotFound
Exception calling "SaveAs" with "2" argument(s): "SaveAs method of Workbook class failed"
At C:\Aravind\Scripts\report.PS1:252 char:5
+ $wb.SaveAs($Path,$xlFixedFormat)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ComMethodTargetInvocation
It looks as if the Office Primary Interop Assemblies are not present on the client where you ran the script.
Note, you do not need Excel to be installed on the station, just the Interop assemblies.
See here for the redistributables
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Earlier I was running the script from the script where I have excel 2013 installed . Not I have installed Interop assemblies on a new system and ran the script it throws me a different error. Have a Powershell v2.0 on the system, Does the function requires PS 3.0?
New-Object : Cannot load COM type Excel.Application.
At C:\Scripts\report.PS1:167 char:21
+ $xl = New-Object <<<< -ComObject Excel.Application
+ CategoryInfo : InvalidType: (:) [New-Object], PSArgumentException
+ FullyQualifiedErrorId : CannotLoadComObjectType,Microsoft.PowerShell.Commands.NewObjectCommand
Property 'DisplayAlerts' cannot be found on this object; make sure it exists and is settable.
At C:\Scripts\report.PS1:168 char:9
+ $xl. <<<< DisplayAlerts = $False
+ CategoryInfo : InvalidOperation: (DisplayAlerts:String) [], RuntimeException
+ FullyQualifiedErrorId : PropertyNotFound
Unable to find type [Microsoft.Office.Interop.Excel.XLFileFormat]: make sure that the assembly containing this type is loaded.
At C:\Scripts\report.PS1:169 char:67
+ $xlFixedFormat = [Microsoft.Office.Interop.Excel.XLFileFormat] <<<< ::xlWorkbookNormal
+ CategoryInfo : InvalidOperation: (Microsoft.Offic...el.XLFileFormat:String) [], RuntimeException
+ FullyQualifiedErrorId : TypeNotFound
You cannot call a method on a null-valued expression.
At C:\Scripts\report.PS1:195 char:30
+ $wb = $xl.Workbooks.Add <<<< ()
+ CategoryInfo : InvalidOperation: (Add:String) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
You cannot call a method on a null-valued expression.
At C:\Scripts\report.PS1:221 char:29
+ $usedRange = $ws.Range <<<< ("a1","$($A1Cols)$($Rows)")
+ CategoryInfo : InvalidOperation: (Range:String) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
You cannot call a method on a null-valued expression.
At C:\Scripts\report.PS1:223 char:18
+ $ws.Range <<<< ("a1","$($A1Cols)1").Interior.ColorIndex = 48
+ CategoryInfo : InvalidOperation: (Range:String) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
You cannot call a method on a null-valued expression.
At C:\Scripts\report.PS1:224 char:18
+ $ws.Range <<<< ("a1","$($A1Cols)1").Font.Bold = $True
+ CategoryInfo : InvalidOperation: (Range:String) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
Property 'Value2' cannot be found on this object; make sure it exists and is settable.
At C:\Scripts\report.PS1:228 char:16
+ $usedRange. <<<< Value2 = $Array
+ CategoryInfo : InvalidOperation: (Value2:String) [], RuntimeException
+ FullyQualifiedErrorId : PropertyNotFound
Sorry, my bad.
This version requires Excel to be installed I'm afraid.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Ok.. So we required both Excel and Interop assemblies to be installed??
Will the script support excel version 2007 and above??
Is it ok to run with Windows PS 2.0??
No, if Excel is installed, the Interop Assemblies should be there as well.
Should work with PS v2
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
How do I append multiple worksheet in the same excel file, Now when I try to generate 2 different report 2 excel files are getting created. I have checked and made sure -AppendWorksheet:$false is NOT set and have added - SheetPosition "end", but i get only two file.
The default is $false, so you should add -AppendWorksheet:$true to the call.
Did you already try that ?
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
That works for me...
Probably my last question
1) How to add a title to the worksheet(1st worksheet) and then the script output gets placed, Add title in row 1 and the script output starts from row 2 or so...
2) I run the script in following order to collect the information
report1
report2
report3
..
....
reportN
But when the report are exported to excel the last reports is present in first worksheet like
reportN
...
...
report3
report2
report1
I got the answer for 2nd question by adding -SheetPosition "end",
If you can help with 1st query it would be fine..
Use the WorksheetName parameter.
See the 3th example in the post
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Hi LucD
I have been using the Export-Xlsx function for reporting various things in vCenter for a good long while now and it has worked great on Windows 7 with Office 2013. I recently moved to Windows 10 and Office 2016 and i am now getting the following errors:-
Unable to find type [Microsoft.Office.Interop.Excel.XLFileFormat].
At C:\Users\blah\OneDrive\MyDocs\Stash\vmware\Export-Xlsx.ps1:169 char:5
+ $xlFixedFormat = [Microsoft.Office.Interop.Excel.XLFileFormat]::x ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (Microsoft.Offic...el.XLFileFormat:TypeName) [], RuntimeException
+ FullyQualifiedErrorId : TypeNotFound
Unable to find type [Microsoft.Office.Interop.Excel.XLFileFormat].
At C:\Users\blah\OneDrive\MyDocs\Stash\vmware\Export-Xlsx.ps1:175 char:9
+ $xlFixedFormat = [Microsoft.Office.Interop.Excel.XLFileFormat ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (Microsoft.Offic...el.XLFileFormat:TypeName) [], RuntimeException
+ FullyQualifiedErrorId : TypeNotFound
Unable to get the SaveAs property of the Workbook class
At C:\Users\blah\OneDrive\MyDocs\Stash\vmware\Export-Xlsx.ps1:252 char:5
+ $wb.SaveAs($Path,$xlFixedFormat)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (:) [], COMException
+ FullyQualifiedErrorId : System.Runtime.InteropServices.COMException
My script is trying to run this:-
get-vm | get-snapshot | select VM,Name,Description | Export-Xlsx -AppendWorksheet -Path $snapdiskReport -WorksheetName $datetime -SheetPosition end | out-null
My PowerCLi version is:-
VMware vSphere PowerCLI 6.0 Release 1 build 2548067
I tried to look for relevant versions of the Interop Assemblies for Office 2016 but couldn't locate them. In desperation I tried to install the Office 2010 ones you linked but unsurprisingly it didn't work. Any ideas?
Thanks in advance
Danny
p.s. I know this is an old thread but it was top of the list in google so thought it might be useful for others to raise this here
The simplest answer for this issue was simply to install Excel 2013 alongside the 2016 install.....everything works again!
I'll in any case have a look to try and get it to work with 2016
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference