excel cannot complete this task with available resources

more data maybe...
is the size of data changed recently?

How much memory is installed in the system, and how much free disk is available on the system drive?

Yes, the data changes monthly, I have been using the same code for two monthly, this is the third. With all combining, I would say it is about 30+MB.

At first, I thought it was some file corrupted. But it was not, the error shows up randomly (not with particular files)  thanks

I have about 600GB free locally..  thanks

Does that function break the maximums?

https://docs.microsoft.com/en-us/office/troubleshoot/excel/excel-cannot-complete-task-with-available

• The maximum worksheet size limit is 1,048,576 rows by 16,384 columns.
• The total number of characters that a cell can contain is 32,767 characters.
• The maximum selected range in a calculation is 2,048.
• The maximum nested level of functions in a calculation is 64.

• 
  The maximum worksheet size limit is 1,048,576 rows by 16,384 columns.  
• >> CV is the last column
• The total number of characters that a cell can contain is 32,767 characters.
• >>The longest cell is the address, so I do not thnk so.
• The maximum selected range in a calculation is 2,048.
• >>there is no calculation
• The maximum nested level of functions in a calculation is 64.
• >>no function

thanks

ok, when you run it today
does it always gives error on same file/location/time

or error is totally random?

your specs not bad, better than mine LOL

ok, then it is BAD CODE :)
we need to see the code
can you attach the code if possible?

please have a look. thanks

# del existing files
$outfile = "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\OUTPUT\MYcombinePropertyFile.csv"
if(Test-path $outfile){
    Remove-Item $outfile -Confirm:$false
}

$outfileXLSX = "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\OUTPUT\MYcombinePropertyFile.xlsx"
if(Test-path $outfileXLSX){
    Remove-Item $outfileXLSX -Confirm:$false
}


# get 3 different file types and convert them into CSV before combining
$filesTXT = Get-ChildItem "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\Source" -Filter *.txt
$filesXLSX = Get-ChildItem "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\Source" -Filter *.xlsx
$filesXLS = Get-ChildItem "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\Source" -Filter *.xls





$files = Get-ChildItem "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\Source" -Filter *.csv
# Check first line if "Trans ID" exists, skip lines
foreach ($f in $files){
    $firstLine = Get-Content -Path $f.FullName -TotalCount 1
    if ($firstLine -like "Transaction ID,*") {
        (Get-Content $f.FullName | Select-Object -Skip 1 | Add-Content $outfile)
    }elseif ($firstLine -like "1,2,3*") {
        (Get-Content $f.FullName | Select-Object -Skip 2 | Add-Content $outfile)
    }elseif ($firstLine -like "Tran ID*") {
        (Get-Content $f.FullName | Select-Object -Skip 1 | Add-Content $outfile)
    }else{
        (Get-Content $f.FullName | Add-Content $outfile)
    }
}




# load into Excel
# convert CSV to XLSX
$excel = New-Object -ComObject Excel.Application 
$excel.Visible = $true
$excel.Workbooks.Open($outfile).SaveAs("$outfileXLSX",51)
$excel.Quit()

#explorer.exe "/Select,C:\Users\MY.LOCAL\Desktop\database\Combine\combineLoanFile.xlsx"

explorer.exe "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\OUTPUT"

Select allOpen in new window

I left out a piece of the code... sorry

# del existing files
$outfile = "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\OUTPUT\MYcombinePropertyFile.csv"
if(Test-path $outfile){
    Remove-Item $outfile -Confirm:$false
}

$outfileXLSX = "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\OUTPUT\MYcombinePropertyFile.xlsx"
if(Test-path $outfileXLSX){
    Remove-Item $outfileXLSX -Confirm:$false
}


# get 3 different file types and convert them into CSV before combining
$filesTXT = Get-ChildItem "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\Source" -Filter *.txt
$filesXLSX = Get-ChildItem "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\Source" -Filter *.xlsx
$filesXLS = Get-ChildItem "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\Source" -Filter *.xls


# convert TXT into CSV
foreach($file in ($filesTXT)) {
    #Changing of extension
    $NewName = $file.BaseName + '.csv'
    $file | Rename-Item -NewName $NewName
}

# convert XLSX into CSV
foreach($file in ($filesXLSX)) {

  $newname = $file.FullName -replace '\.xlsx$', '.csv'
  $ExcelWB = new-object -comobject excel.application
  $Workbook = $ExcelWB.Workbooks.Open($file.FullName) 
  $Workbook.SaveAs($newname,6)
  $Workbook.Close($false)
  $ExcelWB.quit()

}

# convert XLS into CSV
foreach($file in ($filesXLS)) {

  $newname = $file.FullName -replace '\.xls$', '.csv'
  $ExcelWB = new-object -comobject excel.application
  $Workbook = $ExcelWB.Workbooks.Open($file.FullName) 
  $Workbook.SaveAs($newname,6)
  $Workbook.Close($false)
  $ExcelWB.quit()

}


$files = Get-ChildItem "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\Source" -Filter *.csv
# Check first line if "Trans ID" exists, skip lines
foreach ($f in $files){
    $firstLine = Get-Content -Path $f.FullName -TotalCount 1
    if ($firstLine -like "Transaction ID,*") {
        (Get-Content $f.FullName | Select-Object -Skip 1 | Add-Content $outfile)
    }elseif ($firstLine -like "1,2,3*") {
        (Get-Content $f.FullName | Select-Object -Skip 2 | Add-Content $outfile)
    }elseif ($firstLine -like "Tran ID*") {
        (Get-Content $f.FullName | Select-Object -Skip 1 | Add-Content $outfile)
    }else{
        (Get-Content $f.FullName | Add-Content $outfile)
    }
}




# load into Excel
# convert CSV to XLSX
$excel = New-Object -ComObject Excel.Application 
$excel.Visible = $true
$excel.Workbooks.Open($outfile).SaveAs("$outfileXLSX",51)
$excel.Quit()

#explorer.exe "/Select,C:\Users\MY.LOCAL\Desktop\database\Combine\combineLoanFile.xlsx"

explorer.exe "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\OUTPUT"

Select allOpen in new window

• >> CV is the last column

What does that mean?  You are combining 900 files into one, right?  

It is possible that one of those files is bad.  Try grouping them into 9 different groups of 100 each and run your code against each 100. If you get 8 good runs and 1 bad, that may indicate one file is bad. If it works 9 times, then there may be a limit issue.

900 files each with over a1000 rows will break those limits. 

comment out

$excel = New-Object -ComObject Excel.Application 
$excel.Visible = $true
$excel.Workbooks.Open($outfile).SaveAs("$outfileXLSX",51)
$excel.Quit()

Select allOpen in new window

run it
open csv and save as excel
does it work this way?

one more thing to try

process only

txt files
xls files
xssx

then

txt + xls
txt + xlsx
xls + xlsx

I want to find out which file types creates problem...

When I run it by commenting them out, I did not receive any error. But when I open that CSV, the error came back. After clicking OK, I could see the data, BUT, it is way short. It only shows about 30,000 rows while I know for sure, it should be more than 200K rows (we had someone combining them manually just to verify. )  thanks

What I would try is to create a separate log file.  

At each part of your code, write to the log file, the name of the file you are starting, and which line of code you are one. Then write a line to the log file. Write to your log file throughout your code so you may have 8 to 10 places in your code above to write to the log file.

This will help identify where the bad data is coming from.  

it is the "# convert XLSX into CSV" part, when I run 
"# convert TXT into CSV"  and "# convert XLS into CSV" together, there is no problem.
But 95% of the files are XLSX that need to be converted. thanks




how can I add the log part? thanks

Are you saying this part

# convert TXT into CSV
foreach($file in ($filesTXT)) {
    #Changing of extension
    $NewName = $file.BaseName + '.csv'
    $file | Rename-Item -NewName $NewName
}

Select allOpen in new window

Is it the  last line where the error is thrown?

how can I add the log part? thanks 

you can use `echo` left and right, inside loops
so you can see whats going on

echo "XLS File opened happened: $file.FullName"

this is the error from powershell..thanks

The remote procedure call failed. (Exception from HRESULT: 0x800706BE)
At C:\Users\MY.LOCAL\Desktop\Admin\MY\LeadsDB\Combine\PropertyFile\WF\2-test.ps1:73 char:1
+ $excel.Workbooks.Open($outfile).SaveAs("$outfileXLSX",51)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException
 
Exception calling "Quit" with "0" argument(s): "The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)"
At C:\Users\MY.LOCAL\Desktop\Admin\MY\LeadsDB\Combine\PropertyFile\WF\2-test.ps1:74 char:1
+ $excel.Quit()
+ ~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : COMException

Converting TXT, XLSX, XLS to CSV does not create issues. It is the combining part. Sorry if I was not clear enough.  thanks

add some sleep, 1000 ms = 1 sec between operations

after excel close command, or in loops

like

# convert XLS into CSV
foreach($file in ($filesXLS)) {

  $newname = $file.FullName -replace '\.xls$', '.csv'
  $ExcelWB = new-object -comobject excel.application
  $Workbook = $ExcelWB.Workbooks.Open($file.FullName) 
  $Workbook.SaveAs($newname,6)
  $Workbook.Close($false)
  $ExcelWB.quit()
  Start-Sleep -ms 1000
}

Select allOpen in new window

testing now

maybe 1000 ms is too much, it will take 900 sec, 15 minutes :) for 900 files
make it 250 ms and try...

I am assuming, you open excel and do something and close immediately hundreds of time and PC crashes...

use

Start-Sleep -millisecond 250

Select allOpen in new window

Start-Sleep (Microsoft.PowerShell.Utility)

https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/start-sleep?view=powershell-7.1

You can also just run a test before looping to see if it is open or closed. 

I am getting this

Start-Sleep : A parameter cannot be found that matches parameter name 'ms'.
At C:\Users\MY.LOCAL\Desktop\Admin\MY\LeadsDB\Combine\PropertyFile\WF\2-MY.ps1:36 char:15
+   Start-Sleep -ms 250
+               ~~~
    + CategoryInfo          : InvalidArgument: (:) [Start-Sleep], ParameterBindingException
    + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell.Commands.StartSleepCommand

use

Start-Sleep -millisecond 250

Select allOpen in new window

or

Start-Sleep -m 250

Select allOpen in new window

okay, it is done, but I still getting the same error. I added "Start-Sleep -Milliseconds 250" in 4 different places

# del existing files
$outfile = "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\OUTPUT\MYcombinePropertyFile.csv"
if(Test-path $outfile){
    Remove-Item $outfile -Confirm:$false
}

$outfileXLSX = "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\OUTPUT\MYcombinePropertyFile.xlsx"
if(Test-path $outfileXLSX){
    Remove-Item $outfileXLSX -Confirm:$false
}


# get 3 different file types and convert them into CSV before combining
$filesTXT = Get-ChildItem "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\Source" -Filter *.txt
$filesXLSX = Get-ChildItem "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\Source" -Filter *.xlsx
$filesXLS = Get-ChildItem "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\Source" -Filter *.xls


# convert TXT into CSV
foreach($file in ($filesTXT)) {
    #Changing of extension
    $NewName = $file.BaseName + '.csv'
    $file | Rename-Item -NewName $NewName
Start-Sleep -Milliseconds 250

}

# convert XLSX into CSV
foreach($file in ($filesXLSX)) {

  $newname = $file.FullName -replace '\.xlsx$', '.csv'
  $ExcelWB = new-object -comobject excel.application
  $Workbook = $ExcelWB.Workbooks.Open($file.FullName) 
  $Workbook.SaveAs($newname,6)
  $Workbook.Close($false)
  $ExcelWB.quit()
Start-Sleep -Milliseconds 250

}

# convert XLS into CSV
foreach($file in ($filesXLS)) {

  $newname = $file.FullName -replace '\.xls$', '.csv'
  $ExcelWB = new-object -comobject excel.application
  $Workbook = $ExcelWB.Workbooks.Open($file.FullName) 
  $Workbook.SaveAs($newname,6)
  $Workbook.Close($false)
  $ExcelWB.quit()
Start-Sleep -Milliseconds 250

}


$files = Get-ChildItem "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\Source" -Filter *.csv
# Check first line if "Trans ID" exists, skip lines
foreach ($f in $files){
    $firstLine = Get-Content -Path $f.FullName -TotalCount 1
    if ($firstLine -like "Transaction ID,*") {
        (Get-Content $f.FullName | Select-Object -Skip 1 | Add-Content $outfile)
    }elseif ($firstLine -like "1,2,3*") {
        (Get-Content $f.FullName | Select-Object -Skip 2 | Add-Content $outfile)
    }elseif ($firstLine -like "Tran ID*") {
        (Get-Content $f.FullName | Select-Object -Skip 1 | Add-Content $outfile)
    }else{
        (Get-Content $f.FullName | Add-Content $outfile)
    }
Start-Sleep -Milliseconds 250

}




# load into Excel
# convert CSV to XLSX
$excel = New-Object -ComObject Excel.Application 
$excel.Visible = $true
$excel.Workbooks.Open($outfile).SaveAs("$outfileXLSX",51)
$excel.Quit()

#explorer.exe "/Select,C:\Users\MY.LOCAL\Desktop\database\Combine\combineLoanFile.xlsx"

explorer.exe "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\OUTPUT"

Select allOpen in new window

Going back to your error you pointed out https://www.experts-exchange.com/questions/29203293/excel-cannot-complete-this-task-with-available-resources.html#a43212496 

The remote procedure call failed. (Exception from HRESULT: 0x800706BE)
At C:\Users\MY.LOCAL\Desktop\Admin\MY\LeadsDB\Combine\PropertyFile\WF\2-test.ps1:73 char:1
+ $excel.Workbooks.Open($outfile).SaveAs("$outfileXLSX",51)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException
 
Exception calling "Quit" with "0" argument(s): "The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)"
At C:\Users\MY.LOCAL\Desktop\Admin\MY\LeadsDB\Combine\PropertyFile\WF\2-test.ps1:74 char:1
+ $excel.Quit()
+ ~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : COMException 

If that is pointing to Line 73 and the code you posted here https://www.experts-exchange.com/questions/29203293/excel-cannot-complete-this-task-with-available-resources.html#a43212467  matches exactly as what you are running, then the line throwing the error is, $excel.Workbooks.Open($outfile).SaveAs("$outfileXLSX",51) 

# load into Excel
# convert CSV to XLSX
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$excel.Workbooks.Open($outfile).SaveAs("$outfileXLSX",51)
$excel.Quit()

Try using the sleep command just prior to, "$excel = New-Object -ComObject Excel.Application"

ok, now try to disable excel plugins...
and run the code...
one of your plugins may create issue...

Hain, slow down. Try one thing at a time.

The error is pointing to a line you did not address.  Let's wait for the results from https://www.experts-exchange.com/questions/29203293/excel-cannot-complete-this-task-with-available-resources.html#a43212572  before making any more comments.  Otherwise, it is going to be confusing.

I added the time pause here and still getting the same error..thanks

Start-Sleep -Milliseconds 250

# load into Excel
# convert CSV to XLSX
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$excel.Workbooks.Open($outfile).SaveAs("$outfileXLSX",51)
$excel.Quit()

if sleep is not the issue, and error is always on this line, opening and save as
then you can try

$excel.Visible = $false

+  disable some addons from excel... if there is

If that does not work, I would go back to the idea of writing to a log.  The error shows,

$excel.Workbooks.Open($outfile).SaveAs("$outfileXLSX",51) 

At least for each of these, write to your log what the $outfile is before you run that group of code. Then you can look at what specific file is throwing the error and compare it to the files that are working. It could very well be bad data or maybe there are multiple worksheets vs just one and you have to account for that etc.

It did not work. I went back to run the files last month (852 files), it went well. There are 859 files. Sould be okay.

I am guessing it could be some files, but there is no error when converting or combining. thanks

It did not work. I went back to run the files last month (852 files), it went well. There are 859 files. Sould be okay. 

from those files that worked, copy paste 100 files, make it 952 and run again...
just to make sure, the issue is not the number of files/size but just some files are somehow corrupted!

okay, I copy and paste to make it to 1003 files and it went well...

okay, I copy and paste to make it to 1003 files and it went well... 

and those of 1003 files > original 859 files, by size and number of rows?

if yes, then, can you open the $outfile file with excel and save as $outfileXLSX
ie, can you do it manually?

after it crashes, open the csv and save as xlsx to see if any problem here...

is there any reason for " around file name here?

$excel.Workbooks.Open($outfile).SaveAs("$outfileXLSX",51)

>>>

$excel.Workbooks.Open($outfile).SaveAs($outfileXLSX,51)

does this make any difference?

$excel.Workbooks.Open($outfile).SaveAs($outfileXLSX,51)

Select allOpen in new window

>>>

$Workbook = $excel.Workbooks.Open($outfile)
$Workbook.SaveAs($outfileXLSX, 51)

Select allOpen in new window

and those of 1003 files > original 859 files, by size and number of rows?

if yes, then, can you open the $outfile file with excel and save as $outfileXLSX
ie, can you do it manually?

after it crashes, open the csv and save as xlsx to see if any problem here... 

>>> It shows the same error when I open CSV file...

is there any reason for " around file name here?

$excel.Workbooks.Open($outfile).SaveAs("$outfileXLSX",51)

>>>

$excel.Workbooks.Open($outfile).SaveAs($outfileXLSX,51) 


>> I thought that was the way to do it..thanks

does this make any difference? 

same error......very strange

I think it could be because one or more files are corrupted but it was still converted. After combining, it could not be opened. I am not sure how to catch this error or exception. thanks

when you get that error, you should have the combined csv file
did you open it with excel and do a save as?
you should get same error as powershell

one more thing...
can you add these 2 lines to your loops...
before quitting, release the comobject...

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null
$ExcelWB.quit()

Select allOpen in new window

+ why do you create 900 excel application?
one should be enough I guess...

try

# del existing files
$outfile = "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\OUTPUT\MYcombinePropertyFile.csv"
if(Test-path $outfile){
    Remove-Item $outfile -Confirm:$false
}

$outfileXLSX = "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\OUTPUT\MYcombinePropertyFile.xlsx"
if(Test-path $outfileXLSX){
    Remove-Item $outfileXLSX -Confirm:$false
}

# get 3 different file types and convert them into CSV before combining
$filesTXT  = Get-ChildItem "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\Source" -Filter *.txt
$filesXLSX = Get-ChildItem "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\Source" -Filter *.xlsx
$filesXLS  = Get-ChildItem "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\Source" -Filter *.xls

# convert TXT into CSV
foreach($file in ($filesTXT)) {
    #Changing of extension
    $NewName = $file.BaseName + '.csv'
    $file | Rename-Item -NewName $NewName
    #Start-Sleep -Milliseconds 250
}

$Excel = new-object -comobject Excel.Application

# convert XLSX into CSV
foreach($file in ($filesXLSX)) {
    $newname = $file.FullName -replace '\.xlsx$', '.csv'
    #$Excel = new-object -comobject Excel.Application
    $Workbook = $Excel.Workbooks.Open($file.FullName) 
    $Workbook.SaveAs($newname,6)
    $Workbook.Close($false)
    #$Excel.quit()
    #Start-Sleep -Milliseconds 250
}

# convert XLS into CSV
foreach($file in ($filesXLS)) {
    $newname = $file.FullName -replace '\.xls$', '.csv'
    #$Excel = new-object -comobject Excel.Application
    $Workbook = $Excel.Workbooks.Open($file.FullName)
    $Workbook.SaveAs($newname,6)
    $Workbook.Close($false)
    #$Excel.quit()
    #Start-Sleep -Milliseconds 250
}

$files = Get-ChildItem "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\Source" -Filter *.csv
# Check first line if "Trans ID" exists, skip lines
foreach ($f in $files){
    $firstLine = Get-Content -Path $f.FullName -TotalCount 1
    if ($firstLine -like "Transaction ID,*") {
        (Get-Content $f.FullName | Select-Object -Skip 1 | Add-Content $outfile)
    }elseif ($firstLine -like "1,2,3*") {
        (Get-Content $f.FullName | Select-Object -Skip 2 | Add-Content $outfile)
    }elseif ($firstLine -like "Tran ID*") {
        (Get-Content $f.FullName | Select-Object -Skip 1 | Add-Content $outfile)
    }else{
        (Get-Content $f.FullName | Add-Content $outfile)
    }
    #Start-Sleep -Milliseconds 250
}

# load into Excel
# convert CSV to XLSX
# $Excel = New-Object -ComObject Excel.Application 
$Excel.Visible = $true
# $excel.Workbooks.Open($outfile).SaveAs("$outfileXLSX",51)
$Workbook = $excel.Workbooks.Open($outfile)
$Workbook.SaveAs($outfileXLSX, 51)

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null

$Excel.Quit()

#explorer.exe "/Select,C:\Users\MY.LOCAL\Desktop\database\Combine\combineLoanFile.xlsx"

explorer.exe "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\OUTPUT"

Select allOpen in new window

I am not sure how to catch this error or exception. thanks

I have previously suggested a couple of times to create a log and include the line number the log is being written from and the file that is about to be open.  I typically add a counter too so my log would look like

1   35   file_3005.xls
2   41   file_2638.xls
3   75    file_5679.xls
:
:
900   35   file_2135.xls
901   41   file_4901.xls
902   75    file_1621.xls

The last line in the log is going to be the file that is causing the issue.  Now you can look up that file and see what is different.

I am still getting the same error.
In PS, this is the error

Exception calling "Quit" with "0" argument(s): "COM object that has been separated from its underlying RCW cannot be used."
At C:\Users\my.LOCAL\Desktop\Admin\my\LeadsDB\Combine\PropertyFile\my\2-WFcombinePropertyFile.ps1:81 char:1
+ $excel.Quit()
+ ~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidComObjectException

This is what I did, I added a counter. It went all through all the files (849 files)

$counter = 0
$files = Get-ChildItem "C:\Users\my.LOCAL\Desktop\database\Combine\PropertyFile\my\Source" -Filter *.csv
# Check first line if "Trans ID" exists, skip lines
foreach ($f in $files){
    $firstLine = Get-Content -Path $f.FullName -TotalCount 1
    if ($firstLine -like "Transaction ID,*") {
        (Get-Content $f.FullName | Select-Object -Skip 1 | Add-Content $outfile)
    }elseif ($firstLine -like "1,2,3*") {
        (Get-Content $f.FullName | Select-Object -Skip 2 | Add-Content $outfile)
    }elseif ($firstLine -like "Tran ID*") {
        (Get-Content $f.FullName | Select-Object -Skip 1 | Add-Content $outfile)
    }else{
        (Get-Content $f.FullName | Add-Content $outfile)
    }
    Start-Sleep -Milliseconds 250
    $counter++
    echo "XLS File opened happened: $counter $f.FullName"
}

Select allOpen in new window

comment out marshalls, or move quit before marshalls

$Excel.Quit() 
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null 

Select allOpen in new window

I am still getting the same result.

This is very strange. There are about 900 files need to be combined So I combined them with smaller groups to test. When I started to narrow down possible files. (say, 20 files). I combined those 20 files alone, it went through. There is no pattern of which file(s) would cause the error.

I really have no idea now. 

did you test the code that I posted, with only one excel instant?

please post the code that you use last run, and error message please...

this is the error from ps

The remote procedure call failed. (Exception from HRESULT: 0x800706BE)
At C:\Users\MY.LOCAL\Desktop\Admin\MY\LeadsDB\Combine\PropertyFile\MY\2-MYcombinePropertyFile.ps1:78 char:1
+ $Workbook = $excel.Workbooks.Open($outfile)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException
 
COM object that has been separated from its underlying RCW cannot be used.
At C:\Users\MY.LOCAL\Desktop\Admin\MY\LeadsDB\Combine\PropertyFile\MY\2-MYcombinePropertyFile.ps1:79 char:1
+ $Workbook.SaveAs($outfileXLSX, 51)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], InvalidComObjectException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.InvalidComObjectException
 
Exception calling "Quit" with "0" argument(s): "The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)"
At C:\Users\MY.LOCAL\Desktop\Admin\MY\LeadsDB\Combine\PropertyFile\MY\2-MYcombinePropertyFile.ps1:80 char:1
+ $excel.Quit()
+ ~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

Select allOpen in new window

this is the code I use.

# del existing files
$outfile = "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\OUTPUT\MYcombinePropertyFile.csv"
if(Test-path $outfile){
    Remove-Item $outfile -Confirm:$false
}

$outfileXLSX = "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\OUTPUT\MYcombinePropertyFile.xlsx"
if(Test-path $outfileXLSX){
    Remove-Item $outfileXLSX -Confirm:$false
}


# get 3 different file types and convert them into CSV before combining
$filesTXT = Get-ChildItem "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\Source" -Filter *.txt
$filesXLSX = Get-ChildItem "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\Source" -Filter *.xlsx
$filesXLS = Get-ChildItem "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\Source" -Filter *.xls


# convert TXT into CSV
foreach($file in ($filesTXT)) {
    #Changing of extension
    $NewName = $file.BaseName + '.csv'
    $file | Rename-Item -NewName $NewName
    #Start-Sleep -Milliseconds 250
}

$Excel = new-object -comobject Excel.Application

# convert XLSX into CSV
foreach($file in ($filesXLSX)) {
    $newname = $file.FullName -replace '\.xlsx$', '.csv'
    #$Excel = new-object -comobject Excel.Application
    $Workbook = $Excel.Workbooks.Open($file.FullName) 
    $Workbook.SaveAs($newname,6)
    $Workbook.Close($false)
    #$Excel.quit()
    #Start-Sleep -Milliseconds 250
}

# convert XLS into CSV
foreach($file in ($filesXLS)) {
    $newname = $file.FullName -replace '\.xls$', '.csv'
    #$Excel = new-object -comobject Excel.Application
    $Workbook = $Excel.Workbooks.Open($file.FullName)
    $Workbook.SaveAs($newname,6)
    $Workbook.Close($false)
    #$Excel.quit()
    #Start-Sleep -Milliseconds 250
}

$counter = 0
$files = Get-ChildItem "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\Source" -Filter *.csv
# Check first line if "Trans ID" exists, skip lines
foreach ($f in $files){
    $firstLine = Get-Content -Path $f.FullName -TotalCount 1
    if ($firstLine -like "Transaction ID,*") {
        (Get-Content $f.FullName | Select-Object -Skip 1 | Add-Content $outfile)
    }elseif ($firstLine -like "1,2,3*") {
        (Get-Content $f.FullName | Select-Object -Skip 2 | Add-Content $outfile)
    }elseif ($firstLine -like "Tran ID*") {
        (Get-Content $f.FullName | Select-Object -Skip 1 | Add-Content $outfile)
    }else{
        (Get-Content $f.FullName | Add-Content $outfile)
    }
    #Start-Sleep -Milliseconds 250
    $counter++
    echo "XLS File opened happened: $counter $f.FullName"
}


#Start-Sleep -Milliseconds 250

# load into Excel
# convert CSV to XLSX
# $Excel = New-Object -ComObject Excel.Application 
$Excel.Visible = $true
# $excel.Workbooks.Open($outfile).SaveAs("$outfileXLSX",51)
$Workbook = $excel.Workbooks.Open($outfile)
$Workbook.SaveAs($outfileXLSX, 51)
$excel.Quit()
  [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Workbook) | Out-Null
  [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null


#explorer.exe "/Select,C:\Users\MY.LOCAL\Desktop\database\Combine\combineLoanFile.xlsx"

explorer.exe "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\OUTPUT"

Select allOpen in new window

this is the error from ps

That error is identical to what was happening to start with and in the same location at $Workbook = $excel.Workbooks.Open($outfile) 

. Did you create a log to see which specific file is throwing the error?  or at least output the $outfile to a log or screen so you can see which file is causing the error?

I print out filename here, is this what you meant? thanks

foreach ($f in $files){
    $firstLine = Get-Content -Path $f.FullName -TotalCount 1
    if ($firstLine -like "Transaction ID,*") {
        (Get-Content $f.FullName | Select-Object -Skip 1 | Add-Content $outfile)
    }elseif ($firstLine -like "1,2,3*") {
        (Get-Content $f.FullName | Select-Object -Skip 2 | Add-Content $outfile)
    }elseif ($firstLine -like "Tran ID*") {
        (Get-Content $f.FullName | Select-Object -Skip 1 | Add-Content $outfile)
    }else{
        (Get-Content $f.FullName | Add-Content $outfile)
    }
    #Start-Sleep -Milliseconds 250
    $counter++
    echo "XLS File opened happened: $counter $f"
}

Select allOpen in new window

So which -Path $f.FullName caused the error?

That is the thing, there is no error, the count is printed from 1 to 849.(There are 849 files in that folder)

ok, there is no error...
up to line 79, all files are processed...
and you cannot open the csv file...

now, go the folder, and open the csv file with excel

$outfile = "C:\Users\MY.LOCAL\Desktop\database\Combine\PropertyFile\MY\OUTPUT\MYcombinePropertyFile.csv"

can you open it?
if you cannot open,
install notepadd ++, or open it wordpad
how many lines do you have here?
whats the size of outfile?

okay, I can open the final CSV file. But before opening, it shows "

excel cannot complete this task with available resources" then open.

I only have 30870 rows. The file size is 38MB which is about right. But it should have more than that...

When I use notepad++, I have 116136 rows

okay, I think I have located the issue. There is a file that is causing the problem. If I remove this file, all files will be processed without an issue.

If I process this file alone, it is good as well.

If I start with this file and start to put more files to the folder, at first, all files can be processed (alone with this file.)

When I keep adding more files to the folder, the error shows.

There is no pattern to it. The only difference between this file and others is that the header is different.

thanks

There is no pattern to it. The only difference between this file and others is that the header is different. 

This is why I have been trying to suggest finding a bad file to start with and it was never an issue with the code other than not using error trapping such as try/catch.

Could be some bad characters in the header?   Delete the row, try adding a new row above or below and hand type in the column names. Then delete the original row, save and try again.

Also, which oringal filetype was that? I lost track. txt, xls, xlsx?  Is that a pattern?

 I can open the final CSV file. But before opening, it shows

I would want to look at the original file. That is more likely where the issue starts. 

okay, I found out the issue. It has nothing to do with the headers. I deleted the headers and the error is still there.
It went all the way back to your early post @Scott Fell regarding the limitation (I think) The column is only up to CR in this file. But after converting it to CSV, it created commas as there were values in the fields and that throws the Excel off.

In Excel, I highlight everything after CW and delete them (all the way to column Axx).After converting to CSV, still. If I delete all , directly in CSV then everything is fine.

I have attached a file without headers. Also, is this the right way to use try/catch? thanks123.xlsx

try
{
    foreach ($f in $files){
        $firstLine = Get-Content -Path $f.FullName -TotalCount 1
        $secondLine = Get-Content -Path $f.FullName -TotalCount 2
        if ($secondLine -like "1,2,3*") {
            (Get-Content $f.FullName | Select-Object -Skip 3 | Add-Content $outfile)
            echo "1 File opened happened: $counter $f"
        }elseif ($firstLine -like "Transaction ID,*") {
            (Get-Content $f.FullName | Select-Object -Skip 1 | Add-Content $outfile)
            echo "2 File opened happened: $counter $f"
        }elseif ($firstLine -like "1,2,3*") {
            (Get-Content $f.FullName | Select-Object -Skip 2 | Add-Content $outfile)
            echo "3 File opened happened: $counter $f"
        }elseif ($firstLine -like "Tran ID*") {
            (Get-Content $f.FullName | Select-Object -Skip 3 | Add-Content $outfile)
            echo "4 File opened happened: $counter $f"
        }else{
            (Get-Content $f.FullName | Add-Content $outfile)
            echo "5 File opened happened: $counter $f"
        }
        #Start-Sleep -Milliseconds 250
        $counter++
        #echo "CSV File opened happened: $counter $f"
    }
}
catch
{
    echo $_.Exception.Message
}

Select allOpen in new window

This is where it is more important to look at the original file.

If the original is a text file, perhaps the issue is a carriage return is missing and that could be throwing things off?

I am out of time for the day and will be out for a day or two. 

On the Try/Catch, it is for error trapping https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_try_catch_finally?view=powershell-7.1

Probably best for now to just figure out the issue and get that resolved so you can move on.

ok, there is no issue with code
no issue with files
problem is too much data/cell and your excel/pc cannot handle it...

now, did you try to disable plugins?
what do you have here?

yes, I did.....

I was wondering if you could help me with removing everything after last column.....thanks

foreach($file in ($filesXLSX)) {


$Excel.Visible = $True
$objWorkbook = $Excel.Workbooks.Open($file.FullName)
$objWorksheet = $Excel.Worksheets.Item(1)


$objRange = $objWorksheet.UsedRange
$Lastrow = $objRange.SpecialCells(11).row
$LastColumn = $objRange.SpecialCells(11).column
write-host "Lastrow:", $Lastrow, " Last Column:" $LastColumn
}

Select allOpen in new window

I was wondering if you could help me with removing everything after last column 

I could not get what you mean here...
last column is last column, and after that there is no data
what are you trying to remove?

any sample excel may help, or screenshot

sheet1: sample data
sheet2: result of "removing everything after last column"

please have a look at the file attached. this is the file that causes the problem. 123.xlsx

the last column is CR, please SAVEAS CSV file, and open it with notepad. You will see a lot of ,,,,,,,,,,,,
I want to remove those empty columns before cobverting into CSV  thanks

use this

Sub removeEmptyCells()
    Cells(1, 16384).Select
    
    Selection.End(xlToLeft).Select
    ActiveCell.Offset(0, 1).Select
    
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.EntireColumn.Delete
End Sub

Select allOpen in new window

is it possible to do this in powershell? I want to combine with this code  thanks

# convert XLSX into CSV
foreach($file in ($filesXLSX)) {
    $newname = $file.FullName -replace '\.xlsx$', '.csv'
    #$Excel = new-object -comobject Excel.Application
    $Workbook = $Excel.Workbooks.Open($file.FullName) 
    $Workbook.SaveAs($newname,6)
    $Workbook.Close($false)
    #$Excel.quit()
    #Start-Sleep -Milliseconds 250
}

Select allOpen in new window

not sure but should be...

$Excel.Workbooks.ActiveSheet.Cells(1, 16384).Select
$Excel.Workbooks.ActiveSheet.Selection.End(xlToLeft).Select
$Excel.Workbooks.ActiveSheet.ActiveCell.Offset(0, 1).Select
$Excel.Workbooks.ActiveSheet.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
$Excel.Workbooks.ActiveSheet.Selection.EntireColumn.Delete

Select allOpen in new window

 

I guess I manged to make it

$xlToLeft = [Microsoft.Office.Interop.Excel.XlDirection]::xlToLeft
$xlLastCell = [Microsoft.Office.Interop.Excel.Constants]::xlLastCell

$Excel.ActiveSheet.Cells(1, 16384).Select()
$Excel.Selection.End($xlToLeft).select()
$Excel.ActiveCell.Offset(0, 1).Select()
$Excel.Range($Excel.Selection, $Excel.ActiveCell.SpecialCells($xlLastCell)).Select()
$Excel.ActiveSheet.Selection.EntireColumn.Delete()

Select allOpen in new window

sorry, I guess my modification does not work.....thanks

# convert XLSX into CSV
foreach($file in ($filesXLSX)) {
    $newname = $file.FullName -replace '\.xlsx$', '.csv'
    #$Excel = new-object -comobject Excel.Application
    $Workbook = $Excel.Workbooks.Open($file.FullName) 


$xlToLeft = [Microsoft.Office.Interop.Excel.XlDirection]::xlToLeft
$xlLastCell = [Microsoft.Office.Interop.Excel.Constants]::xlLastCell

$Workbook.ActiveSheet.Cells(1, 16384).Select()
$Workbook.Selection.End($xlToLeft).select()
$Workbook.ActiveCell.Offset(0, 1).Select()
$Workbook.Range($Excel.Selection, $Excel.ActiveCell.SpecialCells($xlLastCell)).Select()
$Workbook.ActiveSheet.Selection.EntireColumn.Delete


    $Workbook.SaveAs($newname,6)
    $Workbook.Close($false)
    #$Excel.quit()
    #Start-Sleep -Milliseconds 250







}

Select allOpen in new window

check my last post...

okay, I think it is working now......

thank you so very much............as you can see in the file I attached, it creates many ",,,,,,,," and throws the Excel off. I think I was lucky this time because I was able to find the file that caused it. If it happens again, I am sure I will not be able to locate it with almost 900 files.

My question is that is there a way to capture this in the code?thanks

I will close this question as it is too many posts. Please let me know and I will open a new question. thanks again 

My question is that is there a way to capture this in the code?

actually I am not sure why those cells are not treated as empty
and excel thinks they have empty value
and creates ",,,,,"
maybe they have special formulas, or formatting...
not sure, did not check in detail...

or, instead of doing that,
we can just say "remove all formatting" and save as csv...
maybe this logic is better...

and we already captured those empty columns this way...
we select all then delete...
then excel see them unused and does not add them to csv...

what else you want to do on excel file?

I think it does the trick for now. After the finished product, I send it to another guy to do the analasis. At this point, I am very happy with the outcome. thank you very much.