Results 1 to 3 of 3

Thread: Can I get this macro to run all the code before opening the PDF?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2014
    Posts
    2,326
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Can I get this macro to run all the code before opening the PDF?

    Hi Everyone,

    I have a macro below that works great but looks ugly,
    what i mean is it opens the pdf half way through the macro, so when you close the pdf it shows me a half loaded screen and a looks ugly before it runs the rest of the macro when everything looks great again.

    So I was wondering

    Is there a way i can get the macros to still run after its opened the pdf or open the pdf after its run the macros

    now the macros clear and reset the page the pdf is created from so we need the macro to be created before the call comand as it is shown.

    any ideas plaese?

    Thanks

    Tony


    my code

    Code:
    
    Sub ExportAsPDF3() 'iu
    Application.ScreenUpdating = False
    Call SetPrintArea ''
        Sheets("Fee Proposal PDF").ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=Sheets("Fee Proposal PDF").Range("AU51") & ".pdf", _
        OpenAfterPublish:=True
    Application.ScreenUpdating = True
    Sheets("Fee Proposal PDF").DisplayPageBreaks = False
    
    Call Clear_New_Fee_Proposal1
    
    Application.ScreenUpdating = True
    End Sub
    Last edited by RoryA; Sep 30th, 2019 at 11:06 AM.

  2. #2
    Board Regular Johnny C's Avatar
    Join Date
    Nov 2006
    Location
    Liverpool, UK
    Posts
    996
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I get this macro to run all the code before opening the PDF?

    Unfortunately there's no foolproof way. Excel doesn't 'handshake' with other applications, it fires off the Sheets("Fee Proposal PDF").ExportAsFixedFormat command then gets on with the next statement.

    The only way is to add a forced delay, e.g.

    For i = 1 To 5000: DoEvents: Next

    I use a variable e.g. and set it to 5000 at the top so if it plays up I can quickly change it.
    For i = 1 To intDoEventCount: DoEvents: Next

    Code:
    Sub ExportAsPDF3() 'iu
    Application.ScreenUpdating = False
    Call SetPrintArea ''
    Sheets("Fee Proposal PDF").ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=Sheets("Fee Proposal PDF").Range("AU51") & ".pdf", _
    OpenAfterPublish:=True
    For i = 1 To 5000: DoEvents: Next
    Application.ScreenUpdating = True
    Sheets("Fee Proposal PDF").DisplayPageBreaks = False
    
    Call Clear_New_Fee_Proposal1
    
    Application.ScreenUpdating = True
    End Sub
    Mine is used when copying charts to PowerPoint as Excel copies faster than PowerPoint pastes.

    Opening a PDF however is trickier as there's going to be a much longer delay as the PDF loads and formats itself.
    Last edited by Johnny C; Sep 30th, 2019 at 10:15 AM.
    "If you think this Universe is bad, you should see some of the others" - Philip K. DiĘk

  3. #3
    Board Regular
    Join Date
    Feb 2014
    Posts
    2,326
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I get this macro to run all the code before opening the PDF?

    Thanks Johnny C,
    I will give this a go and play around with timings etc.
    thanks
    for the input
    Tony

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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