VBScript to Combine Multiple XLS Files into a Single File
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
combineExcelFiles.vbs C:\Folder\File1.xlsx C:\Folder\File2.xlsx C:\Folder\CombinedFile.xlsxIf 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 |
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.
Code:800A0009
Thank you
Any suggestions are vere much appretiated.
Thank you