×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Generate Data sheets from Template

Generate Data sheets from Template

Generate Data sheets from Template

(OP)
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.

RE: Generate Data sheets from Template

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

RE: Generate Data sheets from Template

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.

RE: Generate Data sheets from Template

(OP)
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.

RE: Generate Data sheets from Template

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.)

RE: Generate Data sheets from Template

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.

RE: Generate Data sheets from Template

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.

RE: Generate Data sheets from Template

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources