2

Can somebody please help me to resolve this. This is my requirement:

I have few excel files (It will vary) in a folder D:\Script\Test

The name of the files are ExcelA, ExcelB, ExcelC etc

It has worksheets like this: A1 (for ExcelA), B1(for ExcelB), C1(for ExcelC) etc

Here I want to merge all those different excel sheets to one excel sheets

(Lets say the merge file name is 'Final.xlsx')

Condition: Instead of getting the worksheets as its actual names (A1, B1, C1), I want to get it updated with excel file names.

Meaning : Final.xlsx should have merged 3 worksheets with the names: ExcelA, ExcelB, ExcelC

AllenBooTung
  • 1,299
  • 1
  • 10
  • 20
Vimal VV
  • 21
  • 1
  • 2
  • what you describe is doable _in excel_. you can use macros to load external files - CSV or native excel files - into individual sheets. then summarize/present/process them in another sheet. i don't see any need for PoSh. ///// if you _must_ use powershell, then take a look at the `ImportExcel` module. – Lee_Dailey Jun 20 '20 at 14:46
  • Thank you for your reply. I am just a beginner in scripting. The only thing I do is search things in google and copy paste and check. If possible then can you please give the script like how to do that ? – Vimal VV Jun 20 '20 at 19:08
  • i don't have access to Excel - i use LibreOffice. so i can't show you how to set up the Excel macros to load the external files. nor can i show you how to use the Powershell ImportExcel module to work with excel. nor can i show you how to use COM objects with powershell. you may want to change your tags to show MSOffice and VBA [VisualBasicForApplications]. – Lee_Dailey Jun 20 '20 at 19:24

1 Answers1

0
  1. Run powershell / PowerShell ISE as an administrator
  2. Run the following commands,
    • Install-Module ImportExcel (and follow the prompts)
    • Import-Module ImportExcel
  3. Create a script (e.g. MergeExcelWorkbooks.ps1)
    • The below will help you merge 1 sheet each from multiple workbooks and, per requirement, the merged workbook (Final.xlsx) sheet names are workbook names of the source excel files.

Script:

$sourceFolderPath = "D:\Script\Test"

$OutputFilePath = "D:\Script\Test\Final.xlsx"

$XLfiles = Get-ChildItem $sourceFolderPath -Filter *.xlsx

foreach ($XLfile in $XLfiles) {

    <# Hints,
    - If there is only 1 sheet or if you want to import data from the 1st sheet (ordinal, i.e index 0) in the Excel file the below would work
    - Else please use the 'WorkSheetName' parameter to specify the sheet name to import from
    - Use the NoHeader switch of Import-Excel if the source Excel sheets do not contain a header; HeaderName parameter can be used in combination with NoHeader to specify a custom header name
    #>
    Import-Excel $XLfile.FullName | Export-Excel $OutputFilePath -WorksheetName $XLfile.BaseName    

}

In the above script,

  1. $XLfile.FullName returns the full filepath of the source $XLfile
  2. $XLfile.BaseName returns the name of the current excel file (without the extension)

Please try it out and let me know in the comments if it worked for you or if you have a different scenario.

EDIT In order to import from a specific sheet use the -WorkSheetName parameter of the Import-Excel command like below,

Import-Excel $XLfile.FullName -WorksheetName 'Snapshots'| Export-Excel $OutputFilePath -WorksheetName $XLfile.BaseName
Karthick Ganesan
  • 237
  • 1
  • 5
  • @Vimal - Can you please let us know the error message? When you post your error message, you can enclose it within the backtick (``) special character so that SO will `highlight` it here – Karthick Ganesan Jun 22 '20 at 15:18
  • Thank you Karthick for your script. But I am getting error. Opened the powershell ISE as an Administrator and ran that install-module: I clicked Yes to All for the next prompt: – Vimal VV Jun 22 '20 at 15:23
  • You can instead post a link to the screenshot of the error message. – Karthick Ganesan Jun 22 '20 at 15:28
  • This is the error message I am getting: https://snipboard.io/nPlJgv.jpg – Vimal VV Jun 22 '20 at 15:34
  • Okay, that error didn't happen for me. What version of PowerShell are you using? The variable `$PSVersionTable` can tell you that. Can you try [manual download ImportExcel 7.1.0](https://www.powershellgallery.com/packages/ImportExcel/7.1.0)? Go to 'Manual Download' under Installation Options. Once downloaded, try to [install the module](https://docs.microsoft.com/en-us/powershell/scripting/gallery/how-to/working-with-packages/manual-download?view=powershell-7#installing-powershell-modules-from-a-nuget-package). – Karthick Ganesan Jun 22 '20 at 15:41
  • PSVersion 5.1.17134.858 – Vimal VV Jun 22 '20 at 15:45
  • Can you please chat me [here](https://chat.stackexchange.com/rooms/109692/my-se-room1)? – Karthick Ganesan Jun 22 '20 at 16:10
  • @Vimal - Just pinged – Karthick Ganesan Jun 22 '20 at 17:02
  • Karthick Ganesan, I was able to run that script from another computer. I guess I dont have enough privileges in my offc computer. But I was able to run that script successfully from my personal computer. Thank you very much for your nice script. Can you please help me one more thing: In your script you mentioned it is collecting the data of first work sheet. What I want to collect the data of my 4th worksheet (Its name is snapshots) ? I tried to modify the script with worksheet but I dont think what I am doing to correct. Can you please help me ? – Vimal VV Jul 08 '20 at 18:28
  • Please try the command line I've updated at the end of my post. – Karthick Ganesan Jul 09 '20 at 14:52
  • Wonderful!! Thank you very much for your great help. Now I got what I wanted. You are just great!! @Karthick! – Vimal VV Jul 10 '20 at 16:27
  • Thanks Vimal! Can you please consider marking this post as "answer" by marking the tick mark beside? This will give some credit points to both you & me and will also help future seekers of a solution to a similar problem. – Karthick Ganesan Jul 11 '20 at 17:52