How do I fix New-Object Com Class Error for Alteryx?
pavan
10/07/22 09:28 AM
I have an Alteryx workflow that runs a powershell script to convert a csv to excel. It works locally, but when I publish that workflow to Alteryx Gallery, I get an error and my log produces something like this: New-Object: Retrieving the COM class factory for component with CLSID.... $excel = New-Object -ComObject excel.application... Resource Unavailable... FullyQualifiedErrorId: No COMClassIdentified,Microsoft.PowerShell.Commands.NewObjectCommand...
This reads to me like excel is not installed on the alteryx server. Is my assumption correct? What do I need to do to fix this error?
$SharedDriveFolderPath = "\\---\shares\Groups\---\---\---\---\--\B\"
$files = Get-ChildItem $SharedDriveFolderPath -Filter *.csv
foreach ($f in $files){
$outfilename = $f.BaseName +'.xlsx'
$outfilename
#Define locations and delimiter
$csv = "\\---\shares\Groups\---\---\---\---\--\B\$f" #Location of the source file
$xlsx = "\\---\shares\Groups\---\---\---\---\---\---\C\$outfilename" #Desired location of output
$delimiter = "," #Specify the delimiter used in the file
# Create a new Excel workbook with one empty sheet
$excel = New-Object -ComObject excel.application
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)
# Build the QueryTables.Add command and reformat the data
$TxtConnector = ("TEXT;" + $csv)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)
$query.TextFileOtherDelimiter = $delimiter
$query.TextFileParseType = 1
$query.TextFileColumnDataTypes = ,1 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1
# Execute & delete the import query
$query.Refresh()
$query.Delete()
# Save & close the Workbook as XLSX.
$Workbook.SaveAs($xlsx,51)
$excel.Quit()
}
Thanks for this tip. I think this is cool. Trying it out now.
