Export-Excel Not working for 1 variable

PowerShell Help
1

I’m writing a script to extract our GPO settings and it exports it an Excel Workbook. I have multiple sheets within the workbook that Export-Excel is working just fine with, many with a lot more data. Many with just one or two lines of data.

There is one that I can’t figure out why it won’t export. The export line looks like this:
Export-Excel -Path $DataFile -InputObject $PrinterSettingsPolicySettings -WorksheetName "Comp - Printer Settings" -AutoSize -AutoFilter -FreezeTopRow -TableStyle Medium7

What’s weird is that $PrinterSettingsPolicySettings will export data if I use Export-Csv. I can then import-csv the data again and then export-excel and that works.

Any suggestions on what I should be looking at?

2

Any chance you can take one of the objects from $PrinterSettingsPolicySettings and pipe it to Get-Member and share the results?
Is it possible to share what properties are missing from the Excel workbook but not thr CSV (when using Export-Csv)? Just curious to see if maybe there’s something specific about the missing properties.

3

Any specific reason you aren’t piping the data to export-excel? The pipeline does lots of stuff including unrolling collections. I think you should try this and see what happens.

$PrinterSettingsPolicySettings | Export-Excel -Path $DataFile  -WorksheetName "Comp - Printer Settings" -AutoSize -AutoFilter -FreezeTopRow -TableStyle Medium7
1 Like
4

Here it is:

$PrinterSettingsPolicySettings | gm

   TypeName: PrinterSettings

Name            MemberType Definition
----            ---------- ----------
Equals          Method     bool Equals(System.Object obj)
GetHashCode     Method     int GetHashCode()
GetType         Method     type GetType()
ToString        Method     string ToString()
Action          Property   string Action {get;set;}
bypassErrors    Property   string bypassErrors {get;set;}
Comment         Property   string Comment {get;set;}
Default         Property   string Default {get;set;}
DeleteAll       Property   string DeleteAll {get;set;}
doubleSpool     Property   string doubleSpool {get;set;}
Filters         Property   string Filters {get;set;}
GpoSettingOrder Property   string GpoSettingOrder {get;set;}
ipAddress       Property   string ipAddress {get;set;}
LocalName       Property   string LocalName {get;set;}
Location        Property   string Location {get;set;}
lprQueue        Property   string lprQueue {get;set;}
Name            Property   string Name {get;set;}
Path            Property   string Path {get;set;}
Policy          Property   string Policy {get;set;}
PolicyType      Property   string PolicyType {get;set;}
portNumber      Property   string portNumber {get;set;}
Protocol        Property   string Protocol {get;set;}
Section         Property   string Section {get;set;}
SkipLocal       Property   string SkipLocal {get;set;}
snmpCommunity   Property   string snmpCommunity {get;set;}
snmpDevIndex    Property   string snmpDevIndex {get;set;}
snmpEnabled     Property   string snmpEnabled {get;set;}
Status          Property   string Status {get;set;}
UseDNS          Property   string UseDNS {get;set;}
UseIpV6         Property   string UseIpV6 {get;set;}

When I export the variable using Export-Csv the csv file contains this:

"Policy","Section","PolicyType","Name","Status","bypassErrors","GpoSettingOrder","Filters","lprQueue","snmpCommunity","Protocol","portNumber","doubleSpool","snmpEnabled","snmpDevIndex","ipAddress","Action","Location","LocalName","Comment","Default","SkipLocal","UseDNS","UseIpV6","Path","DeleteAll"
"CPLP_PG_Lab_Printer","Computer","PrintersSettings","PGP110","PGP110","1","1","","","","PROTOCOL_RAWTCP_TYPE","9100","0","0","1","PGP110","U","","PGP110","","0","0","1","0","\\PRINTSERVER\PGP110","0"

The Excel file contains only “PrinterSettings” which is the custom class created in the function:

                    "PrintersSettings" {
                        Write-Host "Found a PrinterSettings in Computer section in $($GpoDetail.Name)." -ForegroundColor Green
                        foreach ($Printer in $compext.Printers.PortPrinter) {
                            $PrinterSettingsPolicySettings.Add($(Get-PrinterSetting -Value $Printer))
                        }

The “Get-PrinterSetting” function works and returns the results you see in the Csv file.

5

I’ve tried piping it with the same result.

6

Haven’t looked into the whys, but Export-Excel doesn’t see the properties of a custom class. Try this:

$PrinterSettingsPolicySettings | Select-Object * | Export-Excel -Path $DataFile
1 Like
7

You’re mostly correct. All of the items I’m exporting are from custom classes. I added two more today to the batch and both of them were the same as the printer one - no data exported despite it being there.

Your trick of piping it to select-object and then to export-excel worked.

Thank you.

8

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.