VBA code needed to save as PDF for certain page ranges.

[nahean]

I want to save a range of page in excel woksheet (e.g. page 2 to 9 and 14 to 21) as pdf. the following VBA i am using is saving the whole page as pdf.
the pdf is saved in the same folder where the excel is save, i need to save this pdf in a sub folder (a new folder will be allocated where the excel is present).



Sub PDFActiveSheetNoPromptCheck()

'checks for existing file
'prompt to overwrite or rename
'uses bFileExists Function, below

Dim wsA As Worksheet
Dim wbA As Workbook
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
Dim lOver As Long
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet

'get active workbook folder, if saved
strPath = wbA.path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

strName = wsA.Range("d5").Value _
& "-" & wsA.Range("b25").Value _
& "-" & wsA.Range("d6").Value

'create default name for savng file
strFile = strName & ".pdf"
strPathFile = strPath & strFile

If bFileExists(strPathFile) Then
lOver = MsgBox("Overwrite existing file?", _
vbQuestion + vbYesNo, "File Exists")
If lOver <> vbYes Then
'user can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
(InitialFileName:=strPathFile, _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and FileName to save")
If myFile <> "False" Then
strPathFile = myFile
Else
GoTo exitHandler
End If
End If
End If

'export to PDF in current folder
wsA.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strPathFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

'confirmation message with file info
MsgBox "PDF file has been created: " _
& vbCrLf _
& strPathFile

exitHandler:
Exit Sub
errHandler:
MsgBox "Could not create PDF file"
Resume exitHandler
End Sub
'=============================
Function bFileExists(rsFullPath As String) As Boolean
bFileExists = CBool(Len(Dir$(rsFullPath)) > 0)
End Function
'=============================



Help me out in this case. thanx in advance.

 

[BSALV]

page 2 to 9 and 14 to 21
export in 2 times, part 1=2-9, part 2=1-21, result 2 pdfs.

or
page 2 to 9 is easy if you change the pagesetup, but in the same sequence 14 to 21 is more complex.
You have to hide the section 15-20 and perhaps reset the pagenummer for page 21.

or
Do you know the exact ranges for page2-9 and 14-21 ?
otherwise you define the printarea as "A70:F700,A1400:F2000") according to those ranges

 

[nahean]

page 2 to 9 and 14 to 21
export in 2 times, part 1=2-9, part 2=1-21, result 2 pdfs.

or
page 2 to 9 is easy if you change the pagesetup, but in the same sequence 14 to 21 is more complex.
You have to hide the section 15-20 and perhaps reset the pagenummer for page 21.

or
Do you know the exact ranges for page2-9 and 14-21 ?
otherwise you define the printarea as "A70:F700,A1400:F2000") according to those ranges

yah, 3rd case. exact range of page 2-9 & 14-21, in a single pdf is preferred.

 

[BSALV]

it's difficult for excel to know exact the range of 2-9 and 14-21, because if you change the zoom or the margins or ..., everything can change.
try this (still without changing the page numbering)
The 2 ranges of printarea are the exact locations for the wanted output

     With Sheets("MySheet")
          .PageSetup.PrintArea = "$A$3:$D$20,$F$30:$K$50"       'set the printarea
          .PrintPreview
          .ExportAsFixedFormat Type:=xlTypePDF, Filename:=  ....
     End With

,

 

[nahean]

it's difficult for excel to know exact the range of 2-9 and 14-21, because if you change the zoom or the margins or ..., everything can change.
try this (still without changing the page numbering)
The 2 ranges of printarea are the exact locations for the wanted output

     With Sheets("MySheet")
          .PageSetup.PrintArea = "$A$3:$D$20,$F$30:$K$50"       'set the printarea
          .PrintPreview
          .ExportAsFixedFormat Type:=xlTypePDF, Filename:=  ....
     End With

,

oke, thanks.

can you edit the first code for only one range {eg page 2 to 9). that code save the pdf on the directory of excel file directory. it also prompt a warning before overwrite with same file name.

can you just add the only one page range on it (e g 2 to 9) and save the file in a sub folder of excel file directory.

 

[BSALV]

i hope you have a pdf printer as one of your activeprinters, so fill its name as "MijnPrinter"
This is only that part of your macro for the pdf file, so you can integrate it in your macro

Sub Testing()
     Dim MijnPrinter, MijnFilenaam
     
     With CreateObject("WScript.Network")
          For X = 1 To .EnumPrinterConnections.Count Step 2
               s = s & vbLf & .EnumPrinterConnections(X)
          Next
     End With
     MsgBox "all your available printers are" & vbLf & vbLf & Mid(s, 2)     '---> use one of the pdf-printers in this list as you "ActivePrinter" in the next line

     MijnFilenaam = ThisWorkbook.Path & "\The name.pdf"
     MijnPrinter = "Microsoft Print to PDF"

     Range("A1:K16,A23:K47").PrintOut from:=2, To:=3, ActivePrinter:=MijnPrinter, printtofile:=True, prtofilename:=MijnFilenaam



End Sub

 

[nahean]

i hope you have a pdf printer as one of your activeprinters, so fill its name as "MijnPrinter"
This is only that part of your macro for the pdf file, so you can integrate it in your macro

Sub Testing()
     Dim MijnPrinter, MijnFilenaam
    
     With CreateObject("WScript.Network")
          For X = 1 To .EnumPrinterConnections.Count Step 2
               s = s & vbLf & .EnumPrinterConnections(X)
          Next
     End With
     MsgBox "all your available printers are" & vbLf & vbLf & Mid(s, 2)     '---> use one of the pdf-printers in this list as you "ActivePrinter" in the next line

     MijnFilenaam = ThisWorkbook.Path & "\The name.pdf"
     MijnPrinter = "Microsoft Print to PDF"

     Range("A1:K16,A23:K47").PrintOut from:=2, To:=3, ActivePrinter:=MijnPrinter, printtofile:=True, prtofilename:=MijnFilenaam



End Sub

thanks, it helps a lot.

 

[Michelle723]

Can help to write the vba code for export all worksheet in a workbook to PDF and save into specific file folder and named value taking from individual sheet cell value at AD2.
Send out via email as PDF, email address will be taking from each sheet cell value H9?