+ Reply to Thread
Results 1 to 2 of 2

VBA Page Setup when using ExportAsPdf

  1. #1
    Registered User
    Join Date
    12-10-2008
    Location
    Aberdeen
    MS-Off Ver
    MS365 VER 2308
    Posts
    79

    VBA Page Setup when using ExportAsPdf

    My workbook is on a network location and is available for anyone to use it. The purpose is to export a formatted report as a PDF.

    ws.ExportAsFixedFormat _
    Type:=xlTypePDF, _

    Problem is that one user has his PC connected to a Label printer, so default page size is tiny and it throws all sorts of issues with the PDF.
    I'm not sure where the page size comes from - whether it's the driver or whatever.

    How do I set the page properties so I get a good print/PDF every time, but not disturbing the users settings?

    thanks
    Guy
    Last edited by bluphoto; 05-01-2023 at 06:36 AM.

  2. #2
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,224

    Re: VBA Page Setup when using ExportAsPdf

    It seems that a reasonably safe scenario should look like this:
    1. examine the name of the active printer. If it is a printer that supports A4 or Legal, then you can export the sheet to PDF
    2. if it is a label printer, remember the printer name (to return to it in the future) and then set the active printer to one that supports A4 or Legal. This can be a physical printer (but here the problem arises that you need to check if it is installed on your computer) or a virtual printer, such as "Microsoft Print to PDF" (which I think is already installed with the system). We don't need to use it for printing. It's just a matter of temporarily changing the printer driver to one that supports the appropriate paper formats.
    3 Set the area to be printed and the paper size, programmatically.
    4. issue a command to export the sheet to PDF.
    5. restore as active the printer stored in step 2.

    The above instruction is probably imperfect, but at least it points in the direction you should go.

    Artik

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA Page Setup Scaling to Enlarge (Fit to one page) Wide or Tall
    By Bharti Goswami in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-25-2023, 01:48 PM
  2. [SOLVED] VB code to do page setup and find page breaks
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-14-2019, 06:37 PM
  3. [SOLVED] how to insert modified page setup in print setup
    By JEAN1972 in forum Excel General
    Replies: 1
    Last Post: 01-11-2019, 06:02 AM
  4. [SOLVED] need help with page setup/print Setup on a worksheet
    By laurabach in forum Excel General
    Replies: 9
    Last Post: 04-28-2013, 11:44 PM
  5. Page setup, view page in 3 equal sections?
    By JapanDave in forum Excel General
    Replies: 0
    Last Post: 07-13-2010, 08:03 PM
  6. Set page setup on chart page when no printer is connected
    By Nicky in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-24-2009, 10:13 PM
  7. extracting page number from page setup footer
    By Conor in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-15-2008, 01:43 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1