VBScript to Combine Multiple XLS Files into a Single File

Posted by J. Kinzer
Labels:

Here's a companion tip to the "VBScript to convert CSV to XLS" post.

This script will combine two Excels files into one workbook. The files will be treated as separate worksheets inside the workbook. The worksheet names will be the same as the file names.

You may find this useful when an Inforamtica process outputs multiple flat files and you have converted them into native Excel format but only wish to present one file to the end user (or for archiving or reporting purposes).

Running The Script
The script accepts 3 parameters, you should include the path name of the files.

    Paremeters
  • File number 1 to combine
  • File number 2 to combine
  • Output name of combined file
Here is how it would look to call this script from an Informatica session:

combineExcelFiles.vbs C:\Folder\File1.xlsx C:\Folder\File2.xlsx C:\Folder\CombinedFile.xlsx
If you need to combine more than two files, simply rerun the script multiple times, using the Output file from the first run of the script as the File 1 of the second run, you can use the same Output file name for each subsequent run.

Some suggestions for improving the script
  • add code to remove any blank (default) worksheets
  • change the code that renames the worksheets to exclude the file extension as part of the name
  • change the code that renames the worksheets to accept parameters so you could have more specify meaningful names

Caveats
As with executing any VBScript to create XLS files, be aware that instances of the Excel object can stay resident in memory, especially if the script fails.  I have not noticed this happening on my company's current landscape (Windows em64-bit).  If you do notice this behavior on your system you may need to run an additional script to find and kill the rouge instance.

 

Windows 2008
If you cannot get vb scripts to run on your Windows 2008 server, please see this page.


Conclusion
I hope you find this tip (and script file) useful. If you have questions or suggestions post them as a comment below.

Download the script

Thank you for your support
 or  PayPal





IMPORTANT,  YOU are responsible for backing up your systems before trying or implementing any suggestions from this blog. I do not guarantee 100% accuracy of any code examples. I do not presume to know your system environment(s) or Security requirements; all code examples from this blog should be thoroughly tested before any attempted use on a production system.

| Share

6 Response to "VBScript to Combine Multiple XLS Files into a Single File"

  1. Anonymous 7/11/11, 12:38 AM
    while i try to open the script.. it is saying "Script out of Range error"
    Code:800A0009
  2. J. Kinzer 7/11/11, 9:24 PM
    Please download the script again. This version worked for me. If you still get an error please email me the vbs file and the syntax you are using to run the script.

    Thank you
  3. Anonymous 11/9/13, 2:20 AM
    THANK YOU!!! SIMPLE, ELEGANT AND AWESOME SCRIPT!!! SAVE ME WEEKS...
  4. Jinnesh 2/6/14, 4:03 AM
    cant open/download dis script
  5. Unknown 9/14/18, 2:51 AM
    Is it possible to automate this script? my problem is, that any automation gets done by a service user which is not able to open Excel (only remote desktop users can do that). So if manually click on my file.vbs than it converts multiple csv or xls to a single one. But if I trigger this vbs it does not work.
    Any suggestions are vere much appretiated.
    Thank you
  6. Synkronizer Excel Tool 2/15/19, 4:37 AM
    Thanks for sharing this VBA script for combine multiple excel files. Its really nice and helpful code. But we can also use Sunkronizer Excel Add-in for Combine and Merge Multiple Excel files into one file.

Post a Comment

Subscribe to: Post Comments (Atom)