Bulk File GeneratorSelect any range of cells within a spreadsheet and output the data to either:
- A single file utilizing any file name and extension or to multiple copies of the same file by overwriting all files within a selected directory.
- The utility is launched from Excel's Tools menu.
- Includes full detailed instruction with illustrations.
This Bulk File Generator utility allows a user to export a selected range of cell data from within any spreadsheet to either a new file, overwrite an existing file, or overwrite all the files within a selected directory thus retaining all the original file names and extensions (Bulk File Generation mode).
Use this handy quick access utility anytime you need to export a selected range of cell data to a file or to a group of files. The data is written to the file(s) exactly in the same form as it existed in the spreadsheet. If you are utilizing formulas within the cells, the utility will output the calculated results of the cell data just as it appears on the spreadsheet.
What is this Bulk File Generator Utility Program?
Simply stated, this is small but powerful Excel file output add on utility that is launched from Excel's Tools menu. This utility program will allow a user to output spreadsheet data from a selected Range of Cells to either a single file utilizing any file name and extension combination or to overwrite all files within a selected directory.
Files include in the Bulk File Generator utility package:
- FileGen.xls - This is the Bulk File Generator Utility Program for Microsoft Excel. This is the only file you need for Microsoft Excel. The rest of the files are support documentation.
- Test Data.xls - This is a sample Microsoft Excel file containing test data you can export utilizing the Bulk File Generator Utility Program.
- Test Files folder - This Test folder contains 30 files that you can experiment with utilizing the Bulk File Generation mode (overwriting all file in a directory folder). These file are named 01.html thru 30.html and are all identical. Each supplied file contains a text string of "x" character data.
- Readme.txt - Brief description of the program viewable in any ASCII text editor such as Microsoft Notepad.
- Bulk File Generator Version 2.doc - Description of the program and full detailed instructions with image screen shots viewable in Microsoft Word / WordPad.
Instructions.doc - A short version of the Instructions without image screen shots viewable in Microsoft Word / WordPad.
- Instructions.txt - A short version of Instructions without image screen shots viewable in any ASCII text editor such as Microsoft Notepad.
- Register.txt - Contains information on how to purchase and register the program.
- Software License Agreement.doc - Contains the Software License Agreement.
- Software License Agreement.txt - Contains the Software License Agreement.
- Excel 2002 Support folder - If you are using Excel 2002, a part of Office XP, then you must enable Excel 2002's complete set of programming language features in order to run this program. It is very easy to enable these features with the provided registry file. Refer to the "Excel 2002 Support" folder installed with this program.
One of the easiest ways to describe the use for this Bulk File Generator utility is to use an example, Say a web designer has designed a completely new web site that will eventually replace a client's old web site. In this case, the current web site already has established page links indexed at internet search engines and at other places on the web. The problem here is that if the new web site does not use the exact page names as the old site, visitors will get "no page found" errors when referred to the client's web site.
In this case, the web designer should not just delete the old site and replace it with the new site. This could be very detrimental to the client's business. One method a web designer can use to prevent the "no page found" errors, is to include "additional" redirect pages with the exact page names of the old site with the new web site. These particular pages can automatically redirect a visitor to the new web pages when they are loaded in the visitors browser.
In order to redirect a visitor to the new web page when an page with the old file name is loaded in the visitors browser, the web designer will need to replace the contents of "all" the old web page files (*.html or *.htm) with either a redirection procedure or a statement that informs the visitor of the change. The following is an example message statement.
Welcome to XXXXXXXXXX, Inc.
This particular page has been changed. It was part of our OLD web site. The data you are looking for and much more DOES exist on our NEW web site.
Please visit our new page at http://www.xxxxxxxxxx.com/html/new_page.html or search our web site at http://www.xxxxxxxxxx.com/html/search_site.html for your particular needs.Thank you for your time,
Now here is where this Bulk File Generator utility comes in handy. Say, the old web site contains 300 pages that will no longer be needed. The web designer will need to replace the contents of all 300 pages with the above message statement. To perform the replacement manually could take hours or even days, and in many cases, the web designer may have to redo this manual procedure over and over again to get the desired results.
To replace the contents of all 300 files within seconds, the web designer could use the Bulk File Generator utility. All the web designer has to do is put the html code for the message statement in an Excel spreadsheet, activate the Bulk File Generator, select the directory where the 300 files are located, press a button, and within a few seconds, all 300 files will be replaced with the new html code, thus retaining all of the individual file names and extensions. Now the web designer can upload the entire new web site and the additional 300 message statement files to the web server.
Setup and Instructions on How to use the Bulk File Generator Utility
This Bulk File Generator utility has two modes of functionality. The first mode allows the user to either generate a completely new single file or overwrite an existing file with the contents within a selected range of cells within any spreadsheet. The second mode (Bulk File Generation mode) allows the user to overwrite all the files within a selected directory with the contents within a selected range of cells within any spreadsheet thus retaining all of the original individual file names and extensions.
Note: As a preventative measure to avoid accidental file overwrites in the Bulk File Generation mode, the user will be prompted for verification before the bulk file writing process is performed.
First of all, you may want to place the Bulk File Generator file (FileGen.xls) in you Excel's XLStart directory so the it is accessible at all times. It is a very small file and only uses a few kilobytes of memory. If you do not want to place the file in your Excel's XLStart directory, just load the file when you need it.
When the Bulk File Generator is loaded, a new menu item will be added to the Tools menu across the top of Excel. This menu item is labeled "Export Cell Data to File(s)". To use the utility, select this menu item. See the screen shot below.
Setting up your data for exportation:
First, setup the data you want to export to the file(s) in a single spreadsheet column. If you want to use the automatic selection feature, place all your data in a contiguous range of cells. A contiguous range of cells is a range of cells that do not contain blank cells. If you need to have a blank lines in the output file(s) using the automatic selection feature with a contiguous range of cells, then place a single apostrophe (') in the cells where you want the corresponding blank lines to occur.
See the screen shot example below for exporting the web page html code used to generate the message statement explained in the above sample situation. In this example, the data is a contiguous range of cells located in cells B2 through B19 (B2:B19) within book1.xls.
Activating the Bulk File Generator utility:
To activate the utility, select the menu item labeled "Export Cell Data to File(s)" under the Tools menu. Once selected, you will prompted with the following dialog box.
Step 1 - Select the range of cells you want written to the file(s):
Use the following screen shot to aid in instruction:
In the section labeled Step 1, choose a range of cells selection method from the drop down scroll box. Here you can choose either to manually select your own data including blank cells if needed or to use the automatic selection feature for a Contiguous Vertical Range of Cells. If you choose the Contiguous Vertical Range of Cells option, the program will automatically select the entire Contiguous Range of Cells for you based on a single cell you selected within the cell range. The selected range of cells in both cases will contain the data that will be written to the file(s). Next, press the "Select the Range of Cells" button to select the cells to be exported. After you have made your selection, the range of cells selected will be displayed in the dialog box under the "Select the Range of Cells" button using a blue colored font.
Step 2 - Choose the file output method you want to perform:
Use the following screen shot to aid in instruction:
In the above example, the data selected in Step 1 was a range of cells located in cells B2 through B19 (B2:B19) within book1.xls. This range is shown as [book1]Sheet1!B2:B19 in the dialog box.
In the section labeled Step 2, choose an output method from the drop down scroll box. Here you can choose either Option 1, to generate a single new file (type in a file name) or to overwrite the selected file in the lower right side file directory pane or choose Option 2, the Bulk File Generation mode, to overwrite all files (*.*) within the current directory.
Note: If you select Option 2, as a preventative measure to avoid accidental file overwrites in the Bulk File Generation mode, the user will be prompted for verification before the bulk file writing process is performed.
Step 3a - Select a directory path where to save the generated file(s):
Use the following screen shot to aid in instruction:
The "Quick Jump" feature allows you to instantly switch (jump) to the directory located in the edit box. Enter a most frequently used directory path and press the "Save Path" button to save the directory for future use. The default path is set to the c:\windows\desktop directory folder.
To manually change directories (folders), double click the mouse within the left directory pane over the drive / directory listings, the red circled area. Double clicking the [..] will take you up one directory level. You can always see where you are located in the directory tree by looking above the directory panes at the folder location marked "Current directory" (the red circled area). In the illustration above, the current location is: C:\Backup\Sam\Excel\Bulk File Generator\Test Files.
Step 3b - Outputting the data to a either single file or to multiple files:
Single file output:
If you want to output the data to a new file, type in a filename and extension in the "File" edit box. If you want to overwrite a single file in the right side directory pane, then select the file with the mouse. Finally, press the "Generate File(s)" button. When the routine has finished, you will be alerted with an "Operation Completed" prompt.
Multiple file overwrite output- Bulk File Generation Mode:
If you want to overwrite all the files within the current selected directory, type in *.* in the "File" edit box, then press the "Generate File(s)" button. As a preventative measure to avoid accidental file overwrites in the Bulk File Generation mode, the user will be prompted for verification before the bulk file writing process is performed. See the screen shot below for the user verification prompt. If you choose "No", you will be returned back to the previous dialog box screen. Choosing "Yes" will tell the program to proceed with the Bulk File Generation routine and overwrite all the files in the directory shown. This overwriting process is extremely fast. When the routine has finished, you will be alerted with an "Operation Completed" prompt.
End of Document