Excel CSV export only for files that have refresh data

Welcome Forums General PowerShell Q&A Excel CSV export only for files that have refresh data

This topic contains 1 reply, has 2 voices, and was last updated by

 
Keymaster
1 year, 10 months ago.

  • Author
    Posts
  • #60832

    Participant
    Points: 0
    Rank: Member

    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

    Keymaster
    Points: 1,638
    Helping HandTeam Member
    Rank: Community Hero

    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.

The topic ‘Excel CSV export only for files that have refresh data’ is closed to new replies.