Results 1 to 4 of 4

Thread: Macro to print to PDFCreator

  1. #1

    Macro to print to PDFCreator



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I'm using Ken's code taken from the following link in an attempt to create a single PDF document made up of multiple worksheets in the same workbook.

    http://www.excelguru.ca/node/22#MultiSingle

    The macro seems to run OK but I can't seem to find where the PDF is saved to. I've done multiple searches across all drives to no avail. Ideally I would like to to save to the same directory as the Excel file that I am converting with the same filename as the Excel file.

    It would be really appreciated if someone could assist. Apologies if this is simple thing to do, I'm a bit of a VBA novice!

    Thanks in advance.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,189
    Articles
    57
    Blog Entries
    14
    Hi chumley, and welcome to the forum!

    Quote Originally Posted by chumley View Post
    Ideally I would like to to save to the same directory as the Excel file that I am converting with the same filename as the Excel file.
    Ironically, that's exactly where the code points too. This part here:

    Code:
        '/// Change the output file name here!  ///
        sPDFName = "Consolidated.pdf"
        sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
    Basically builds the file path. So if your workbook is saved in C:\Temp, the sPDFPath variable will return:

    Code:
    C:\Temp\
    Then you'll see later in the code references to:

    Code:
    sPDFPath & sPDFName
    Which would return:

    Code:
    C:\Temp\Consolidated.pdf
    It's not showing up in the directory that your workbook is in though?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Quote Originally Posted by Ken Puls View Post
    It's not showing up in the directory that your workbook is in though?
    Many thanks for the prompt response Ken.

    No, I cannot find the pdf anywhere. The workbook I am using is saved on a network drive, I have searched the whole of that drive plus the c: drive for a document called Consolidated.pdf and it isn't there. Any chance it could be because it is saved on a network drive and not the hard drive of my PC? Also, the code is stored in a Personal Macro Workbook, could that also have an impact?

    Also, would it be possible to advise how to amend the 'sPDFName = "Consolidated.pdf"' part of the code to name the pdf the same as the workbook?

    Thanks for your help.
    Last edited by chumley; 2011-05-26 at 08:33 AM.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,189
    Articles
    57
    Blog Entries
    14
    Quote Originally Posted by chumley View Post
    Also, the code is stored in a Personal Macro Workbook, could that also have an impact?
    Hmm... I don't believe the folder your personal macro workbook is stored in is an indexed location for searching, but the code shouldn't be referring to that anyway.

    Try changing the following:
    Code:
        '/// Change the output file name here!  ///
        sPDFName = "CallItWhatever.pdf"
        sPDFPath = "H:\Folder\Subfolder" & Application.PathSeparator
    Obviously, you'll want to use your real file names and paths there.

    Let me know,
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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