Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Sep 2016
    Posts
    6
    Thanks
    1
    Thanked 1 Time in 1 Post

    Generate overview in Word containing different tables and with Excel source

    Hello all,

    I want to generate an overview which shows all validations of fields that are used for creating some report.
    Please see attachments: in the excel file, tab 'Reports' contains the fields of the report. The tab 'SourceData' contains the source data that are used for the report.
    Now I want to generate the overview that contains all fields of the report and for each report field the source field with its validation. If a source field is calculated for the report, then no validation is applicable and this field is reported in another way; see attached Word example which is the file to be generated based on the Excel file.
    Anyone any idea how to get this done?

    Regards,
    Maurijn
    Attached Files Attached Files
    Last edited by mvddoes; 2016-09-22 at 02:45.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Hi Maurijn,

    I took a look at what you would like to achieve. Although this is possible, it would be so much more simpler to create the report on a new sheet within the same spreadsheet. Both the printout and display would look exactly like a word document, however, the benefits would be precise control of the formatted report, instant updates with all edits, and one less file to create/open/maintain.

    If you prefer a Word document as the final report, I would still build the report on a separate sheet then export it to Word.

    Questions:
    1. Must the final report be a Word Document?

    2. Is there only one record (line on Report sheet) per report or will the final report be a concatenation all the records on the Report sheet?

    3. What is the naming convention of the Word document(s) if preferred?

    That's all the questions I have right now. Others might have some additional questions to help develop a solution that fits your needs

    Maud

  3. #3
    New Lounger
    Join Date
    Sep 2016
    Posts
    6
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hello Maud,

    Thanks for your tips and please find the answers below:
    1. no, not strictly neccessary
    2. on the overview to be generated, there must be one record for each item on the report. I think you meant that with the first part of your question... so no concatenation is applicable...
    3. no special requirements... something like "Validations-" + <ReportName> + "-" + <CurrentYear> + "-" + <CurrentMonth> will do..

    Regards,
    Maurijn

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Maurijn,

    I'm working on a solution for you but I have a few other critical things to deal with today.
    Will post my solution as soon as possible.

    zeddy

  5. #5
    New Lounger
    Join Date
    Sep 2016
    Posts
    6
    Thanks
    1
    Thanked 1 Time in 1 Post
    Fine, I am looking forward to see it. Take your time and have a nice weekend first!
    Regards,
    Maurijn

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Maurijn

    ..see my attached version

    This will save the Report as a PDF file rather than a Word doc.
    I'll explain what I did in next post, just wanted you to test out and see what I did first.
    I added some more data to create a 'Part2' section for the test report.

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2016-09-26 at 08:59.

  7. #7
    New Lounger
    Join Date
    Sep 2016
    Posts
    6
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hello Zeddy,
    This looks great! I am impressed! I am looking forward to read your explanation...
    Regards,
    Maurijn

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Maurijn

    Welcome to the Lounge!

    OK, lets start with the assumptions. I agree with Maud that it is simpler to use a new sheet to create the required Report. A Word doc is easy to modify elsewhere, so perhaps a PDF file might be more appropriate. So lets go with a PDF file, just for starters, for simplicity.

    Sheet [Reports]
    It is assumed that the data records on this sheet will apply to a single Report only and the records have been sorted into the sequence that they will appear in the output file.
    For multiple Reports, we could just extract the required records for each Report onto this sheet. This would be simpler than using a data filter directly on this sheet.

    Sheet [SourceData]
    It is assumed that all relevant records for the required Report are on this sheet.
    A 'helper column' has been added in column [I] to simplify the processing of data for fields that have multiple validation conditions. It would be possible to do this without the helper columns (using array formulas etc etc etc), but why make it more complicated. The formulas in the helper-column must be 'copied-down' for all records on the sheet.

    Sheets [section]
    This sheet is used to process each record from the [Reports] sheet. Provision is made for up to 10 validation rules for any given field (this can be increased if really really necessary). For testing, a 'Clicker' button has been included (in top row, column [T] ) to show how each record will be dealt with. Use this clicker to see how each record will be processed.
    Formulas are used to 'fetch' the data from the [Reports] sheet for each record, and the associated corresponding data from the [SourceData] sheet. The layout of the required section on this sheet is 'appended' to the output report created on sheet [OverView]

    [OverView] sheet
    The column widths on this sheet are the same as on the [section] sheet.
    Use the print-page-setup on the [OverView] sheet to define paper size, headers, footers etc etc that will appear on the output file. These settings could also be defined via vba etc etc etc.

    By default, the output pdf file will be named as xxxxxx@yyyy-mm-dd.pdf
    ..where xxxxxx is the name of the Report (e.g. from cell [A2] on sheet [Reports]
    yyyy-mm-dd is the date the file was generated

    If the same Report is generated multiple times on the same day, it will overwrite prior copies i.e. only keep the latest. The file-naming convention can be easily changed in the vba code.

    If this all sounds Dutch to you, just ask again here and I'm sure our Lounge helpers will respond.

    zeddy

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Maurijn

    ..if you add more records to the sheet [Reports], there should be no 'empty' rows i.e. no gaps in the records.

    zeddy

  10. #10
    New Lounger
    Join Date
    Sep 2016
    Posts
    6
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hello Zeddy,

    Great work again! It is all clear to me... one question: when a validation text is pretty long, the autosize-height does not work (with word-wrap). I tried to define the cells as text, but nothing works... any idea how to get this solved?

    Regards,
    Maurijn

  11. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Maurijn

    By default, when you wrap text within a cell Excel will automatically adjust the row height so that all the text in that cell is visible. There are only two exceptions to this default:
    ⦁ The cell in which you are wrapping text is actually merged with another cell(s).
    ⦁ The height of the row in which that cell is located was previously set manually.

    Since we are using 'merged cells' for the Functional description of validations on our [section] sheet, we have to deal with this issue via vba. The easiest way to do this is to, first, adjust the row heights on the [section] sheet to allow for 'longer' text descriptions to be displayed in the merged cells. For example, in the attached updated version I have doubled the row height in rows 20:29 on the [section] sheet from 15 to 30. Click the clicker button to show record number 3 or 11 to see the result.
    If you have even longer text descriptions, then perhaps you could increase row height to 45 etc etc etc.

    I have adjusted the vba to make sure that the row heights are 'copied' when appending the sections to the [OverView] sheet. The row height of 30 is set for the validation rows in the vba code (this can be adjusted as required).

    Also, I noted during my testing that the last record was not being added to the output report. This has been fixed in the attached version.

    Let me know if this solves the issue.

    zeddy

    I also set the formatting for these rows to 'middle-aligned' text, with one 'indent'.
    Attached Files Attached Files

  12. The Following User Says Thank You to zeddy For This Useful Post:

    mvddoes (2016-09-28)

  13. #12
    New Lounger
    Join Date
    Sep 2016
    Posts
    6
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hellio again,

    It solves it yes, but some texts are much larger than other, so I solved it by iterating through the validations:

    For i2 = 1 To zValidationCount
    Call MergeAndFit(Range("e" & zPasteRow + 7 + i2 & ":i" & zPasteRow + 7 + i2))
    Next i2

    Using a MergeAndFit function:

    Public Sub MergeAndFit(ByVal r As Range)
    Dim Row As Range: Set Row = r.Rows(1)

    If Len(r.Cells(1).Text) > 260 Then
    Row.RowHeight = 75
    ElseIf Len(r.Cells(1).Text) > 195 Then
    Row.RowHeight = 60
    ElseIf Len(r.Cells(1).Text) > 130 Then
    Row.RowHeight = 45
    ElseIf Len(r.Cells(1).Text) > 65 Then
    Row.RowHeight = 30
    ElseIf Len(r.Cells(1).Text) >= 0 Then
    Row.RowHeight = 15
    End If

    End Sub


    Regards,
    Maurijn

  14. The Following User Says Thank You to mvddoes For This Useful Post:

    zeddy (2016-09-28)

  15. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Maurijn

    Top marks for dealing with that, and sharing your method with us.
    It is always great to get feedback, and for sharing with others.

    zeddy

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •