macro save worksheet as pdf with same workbook filename and location

skeeta

New Member
Joined
Jun 30, 2010
Messages
7
Hi,have viewed some macros that neeeeearly do this for me
e.g
Sub Create_PDF()

Application.ScreenUpdating = False
Range("A1").Select
x = ActiveCell.Value
ActiveSheet.PageSetup.PrintArea = "$A$4:$AB$52"

Dim ThisWBPath As String
ThisWBPath = ThisWorkbook.Path
ActiveWindow.SelectedSheets.PrintOut ActivePrinter:="Adobe PDF", PrToFileName:=ThisWBPath & "\" & x & ".pdf"

Application.ScreenUpdating = True

End Sub

I got this from a thread here,it does save the file to the same location,but file name is only "pdf"

thanks
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
It expects the filename to be in A1 of the currently selected sheet.
 
Upvote 0
What i need is for the worksheet to be saved as a pdf, with the file name of the worksheet,in the same location that the workbook is.
The code I showed was one I gt of the site that almost does it,but the file name is just"pdf",and not in exactly the folder as the original workbook
 
Upvote 0
Try

Code:
Sub Create_PDF()
With ActiveSheet
    .PageSetup.PrintArea = "$A$4:$AB$52"
    .PrintOut ActivePrinter:="Adobe PDF", PrToFileName:=ThisWorkbook.Path & "\" & .Name & ".pdf"
End With
End Sub
 
Upvote 0
Thankyou,
That saves it as the worksheet name in the right folder!
Can I include the workbooks file name before the worksheets name as well??

I really appreciate this
 
Upvote 0
Try

Code:
Sub Create_PDF()
With ActiveSheet
    .PageSetup.PrintArea = "$A$4:$AB$52"
    .PrintOut ActivePrinter:="Adobe PDF", PrToFileName:=ThisWorkbook.Path & "\" & Left(ThisWorkbook.Name, InStr(ThisWorkbook.Name, ".") - 1) & "_" & .Name & ".pdf"
End With
End Sub
 
Upvote 0
Sorry to trouble again,but when I go to open the pdf(that is correctly named and in the right folder) it wont open
Error message says because it is not supported or is damaged
I have a pdf
 
Upvote 0
I have the same problem.

When I do not specify a name for the output PDF file (that is, prtofilename ThisWBPathXXX is not defined), it displays a dialog box to define the location (directory) and file name. Adobe can open the file correctly.

Specifying a valid file name (say:ThisWBPathXXX = C:\Temp\Output.pdf), there is no dialog box displayed and it creates a pdf file that Adobe cannot open. It is approximately twenty (20) times the size of the one that does open

The complete message is:

-----------------
Adobe reader could not open "Output.pdf" because it is either not a supported file type or because the file has been damaged (for example, it was sent as an email attachment and wasn't correctly decoded)
-------------------

Here is the code

Code:
' ThisWBPathXXX is not defined
    ActiveSheet.PrintOut Copies:=1, preview:=False, ActivePrinter:=pdfPrinter, printtofile:=True, Collate:=True, prtofilename:=ThisWBPathXXX

Is there a solution for this?
 
Upvote 0
Hi UH soccer.

Thanks for the tip,you would have seen in the thread that I got this code from someone here,he said it works well as he has full version of Adobe,I only have V9
Can suggest where to put your code below to make it work?
Its a groovy little macro,if I can only get it to print!!!

Sub Create_PDF()
With ActiveSheet
.PageSetup.PrintArea = "$A$4:$AB$52"
.PrintOut ActivePrinter:="Adobe PDF", PrToFileName:=ThisWorkbook.Path & "\" & Left(ThisWorkbook.Name, InStr(ThisWorkbook.Name, ".") - 1) & "_" & .Name & ".pdf"
End With
End Sub

thanks
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top