Merge xslx files

Tagged: , ,

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of H Man H Man 1 year, 1 month ago.

  • Author
  • #29813
    Profile photo of Dmitry P
    Dmitry P

    Hi! I got a script for merge few xslx files in one, but I need much more abilities from that script, such as column width and worksheet name. I have tried to make it by myself, but I have no success. Please help
    The code:
    $Files = GCI 'D:\Docs\Excel\Merge' | ?{$_.Extension -Match "xlsx?"} | select -ExpandProperty FullName

    #Launch Excel, and make it do as its told (supress confirmations)
    $Excel = New-Object -ComObject Excel.Application
    $Excel.Visible = $True
    $Excel.DisplayAlerts = $False

    #Open up a new workbook
    $Dest = $Excel.Workbooks.Add()

    #Loop through files, opening each, selecting the Used range, and only grabbing the first 6 columns of it. Then find next available row on the destination worksheet and paste the data
    ForEach($File in $Files[0..4]){
    $Source = $Excel.Workbooks.Open($File,$true,$true)
    If(($Dest.ActiveSheet.UsedRange.Count -eq 1) -and ([String]::IsNullOrEmpty($Dest.ActiveSheet.Range("A1").Value2))){ #If there is only 1 used cell and it is blank select A1
    [void]$source.ActiveSheet.Range("A1","F$(($Source.ActiveSheet.UsedRange.Rows|Select -Last 1).Row)").Copy()
    }Else{ #If there is data go to the next empty row and select Column A
    [void]$source.ActiveSheet.Range("A2","F$(($Source.ActiveSheet.UsedRange.Rows|Select -Last 1).Row)").Copy()
    [void]$Dest.ActiveSheet.Range("A$(($Dest.ActiveSheet.UsedRange.Rows|Select -last 1).row+1)").Select()
    $Dest.SaveAs("D:\Licenses 2015.xlsx",51)

  • #29885
    Profile photo of H Man
    H Man

    check Doug Finke's Excel Module

You must be logged in to reply to this topic.