Format table stopped working

[Reiner2010]

I have used the following lines in a script: $ListObject = $Excel.ActiveSheet.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $Excel.ActiveCell.CurrentRegion, $null ,[Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes) $ListObject.Name = "TableData" $ListObject.TableStyle = "TableStyleMedium9"

These commands worked fine, but now the first line returns an error: Unable to find type [Microsoft.Office.Interop.Excel.XlListObjectSourceType]

My environment is: Powershell 7.3.4 Powershell Module ImportExcel 7.8.4, Windows 11 with current patch level

How can I get these lines running again?

[dfinke]

This looks like your using COM and Excel. The Import-Excel module does not rely on that. Please include a more complete repro, including the xlsx, and how you create the $Excel object.

[Reiner2010]

Hello Doug,

here is an example script. All was working until 19th of May. Afterwards the red marked lines started to throw an error.

Define the destination file name

$Myfile = '.\ExcelExample.xlsx'

MAIN

Write-Host "Script: Excel operations with PowerShell ..." Import-Module "ImportExcel"

Define common parameter for the Excel object

$Excel = New-Object -ComObject excel.application $Excel.displayalerts = $false $Excel.displayalerts = $true $Excel.visible = $true

Add a workbook to the new Excel file.

$Workbook = $Excel.workbooks.add()

Add a sheet to the Excel workbook.

$Worksheet = $Workbook.worksheets.item(1) $Worksheet.name = "User Report"

Add captions to the Excel sheet.

$Worksheet.cells.item(1,1) = 'AccountName' $Worksheet.cells.item(1,2) = 'UPN' $Worksheet.cells.item(1,3) = 'Country' $Worksheet.cells.item(1,4) = 'City' $Worksheet.cells.item(1,5) = 'PhoneNumber' $Worksheet.cells.item(1,6) = 'EnterpriseVoiceEnabled' $Worksheet.cells.item(1,7) = 'VoiceRouting' $Worksheet.cells.item(1,8) = 'DialPlan' $Worksheet.cells.item(1,9) = 'AccountStatus'

Format the header line

$Worksheet.Range("A1:I1").font.size = 12 $Worksheet.Range("A1:I1").font.bold = $true $Worksheet.Range("A1:I1").font.name = 'Arial'

Add data to the Excel sheet.

$Worksheet.cells.item(2,1) = 'Test' $Worksheet.cells.item(2,2) = @.***' $Worksheet.cells.item(2,3) = 'Country Name' $Worksheet.cells.item(2,4) = 'City Name' $Worksheet.cells.item(2,5) = 'E164 phone number' $Worksheet.cells.item(2,6) = 'Enabled/Disabled' $Worksheet.cells.item(2,7) = 'Voice Routing Policy' $Worksheet.cells.item(2,8) = 'Dial Plan Policy' $Worksheet.cells.item(2,9) = 'Account Status'

Centralize all data. Horizontal and vertical alignment disabled.

$rowCount = $Worksheet.UsedRange.Rows.Count $Worksheet.Range("A1:I1").HorizontalAlignment = -4131 $Worksheet.Range("A2:G$rowCount").HorizontalAlignment = -4131 $Worksheet.Range("H2:I$rowCount").HorizontalAlignment = -4108 $Worksheet.Range("A1:I$rowCount").VerticalAlignment = -4108

Format as table

$ListObject = $Excel.ActiveSheet.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $Excel.ActiveCell.CurrentRegion, $null ,[Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes) $ListObject.Name = "TableData" $ListObject.TableStyle = "TableStyleMedium9"

format column width

$Worksheet.Columns.AutoFit()

Save the excel file using Powershell.

$Workbook.Saveas($Myfile) $Excel.Quit()

Some attachments. First to show the progress before the first “error” line is execute and second to show the error message. @.***

@.***

Grüße, regards Reiner

Internal: All rights reserved. Distribution within the company, customer and partners

Von: Doug Finke @.> Gesendet: Mittwoch, 7. Juni 2023 19:26 An: dfinke/ImportExcel @.> Cc: Foerg Reiner IM2 @.>; Author @.> Betreff: Re: [dfinke/ImportExcel] Format table stopped working (Issue #1463)

Sie erhalten nicht oft eine E-Mail von @.**@.>. Erfahren Sie, warum dies wichtig isthttps://aka.ms/LearnAboutSenderIdentification Caution: This email originated from outside the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.

Please include a more complete repro, including the xlsx, and how you create the $Excel object.

— Reply to this email directly, view it on GitHubhttps://github.com/dfinke/ImportExcel/issues/1463#issuecomment-1581236228, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A5X6NXAWFCFAYR5FFCM3NFTXKC2R3ANCNFSM6AAAAAAY5K4IE4. You are receiving this because you authored the thread.Message ID: @.**@.>>

[dfinke]

Yeah, that is using $Excel = New-Object -ComObject excel.application.

That has nothing to do with my PowerShell module.

Stackoverflow is where you want to post this question.

[Reiner2010]

Hello Doug,

OK, you’re right. I opened an incident at MS and the service desk agent told me to contact you. But, I guess no one has really done a close look on the code.

Anyway, I will change my script and will use your module.

Grüße, regards Reiner

Von: Doug Finke @.> Gesendet: Freitag, 9. Juni 2023 15:43 An: dfinke/ImportExcel @.> Cc: Foerg Reiner IM2 @.>; Author @.> Betreff: Re: [dfinke/ImportExcel] Format table stopped working (Issue #1463)

Sie erhalten nicht oft eine E-Mail von @.**@.>. Erfahren Sie, warum dies wichtig isthttps://aka.ms/LearnAboutSenderIdentification Caution: This email originated from outside the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.

Yeah, that is using $Excel = New-Object -ComObject excel.application.

That has nothing to do with my PowerShell module.

Stackoverflow is where you want to post this question.

— Reply to this email directly, view it on GitHubhttps://github.com/dfinke/ImportExcel/issues/1463#issuecomment-1584599945, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A5X6NXGVCHKF6BXFZTYKPDTXKMR53ANCNFSM6AAAAAAY5K4IE4. You are receiving this because you authored the thread.Message ID: @.**@.>>

[Reiner2010]

Hello Doug,

I’m trying to change a cell to a link with a display text (e.g. Report for City 1) and the link behind this, like \servername\share\filename.xlsxfile://servername/share/filename.xlsx.

Currently I write the values like \servername\share\filename.xlsx to those cells and tried to change this. I moved through nearly all examples in the ImportExcel folder, but didn’t find something that is useful here.

Do you have a hint for me?

LocationId ReportLocation City 1 Report for City 1file://srvvib726/MSTeamsReports$/AT_BRA/_Reporting_Telephony_23_07_2023_20_00.xlsx City 2 \servername\Share\filename.xlsxfile://servername/Share/filename.xlsx

Grüße, regards Reiner

Internal: All rights reserved. Distribution within the company, customer and partners