Excel com objects for refreshing data and saving as CSV

This topic contains 10 replies, has 3 voices, and was last updated by  Paps Sale 2 years, 7 months ago.

  • Author
  • #33053

    Paps Sale

    Hi everyone.

    I'm currently trying to have a Powershell automation work for the following scenario:
    1. Open an Excel (xlsx) file
    2. Refresh data source
    3. Save as CSV (with same filename as current xlsx file)

    The thing is, I'd like this process to be applied to ALL xmls files included in a certain folder, with each xlsx file being in their own sub-folder, in the following manner:

    Below is a script that I've received, but only works with a third party macro tool (AutoHotkey):

    xlApp := ComObjCreate("Excel.Application") ; Create an instance of Excel
    Loop, Files, D:\Product Feeds\*.xlsx, FR
    ThisWorkbook := xlApp.Workbooks.Open(A_LoopFileFullPath)

    SplitPath, A_LoopFileFullPath,, FileDir,, FileNameNoExt

    for sht, in ThisWorkbook.Sheets
    for qt, in sht.QueryTables

    NewFilePath := FileDir "\" FileNameNoExt ".csv"
    FileDelete, %NewFilePath%
    ThisWorkbook.SaveAs(NewFilePath, xlCSV := 6)


    Could someone kindly advise on how to have this exact same macro work in Powershell?

    Thank you very much and my apologies for being a newbie to Powershell 🙂

  • #33062

    Wilfredo Perez
  • #33064

    Matt Bloomfield

    I agree with Wilfredo, use the Excel module. Something like this is what you're after although I'm not sure I've fully understood your requirement.

    #Get all the Excel files in the folder.
    $excelFiles = Get-ChildItem *.xlsx
    foreach ($file in $excelFiles) {
        #Get the subfolder path from the current directory and the name of the file.
        $subfolder = "$($file.DirectoryName)\$($file.BaseName)"
        #Create the subfolder if it does not exist.
        if (-not (Test-Path $subfolder)) {
            New-Item -Path "$subfolder" -ItemType Directory
        #Process each sheet in the file, converting to a CSV file, and save it in the subfolder.
        ConvertFrom-ExcelSheet -Path $file.FullName -OutputPath $subfolder
  • #33074

    Paps Sale

    Thank you Wilfredo for the link. I will definitely check it soon.

    And Matt, what I actually require is that Powershell goes through all .xlsx files located in subfolders by performing the following for each file:
    1. Refresh data source (in Excel 2010, this is located in "Data" tabs under "Connections" group, and the button is "Refresh All")
    2. After refresh is done, save/convert the file into .csv in the SAME folder

    Hopefully this was more clearly explained now.

  • #33076

    Matt Bloomfield

    OK, you won't be able to refresh using the Excel Module so the COM object is your only option. The following is tested with Excel 2013 and should meet your requirements:

    #Get all the spreadsheets.
    $files = Get-ChildItem *.xlsx -Recurse
    #Create Excel COM object.
    $xl = New-Object -ComObject Excel.Application
    foreach ($file in $files) {
        #Make the filename.
        $newFile = "$($file.DirectoryName)\$($file.BaseName).csv"
        #Open the file and refresh it.
        $wb = $xl.Workbooks.Open($file)
        #Save the file as a CSVfile (xlCSV = 6).
        #Close the workbook, $true will save any changes without a prompt.
    #Close the COM object.
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl) | Out-Null

    Bear in mind that CSV only supports a single sheet. If you have multiple sheets in a workbook then I'd be looking at using a mixture of the COM object and the Excel module.

  • #33081

    Paps Sale

    Thanks Matt!

    Yes, I am aware of the fact that CSV only supports single sheet :).

    I ran the script, but it still asks whether I'd want to replace the CSV or not.
    I think I forgot to tell you that my intention is to also replace already existing CSV files, sorry for not mentioning it before.

  • #33085

    Matt Bloomfield

    My fault, I was running the script in the same folder as my test files. Replace $wb = $xl.Workbooks.Open($file) with $wb = $xl.Workbooks.Open($file.FullName).

  • #33087

    Paps Sale

    OK Matt! I got it working great :).

    I posed one last concern (which I edited in my last post), here it is again:
    "I ran the script, but it still asks whether I'd want to replace the CSV or not.
    I think I forgot to tell you that my intention is to also replace already existing CSV files, sorry for not mentioning it before."

  • #33088

    Matt Bloomfield

    I missed the edit, sorry. You can check and remove a file with the same name using the following code.

        if (Test-Path -Path $newFile) {
            Remove-Item $newFile -Force

    Insert it before the $wb.SaveAs("$newFile",6) line

  • #33089

    Paps Sale

    Just brilliant Matt! Works great. Thank you very much! 🙂

  • #33090

    Paps Sale

    Oh an Matt, I forgot to ask:
    Once the script is done, will it close Excel from the background?
    I would like it to close Excel.

    EDIT: Never mind. I got it :). I just had to remove " | Out-Null" from the very end of the script.

You must be logged in to reply to this topic.