Merging multiple excel fiels into multiple sheet of an excel

This topic contains 0 replies, has 1 voice, and was last updated by  Forums Archives 5 years, 9 months ago.

  • Author
    Posts
  • #5182

    by naveenjv at 2012-10-09 06:50:35

    Hi,

    I have two two different excel files with one sheet each (with formated columns). How do I merge these two excel files into different sheets of a single excel using powershell

    Please help.

    Thanks,
    Naveen J V

    by JeffH at 2012-10-09 08:33:43

    Is this a one-off task or something you need to do on a repeated basis or for a lot of files? You could use PowerShell to read the files and write data to a new file. I might try running through the process manually and record a VBA macro. You can use the macro as a guide in developing a PowerShell script. You'll need to use the Excel.Application COM object. This is not going to be a simple project.

    by naveenjv at 2012-10-09 21:59:31

    Hi Jeffery Hicks,

    I would like to create a script which works in a general way.
    For example, if I have three excel files, I would like to combine all three files into one excel file with three different sheets.
    I would like to accomplish this using PowerShell.
    Can you please guide?

    Thanks,
    Naveen J V

    by JeffH at 2012-10-10 06:38:49

    See if this gets you started.


    $excel = New-Object -ComObject "Excel.Application"
    $wb = $excel.Workbooks.Add()

    $file = $excel.workbooks.open("C:\work\files.xlsx")
    $file.Sheets.item("files").Select()
    $file.Sheets.item("files").Copy($wb.sheets.item("Sheet1"))
    #don't save any changes to this file
    $excel.DisplayAlerts=$False
    $file.close()

    $excel.Visible=$True

    You might also want to take a look at http://jdhitsolutions.com/blog/2012/05/ ... and-demos/

You must be logged in to reply to this topic.