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 ScriptThe script accepts 3 parameters, you should include the path name of the files.
- 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
CaveatsAs 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 2008If you cannot get vb scripts to run on your Windows 2008 server, please see this page.
ConclusionI hope you find this tip (and script file) useful. If you have questions or suggestions post them as a comment below.
Download the script