none
Macro to checking Print Area and convert Excel to PDF RRS feed

  • Question

  • Hi

    My Excel files have 6 tabs.

    When I convert the Excel to PDF, there are few text missing in PDF which was not set in the Print Area in the Excel.

    It would be great if I get a macro to give an alert if the text on any of the tab does not incude in the Print Margin Area.

    So, I can correct the Print Margin manually and convert into PDF.

    Thanks 

    John

    Friday, November 1, 2019 4:41 AM

All replies

  • Hi Johnmiller1234,

    How about this?
    PrintPreview before converting each sheet to PDF
    I suppose it's an easier way to confirm if some text is missing.

    Regards,

    Ashidacchi -- http://hokusosha.com

    Friday, November 1, 2019 6:20 AM
  • Hi Ashidacchi,

    Every sheet in the work book has more than 5000 rows with more number of columns, so reviewing in Print Preview before converting each sheet to one single PDF would be little hard.

    So a macro to give an alert message indicates which sheet not set up correctly would be much helpful.

    Regards,

    John

    Friday, November 1, 2019 6:51 AM
  • Hi John,

    Thank you for more information.

    I'm wondering how we can determine print area in each sheet, i.e. which cell is the top left and bottom right.
    MinRow = Cells(1, 1).End(xlDown).Row
    MinCol = Cells(1, 1).End(xlToRight).Column
    MaxRow = Cells(Rows.Count, 1).End(xlUp).Row
    MaxCol = Cells(1, Columns.Count).End(xlToLeft).Column
    If the above is what we want, we could check these values are within PageSetup.PrintArea.

    Regards,

    P.S.
    Sorry, I did't check if the above code works.  

    Ashidacchi -- http://hokusosha.com

    Friday, November 1, 2019 7:16 AM
  • Hi Ashidacchi 

    Yes, the code is the base to cross check with PageSetup.PrintArea.

    Friday, November 1, 2019 7:42 AM
  • Hi Ashidacchi 

    Yes, the code is the base to cross check with PageSetup.PrintArea.

    Hi John,

    If so, I suppose you could check with PageSetup.PrintArea.

    By Recording Macro, PrintArea shows like ActiveSheet.PageSetup.PrintArea = "$A$1:$C$27".

    --------- please forget the below ---
    If you get value of PrintArea, you could check if MinRow/MaxRow/MinCol/MaxCol is within PrintArea.
    ------------------------------------------
    --------- modified the above ---
    You could set PrintArea with MinRow/MaxRow/MinCol/MaxCol.
    Of course, you need to convert column number (eg. 3) to column name (eg. C).

    Regards,

    Ashidacchi -- http://hokusosha.com


    • Edited by Ashidacchi Friday, November 1, 2019 9:51 AM
    Friday, November 1, 2019 9:41 AM
  • Hi Ashidacchi 

    I am not familiar with macro coding, could you please provide the full coding

    My file has 8  columns i.e., A:H

    Regards,

    John

    Saturday, November 2, 2019 4:42 AM
  • Hi John,

    I hope you will provide clear requirements.
    e.g.
    1. Your one Excel book has more than one sheet (you call it "Tab") and 
        a) one sheet is converted to one PDF file
        b) two or more sheets are converted to one PDF file
    2. in each sheet, PrintArea has been determined before conversion to PDF

    I cannot imagine all of your requirements. Please provide, explain them.

    Regards,

    Ashidacchi -- http://hokusosha.com

    Saturday, November 2, 2019 6:56 AM
  • Hi Ashidacchi,

    Thanks for the reply

    1. Yes, my Excel book has 6 sheets

    Its not reqires the macro to generate a PDF from Excel book,

    2. I need the macro needs to check all the 6 Sheets and determined the PrintArea of every single sheet and give an error message if the text exceeds the PrintArea in any of the sheets.

    So, I can manually adjust the print area and generate the PDF.


    Saturday, November 2, 2019 11:27 AM