saving pdf file of the specified range

[Joseph89]

Hello All,

I would like to save range of A1:T38 of sheet1 as a pdf file in a folder which has a name regarding cell A1 in :C\ .

Could you please some of you guys help me?

[gmayor]

How about the following. Note that it does not contain error handling for illegal filename characters in the cell A1, nor validation for the existence of the named path, nor handling the existence of a file of the same name at the named location.



Sub SaveRangeAsPDF()
Dim xlSheet As Worksheet
Dim oRng As Range
Dim strFileName As String
Const strPath As String = "C:\Path\"

Set xlSheet = ActiveWorkbook.Sheets(1)
Set oRng = xlSheet.Range("A1:T38")

strFileName = strPath & xlSheet.Range("A1") & ".pdf"

oRng.ExportAsFixedFormat Type:=xlTypePDF, _
fileName:=strFileName, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=True, _
From:=1, To:=1, _
OpenAfterPublish:=True
lbl_Exit:
Exit Sub
End Sub

[Yongle]

Beaten to the draw by gmayor , but here is a very similar alternative

The message box will tell you where the file will be saved to
Current settings
File name assumed to be in A1 (name only without extension .pdf)
Save to folder c:\documents if you amend this line of code remember the trailing "\"
Current range being saved is Range("A1:T38") in sheet1


Sub Save_Range_As_PDF()


Dim MyFileName As String, FilePath As String, FullPath As String, MyRange As Range

MyFileName = Range("A1").Value
Set MyRange = Sheets("Sheet1").Range("A1:T38")
FilePath = "c:\documents\"
FullPath = FilePath & MyFileName & ".pdf"


MsgBox "Path and file name = " & FullPath


MyRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
FullPath, Quality:=xlQualityStandard, IncludeDocProperties _
:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

End Sub

[Joseph89]

Hello ,

Thank a lot for your quick reply but i am getting yellow error for following part:( , do you know the reason ?


MyRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
FullPath, Quality:=xlQualityStandard, IncludeDocProperties _
:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

[Joseph89]

Hello ,

Thank a lot for your quick reply but i am getting yellow error for following part:( , do you know the reason ?


MyRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
FullPath, Quality:=xlQualityStandard, IncludeDocProperties _
:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

[gmayor]

Which version of Excel do you have? This should work with 2010 or later.
In the case of 2007, you must either have the PDF plug-in or have updated to SP2 (which includes the plug-in).
It will not work with Excel versions before 2007.

[Joseph89]

Actually yes i am using excel 2013 , do i still need to update this?

[gmayor]

No - it should work with Office 2013. Did you ensure the path was present (or change the line to reflect an existing path)?


Const strPath As String = "C:\Path\" in my macro or

FilePath = "c:\documents\" in the other macro.

[Yongle]

Just to eliminate a couple of things for me, try using this code without modifying it in any way.
The range will save as file TestFile.pdf in c:\
Also it should open after saving in your default pdf reader
thanks



Sub Save_Range_As_PDF_2()
ActiveSheet.Range("A1:T38").ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\TestFile.pdf", Quality:=xlQualityStandard, IncludeDocProperties _
:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End sub

[Yongle]

sorry @gmayor (http://www.vbaexpress.com/forum/member.php?54471-gmayor) your post was not there when I started - got distracted! Did not mean to confuse.
Yon

[Joseph89]

thanks a lot but it still does not work:( ,I think better to do same thing but save as a jpg in folder instead of pdf

Could you help me to do that,?

[Yongle]

Please do ONE more thing for me before we give up
Record the following macro and post the code for us to look at

START recording macro

Click on file SAVE AS
and change file type to PDF
save the file

STOP recording macro

13102

[Joseph89]

Now i got it , while was triying save sheet as a pdf in :C/ ,system does not let me to save there maybe because of this i got error I changed the path and the code works perfectly. Problem is that when i save the range as pdf , it is devided two page , i think maybe range is too big thus better to take a screenshot an save it as jpg. What do you think?
Or can we add zome adjustment in pdf code?

[Yongle]

There are a couple of things that can be adjusted.
But the quality of the pdf will depend on how wide & long the spreadsheet is etc
Try this lot and see what it looks like.

Make your columns fit the data better - get rid of as much white space as you can by making the columns less wide

The pdf takes many of its settings from the print settings and so you can also:
: set print range to only include those cells (probably already done that in the macro)
: Force the range to fit to one page using "Scale to fit" on Page Layout tab and selecting 1 page for width and height
: Choose the best orientation to fit your data
- if spreadsheet is WIDER than LENGTH choose LANSCAPE
- if spreadsheet is LONGER than WIDTH choose PORTRAIT
: Select narrow margins
: If you do not want to print it out, then you could also choose different paper size to get best fit

13103

[Yongle]

One other thing to consider is:
Do you need all the columns printing out?
If not, Excel lets you "Hide" columns
It would be easy to add some code in the macro to make it hide columns only when creating the pdf

[Yongle]

removed this post (duplicated in error)

[Yongle]

And finally...
Excel does not allow you to save as a jpeg
A better solution would be to change the "save as" filetype to Single File Web Page - this allows you to look at the spreadsheet in your internet browser - you will probably think this is the best solution after you have tried everything, I think.

Before doing this you need to set A1 to T38 as the print range
Then click on "save as "
select filetype "Single File Web Page"
click small option called "selection" (on left)
click on publish
make sure your print area is selected
click on publish again