macro save worksheet as pdf with same workbook filename and location

[skeeta]

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

 

[VoG]

It expects the filename to be in A1 of the currently selected sheet.

 

[skeeta]

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

 

[VoG]

Try

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

 

[skeeta]

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

 

[VoG]

Try

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

 

[skeeta]

Thank you very much! Works purrrrrfectly

 

[skeeta]

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

 

[UHsoccer]

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

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

Is there a solution for this?

 

[skeeta]

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