Vba Save To Pdf
- Mar 13, 2019 Excel VBA - Save As PDF Files. In Excel 2010 and later, you can export a sheet, or a group of sheets, as a PDF file. This tutorial shows sample code for doing this.
- The VBA function provides a way of testing any file name your users provide to save the PDF document as. Function ValidFileName(FileName As String ) As Boolean 'PURPOSE: Determine If A Given Excel File Name Is Valid.
Below is a simple VBA macro that will allow you to quickly turn your Microsoft Word Document into a PDF file in a snap. The code is written to save your PDF in the same folder as the Word Document file currently resides. If you need to make modifications, hopefully, you will be able to follow along. Let's start taking a look at the basic ways to save an Excel workbook using VBA. The most basic method to save Excel workbooks using VBA is the Workbook.Save method. Workbook.Save saves the relevant workbook. In other words, the Workbook.Save method is, roughly, the VBA equivalent of the Save command in Excel. If your spreadsheet has several tables and you need to save each one to a separate PDF, you can run this VBA code. When you run this macro, a dialog box will appear asking you to select the folder where you want to save your PDFs.
As the title suggests.. I'm looking to expand my automating to include directly printing to PDF. The macro(s) I have written already formats the print area and page setup as I need it. But when I go to record my macros nothing for printing, changing printers or saving to file is captured.
Is there a way to accomplish this? I could even get by if it just required a save location prompt. The file name I would imagine could be auto-generated by grabbing existing info within the spreadsheet.
1 Answer
if you are using Excel 2010 and above, then Excel provides you with in internal add-in which allows you to save the file as PDF format.
Use the following code in your macro to save your file as PDF
Here you can replace the path C:Users46506090DesktopBook1.pdf and file name to your liking.
Edit: If you intend to publish the entire workbook as PDF and not just the sheet you are working on just replace ActiveSheet.ExportAsFixedFormat to ActiveWorkbook.ExportAsFixedFormat
PrasannaPrasannaNot the answer you're looking for? Browse other questions tagged microsoft-excelmicrosoft-excel-2010pdfvba or ask your own question.
How to save a worksheet as PDF file and email it as an attachment through Outlook?
In some cases, you may need to send a worksheet as a PDF file through Outlook. Usually, you have to manually save the worksheet as a PDF file, then create a new email with this PDF file as attachment in your Outlook and finally send it. It is time-consuming to achieve it manually step by step. In this article, we will show you how to quickly save a worksheet as a PDF file and send it automatically as an attachment through Outlook in Excel.
Easily save a worksheet or multiple worksheets as separate PDF files at once:
The Split Workbook utility of Kutools for Excel can help you easily save a worksheet or multiple worksheets as separate PDF files at once. See screen shot:
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words..
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum..
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns..
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Create Mailing List and Send Emails by Cell's Value..
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment..
- More than300 powerful features; Works with Office2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Save a worksheet as PDF file and email it as an attachment with VBA code
Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!
You can run the below VBA code to automatically save active worksheet as a PDF file, and then email it as an attachment through Outlook. Please do as follows.
1. Open the worksheet you will save as PDF and send, then press the Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy and paste the below VBA code into the Code window. See screenshot:
VBA code: Save a worksheet as PDF file and email it as an attachment
3. Press the F5 key to run the code. In the Browse dialog box, please select a folder to save this PDF file, and then click the OK button.
Notes:
1. Now the active worksheet is saved as PDF file. And the PDF file is named with the worksheet name.
2. If the active worksheet is blank, you will get a dialog box as below screenshot shown after clicking the OK button.
4. Now a new Outlook email is created and you can see the PDF file is listed as an attachment in the Attached filed. See screenshot: Htc evo music player.
5. Please compose this email and then send it.
Excel Vba Programming Pdf
Note: This code is only available when you use Outlook as your mail program.
Related articles:
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails..
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range..
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns.. Prevent Duplicate Cells; Compare Ranges..
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select..
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more..
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments..
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic..
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF..
- More than300 powerful features. Supports Office/Excel2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features30-day free trial.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by50%, and reduces hundreds of mouse clicks for you every day!
or post as a guest, but your post won't be published automatically.
Vba Save Sheet To Pdf
Vba Save To Pdf
- Hello,
Is it possible to find the name for pdf from a cell? Ex. Cell H4
And in Cell H4 i want it to collect from three different cells. Is this possible? - To post as a guest, your comment is unpublished.How can I make it delete the saved pdf after it emails it?
- Hi Jason,
Sorry can't help you with that yet. You need to manually delete it after emailing it.
- To post as a guest, your comment is unpublished.Thanks it works.
- To post as a guest, your comment is unpublished.Hi, how can i save & send the pdf wit the workbook name with the current VBA code? what do i use instead of xSht.Name
- Hi James,
Do you want to send the active worksheet as pdf and name it as the workbook name?
- To post as a guest, your comment is unpublished.How would I edit the VBA script above so that the file name is saved as a specific cell selected within the current sheet, for example cell A1?
- Hi Tom.
Sorry can’t help with this.
Welcome to post any question in our forum: https://www.extendoffice.com/forum.html
You will get more Excel support from out Excel professional or other Excel fans.
- To post as a guest, your comment is unpublished.How can I add for example sheet 2 from the workbook as an pdf?
- Hi Armin,
You need to open the Sheet 2 in your workbook firstly and then run the VBA code with above steps to get it down.
- To post as a guest, your comment is unpublished.How would I edit the VBA script above so that it adds a date and time stamp to the file name that way it doesn't keep overwriting what is already saved?
- Dear Michael,
Please run the below VBA code to solve the problem.
Sub Saveaspdfandsend()
Dim xSht As Worksheet
Dim xFileDlg As FileDialog
Dim xFolder As String
Dim xYesorNo As Integer
Dim xOutlookObj As Object
Dim xEmailObj As Object
Dim xUsedRng As Range
Dim xStr As String
Set xSht = ActiveSheet
Set xFileDlg = Application.FileDialog(msoFileDialogFolderPicker)
If xFileDlg.Show = True Then
xFolder = xFileDlg.SelectedItems(1)
Else
MsgBox 'You must specify a folder to save the PDF into.' & vbCrLf & vbCrLf & 'Press OK to exit this macro.', vbCritical, 'Must Specify Destination Folder'
Exit Sub
End If
xStr = Format(Now(), 'yyyy-mm-dd-hh-mm-ss')
xFolder = xFolder + ' + xSht.Name + '-' + xStr + '.pdf'
'Check if file already exist
If Len(Dir(xFolder)) > 0 Then
xYesorNo = MsgBox(xFolder & ' already exists.' & vbCrLf & vbCrLf & 'Do you want to overwrite it?', _
vbYesNo + vbQuestion, 'File Exists')
On Error Resume Next
If xYesorNo = vbYes Then
Kill xFolder
Else
MsgBox 'if you don't overwrite the existing PDF, I can't continue.' _
& vbCrLf & vbCrLf & 'Press OK to exit this macro.', vbCritical, 'Exiting Macro'
Exit Sub
End If
If Err.Number <> 0 Then
MsgBox 'Unable to delete existing file. Please make sure the file is not open or write protected.' _
& vbCrLf & vbCrLf & 'Press OK to exit this macro.', vbCritical, 'Unable to Delete File'
Exit Sub
End If
End If
Set xUsedRng = xSht.UsedRange
If Application.WorksheetFunction.CountA(xUsedRng.Cells) <> 0 Then
'Save as PDF file
xSht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=xFolder, Quality:=xlQualityStandard
'Create Outlook email
Set xOutlookObj = CreateObject('Outlook.Application')
Set xEmailObj = xOutlookObj.CreateItem(0)
With xEmailObj
.Display
.To = '
.CC = '
.Subject = xSht.Name + '-' + xStr + '.pdf'
.Attachments.Add xFolder
If DisplayEmail = False Then
'.Send
End If
End With
Else
MsgBox 'The active worksheet cannot be blank'
Exit Sub
End If
End Sub- Hi Crystal,
It's really great and working perfectly for me. Need more help to add:
1. in 'To' I want to give link to particular cell of Active sheet like wise in CC and in BCC i would like to add active sheet link
2. in e-mail body i need to specify some standard text.
I will be great full to you for your help.
Thanks
Parag - To post as a guest, your comment is unpublished.Hi Crystal,
It's really great and working perfectly for me. Need more help to add:
1. in 'To' I want to give link to particular cell of Active sheet like wise in CC and in BCC i would like to add active sheet link
2. in e-mail body i need to specify some standard text.
I will be great full to you for your help.
Thanks
Parag - To post as a guest, your comment is unpublished.Hi Crystal,
It's really great and working perfectly for me. Need more help to add:
1. in 'To' I want to give link to particular cell of Active sheet like wise in CC and in BCC i would like to add active sheet link
2. in e-mail body i need to specify some standard text.
I will be great full to you for your help.
Thanks
Parag- Hi Parag Somani,
The below VBA code can help you. Please change the .To, .CC, .BCC and .Body fields based on your needs.
Sub Saveaspdfandsend()
Dim xSht As Worksheet
Dim xFileDlg As FileDialog
Dim xFolder As String
Dim xYesorNo As Integer
Dim xOutlookObj As Object
Dim xEmailObj As Object
Dim xUsedRng As Range
Dim xStr As String
Set xSht = ActiveSheet
Set xFileDlg = Application.FileDialog(msoFileDialogFolderPicker)
If xFileDlg.Show = True Then
xFolder = xFileDlg.SelectedItems(1)
Else
MsgBox 'You must specify a folder to save the PDF into.' & vbCrLf & vbCrLf & 'Press OK to exit this macro.', vbCritical, 'Must Specify Destination Folder'
Exit Sub
End If
xStr = Format(Now(), 'yyyy-mm-dd-hh-mm-ss')
xFolder = xFolder + ' + xSht.Name + '-' + xStr + '.pdf'
'Check if file already exist
If Len(Dir(xFolder)) > 0 Then
xYesorNo = MsgBox(xFolder & ' already exists.' & vbCrLf & vbCrLf & 'Do you want to overwrite it?', _
vbYesNo + vbQuestion, 'File Exists')
On Error Resume Next
If xYesorNo = vbYes Then
Kill xFolder
Else
MsgBox 'if you don't overwrite the existing PDF, I can't continue.' _
& vbCrLf & vbCrLf & 'Press OK to exit this macro.', vbCritical, 'Exiting Macro'
Exit Sub
End If
If Err.Number <> 0 Then
MsgBox 'Unable to delete existing file. Please make sure the file is not open or write protected.' _
& vbCrLf & vbCrLf & 'Press OK to exit this macro.', vbCritical, 'Unable to Delete File'
Exit Sub
End If
End If
Set xUsedRng = xSht.UsedRange
If Application.WorksheetFunction.CountA(xUsedRng.Cells) <> 0 Then
'Save as PDF file
xSht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=xFolder, Quality:=xlQualityStandard
'Create Outlook email
Set xOutlookObj = CreateObject('Outlook.Application')
Set xEmailObj = xOutlookObj.CreateItem(0)
With xEmailObj
.Display
.To = Range('A8')
.CC = Range('A9')
.BCC = Range('A10')
.Subject = xSht.Name + '-' + xStr + '.pdf'
.Body = 'Dear ' _
& vbNewLine & vbNewLine & _
'This is a test email ' & _
'sending in Excel'
.Attachments.Add xFolder
If DisplayEmail = False Then
'.Send
End If
End With
Else
MsgBox 'The active worksheet cannot be blank'
Exit Sub
End If
End Sub
- To post as a guest, your comment is unpublished.I have tried pasting this into a new module and i get Compile error: Sub or Function not defined. Please help.
- Same issue
- To post as a guest, your comment is unpublished.Dear Darren,
Which Office version do you use?- Office 360
- To post as a guest, your comment is unpublished.This is working great for me but is there a way to select a folder location automatically rather than select manually? I am hoping to do this for 40 sheets at once.