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.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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?
 

chumley

New member
Joined
May 25, 2011
Messages
2
Reaction score
0
Points
0
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:

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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,
 
Top