Excel com objects for refreshing data and saving as CSV

This topic contains 10 replies, has 3 voices, and was last updated by Profile photo of Paps Sale Paps Sale 1 year, 5 months ago.

  • Author
    Posts
  • #33053
    Profile photo of Paps Sale
    Paps Sale
    Participant

    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:
    C:\Folder\abc\abc.xlsx
    C:\Folder\def\def.xlsx
    C:\Folder\ghi\ghi.xlsx
    etc.

    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
    ComObjError(false)
    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
    qt.Refresh(0)

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

    ThisWorkbook.Close(0)
    }
    xlApp.Quit()

    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
    Profile photo of Wilfredo Perez
    Wilfredo Perez
    Participant
  • #33064
    Profile photo of Matt Bloomfield
    Matt Bloomfield
    Participant

    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
    Profile photo of Paps Sale
    Paps Sale
    Participant

    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
    Profile photo of Matt Bloomfield
    Matt Bloomfield
    Participant

    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)
        $wb.RefreshAll()
    
        #Save the file as a CSVfile (xlCSV = 6).
        $wb.SaveAs("$newFile",6)
        #Close the workbook, $true will save any changes without a prompt.
        $wb.Close($true)     
    
    }
    
    #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
    Profile photo of Paps Sale
    Paps Sale
    Participant

    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
    Profile photo of Matt Bloomfield
    Matt Bloomfield
    Participant

    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
    Profile photo of Paps Sale
    Paps Sale
    Participant

    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
    Profile photo of Matt Bloomfield
    Matt Bloomfield
    Participant

    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
    Profile photo of Paps Sale
    Paps Sale
    Participant

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

  • #33090
    Profile photo of Paps Sale
    Paps Sale
    Participant

    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.