Generate Data sheets from Template

[cherry2000]

I need to generate Data sheets using a Template filled with some generic and some customised information. The customised information is filled in a seperate Database file with a file name. The customised data field is also highlighted in red in the database. Now all the macro needs to do is :

(initially user opens both the sheets..template + database)
1. search and replace all the red fields with data taken from the database
2. save the file using the filename given in the database
3. again repeat the process for the next row of data in the Database

I am attaching both the sample files, for reference.

If somebody can help with the Macro, I will be grateful.

This will be great tool for generating multiple data sheets.

 

[FrancisL]

If your Templates were done in Word you could use Word's Mail Merge facilities

 

[cowski]

Here is a macro for you. I changed one thing in the sample sheet, the MCC tag has its own cell. I found it easier to paste it into its own cell rather than appending it to the end of a cell. Before you run the macro you will have to change a few file paths (they are commented in the macro). The macro is stored in the database file, press 'alt' + 'F11' to open the editor.

 

[cherry2000]

Hi Cowski.. I just executed a trial run for the Macro with corrected file paths, and it works like a charm!! Thanks a lot!! This can be very useful for many other users.

 

[zelgar]

I've done a little editting to the macro. I don't know if this would be a problem, but the original macro kept all of the new spreadsheets open. I also changed how everything was copied (i.e., instead of doing a simple copy - paste and going back and forth between the files, I created strings for each of the items and only went back & forth once.)

 

[cowski]

Good additions, zelgar.

I should have mentioned in my previous post that there is plenty of room for improvement and some error checking is probably needed.

 

[zelgar]

Here's another version. This one you don't need to worry about changing the directory.

I also used the Choose command with an array.

There's so many fun ways of doing this

 

[zelgar]

First, I noticed a slight error in my last version of the macros. The directory commands didn't always work.

I also noticed that the current versions would want to replace the existing created files. This can be fixed by either deleting the current data in the database or by changing the starting row. Attached are 2 different versions that would fix this problem.

 

[cherry2000]

I must say thanks to all of you..the Macro works..and it should be a very useful tool, if Data sheets with say 95% generic data and remaining custized data is required to be generated.