supress save as pdf dialog box excel with vba

Hazem Wageh

New Member
Joined
Jul 6, 2020
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
i need a modification in this code to suppress save as dialogbox(save file as pdf in mentioned location (C:\Users\hazem\Desktop\New folder (4)\HM\PDF) without any save as screen appears and without excel workbook name changed)_

note :_

  1. i work on windows 10 ,excel ver. 2019
  2. this code is part of macro
  3. (Application.DisplayAlerts = False _
""code between""_

Application.DisplayAlerts = true) doesn't work with me

This is the code:
Code:
Sub PDFActiveSheet()


Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim MyFile As Variant
Dim answer As Integer
Dim fnd As Variant
Dim rplc As Variant
Filename1 = Range("B4")
filename2 = Range("G11")
filename3 = Range("M4")
filename4 = Range("B4")
filename5 = Range("B5")
filename6 = Range("C5")


Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "yyyy-mm-dd_hhmm")

strPath = "C:\Users\hazem\Desktop\New folder (4)\HM\PDF"
If strPath <> "C:\Users\hazem\Desktop\New folder (4)\HM\PDF" Then
  Exit Sub
End If
strPath = strPath & "\"

'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")

'create default name for savng file
strFile = Filename1 & "_" & filename3 & " " & "of" & " " & filename2 & " " & "at" & " " & strTime & ".pdf"
strPathFile = strFile

'use can enter name and
' select folder for file
answer = MsgBox("Please!! Save the PDF to path (FINISHED CRS PDF OF SELECTED AUTHORITY) ", vbQuestion + vbYesNo + vbDefaultButton2, "CRS PDF CREATOR")
If answer = vbNo Then MsgBox "Please! Try again Later"
If answer = vbNo Then Exit Sub
Application.DisplayAlerts = False
ActiveWorkbook.Close savechanges:=True
MyFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")
Application.DisplayAlerts = False
If MyFile = Cancel Then Exit Sub

'export to PDF if a folder was selected
MsgBox "PDF file wil be opened in seconds "
MsgBox "please click CTRL+P to print PDF and change copies to the no. you need"
If MyFile <> "False" Then
    Application.EnableEvents = False
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                  Filename:=MyFile, _
                                  Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                                  IgnorePrintAreas:=False, OpenAfterPublish:=True
    Application.EnableEvents = True
    'confirmation message with file info
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & MyFile
    MsgBox "Your Work is done here!"
    MsgBox "Thank you"
End If
End sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,680
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Welcome to the forum. :)

Please take a minute to read the forum rules, especially the section on cross-posting, and then add the relevant links here. Thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,490
Messages
5,548,356
Members
410,828
Latest member
A9Bosv3
Top