Hi there,
Brand new and an extreme novice so apologies.
I have a macro that I have found online that allows me to save the active worksheet as a PDF, with the save as box popping up. I would like to amend it to still have the save as box pop up so a file location can be chosen, but to have the file name already populated using a concatenated formula in cell B383. Can anyone help me please?
Here is the code:
Function RDB_Create_PDF(Myvar As Object, FixedFilePathName As String, _
OverwriteIfFileExist As Boolean, OpenPDFAfterPublish As Boolean) As String
Dim FileFormatstr As String
Dim Fname As Variant
'Test to see if the Microsoft Create/Send add-in is installed.
If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _
& Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <> "" Then
If FixedFilePathName = "" Then
'Open the GetSaveAsFilename dialog to enter a file name for the PDF file.
FileFormatstr = "PDF Files (*.pdf), *.pdf"
Fname = Application.GetSaveAsFilename("", filefilter:=FileFormatstr, _
Title:="Create PDF")
'If you cancel this dialog, exit the function.
If Fname = False Then Exit Function
Else
Fname = FixedFilePathName
End If
'If OverwriteIfFileExist = False then test to see if the PDF
'already exists in the folder and exit the function if it does.
If OverwriteIfFileExist = False Then
If Dir(Fname) <> "" Then Exit Function
End If
'Now export the PDF file.
On Error Resume Next
Myvar.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=Fname, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=OpenPDFAfterPublish
On Error GoTo 0
'If the export is successful, return the file name.
If Dir(Fname) <> "" Then RDB_Create_PDF = Fname
End If
End Function
Sub RDB_Worksheet_Or_Worksheets_To_PDF()
Dim FileName As String
If ActiveWindow.SelectedSheets.Count > 1 Then
MsgBox "There is more than one sheet selected," & vbNewLine & _
"and every selected sheet will be published."
End If
'Call the function with the correct arguments.
'You can also use Sheets("Sheet3") instead of ActiveSheet in the code(the sheet does not need to be active then).
FileName = RDB_Create_PDF(ActiveSheet, "", True, True)
'For a fixed file name and to overwrite it each time you run the macro, use the following statement.
'RDB_Create_PDF(ActiveSheet, "", True, True)
If FileName <> "" Then
'Uncomment the following statement if you want to send the PDF by e-mail.
'RDB_Mail_PDF_Outlook FileName, "[email protected]", "This is the subject", _
"See the attached PDF file with the last figures" _
& vbNewLine & vbNewLine & "Regards Ron de bruin", False
Else
MsgBox "It is not possible to create the PDF; possible reasons:" & vbNewLine & _
"Add-in is not installed" & vbNewLine & _
"You canceled the GetSaveAsFilename dialog" & vbNewLine & _
"The path to save the file is not correct" & vbNewLine & _
"PDF file exists and you canceled overwriting it."
End If
End Sub
Bookmarks