Macro to print to PDFCreator

chumley

New member
Joined
May 25, 2011
Messages
2
Reaction score
0
Points
0
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.
 
Hi chumley, and welcome to the forum!

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?
 
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:
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,
 
Back
Top