Hi Excel brains trust,
My company have created a Report generator using a spreadsheet + VBA/macros.
To use it, our employees fill in a row, select a template from a dropdown, enter the reference pertaining to it, and then click a "Create Document" button. The code then reads which template is required, creates a new Word doc from said template, and fills in the placeholders based on the row.
The code does the following steps:
We're looking to level this up with some new data we have but aren't sure how to go about it. I will do my best to explain what we are trying to achieve.
We want an additional sheet within the workbook, where we can copy and paste technical data from another programme into. This would be deleted and done fresh with each new document, as does not need to stay there. When the doc is generated, we want it to pull these rows into the doc as a new table per row present. It's easy enough to do it for one table/row but we need it to return multiple tables based on how many rows there are. E.g. one new table per row present. Sometimes this it's 2 - sometimes it's 20.
Something like...
> check 'Sheet 3' for rows excluding the header
> counts how many rows have data
> create the table with the placeholders
> find and replace based on placeholders
> loop until all rows with data have a table
This would likely be in between step 6 and 7 in the code?
I'm really hoping this makes sense! Here's a screenshot of how the sheet is set up.
Thanks in advanced - let me know what else I need to post! Hoping to avoid posting the whole code due to work confidentiality purposes but maybe I can post redacted parts or send them in a message if required.
Regards,
Mik
P.s. our VBA/Macros skills would be considered Intermediate.
My company have created a Report generator using a spreadsheet + VBA/macros.
To use it, our employees fill in a row, select a template from a dropdown, enter the reference pertaining to it, and then click a "Create Document" button. The code then reads which template is required, creates a new Word doc from said template, and fills in the placeholders based on the row.
The code does the following steps:
- Check if template selected in Cell C1
- Check if reference was provided in Cell F1
- Check the template actually exists within the folder
- Find the data and where the data starts
- Opens the correct Word doc and start the find and replace process within the main body of the document
- It loops through and fine/replaces based on placeholders in row 3
- Special handler for text boxes
- End code
We're looking to level this up with some new data we have but aren't sure how to go about it. I will do my best to explain what we are trying to achieve.
We want an additional sheet within the workbook, where we can copy and paste technical data from another programme into. This would be deleted and done fresh with each new document, as does not need to stay there. When the doc is generated, we want it to pull these rows into the doc as a new table per row present. It's easy enough to do it for one table/row but we need it to return multiple tables based on how many rows there are. E.g. one new table per row present. Sometimes this it's 2 - sometimes it's 20.
Something like...
> check 'Sheet 3' for rows excluding the header
> counts how many rows have data
> create the table with the placeholders
> find and replace based on placeholders
> loop until all rows with data have a table
This would likely be in between step 6 and 7 in the code?
I'm really hoping this makes sense! Here's a screenshot of how the sheet is set up.
Thanks in advanced - let me know what else I need to post! Hoping to avoid posting the whole code due to work confidentiality purposes but maybe I can post redacted parts or send them in a message if required.
Regards,
Mik
P.s. our VBA/Macros skills would be considered Intermediate.
