Author Posts

December 25, 2016 at 10:22 pm

I have a powershell script that goes through all xlsx files within each child folder, refreshes their data and saves a CSV file for each of them (also overwriting the original xlsx files).

I'm currently looking for a way for the powershell script to only create CSV files when the refresh data-source actually exists.
If it doesn't, excel wouldn't create the CSV file (in other words, when the "Excel cannot find the text file to refresh..." warning happens, it would not save a CSV file).

Below is my current code:

$files = Get-ChildItem -Path "E:\Feeds\" *.xlsx -Recurse
$xl = New-Object -ComObject Excel.Application
$xl.DisplayAlerts = $FALSE
foreach ($file in $files) {

    $newFile = "$($file.DirectoryName)\$($file.BaseName).csv"
    $newFile2 = "$($file.DirectoryName)\$($file.BaseName).xlsx"
    $wb = $xl.Workbooks.Open($file.FullName)
    $wb.RefreshAll()
    if (Test-Path -Path $newFile) {
        
        Remove-Item $newFile -Force
    
    }
    $wb.SaveAs("$newFile2",51)
    $wb.SaveAs("$newFile",6)
    $wb.Close($true)
}
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)

If someone could help me with this one, I would be extremely grateful!

December 27, 2016 at 11:07 pm

I'm not certain that the Excel COM object is able to "see" the data source status, to be honest. That's what you'd need to figure out, but it's a VERY old piece of code that hasn't been updated in almost a decade.