Excel CSV export only for files that have refresh data

This topic contains 1 reply, has 2 voices, and was last updated by  Don Jones 9 months, 3 weeks ago.

  • Author
    Posts
  • #60832

    Paps Sale
    Participant

    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!

  • #60886

    Don Jones
    Keymaster

    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.

You must be logged in to reply to this topic.