Macro to Export from Excel to .pdf

**davo3286** · 06-15-2018, 05:05 AM

Can anyone recommend a better Macro to be able to do the following:

1. Choose where you can save the exported file

2. MAXIMUM quality of export

Here is what I'm currently using:

Sub ExportPDF()

ThisWorkbook.Sheets(Array("Sheet1")).Select

    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:="C:\Users\user\Export", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        
ThisWorkbook.Sheets(Array("Sheet1")).Select

End Sub

Thanks as always!

**bakerman2** · 06-15-2018, 05:26 AM

Let's start with this.

Sub ExportPDF()
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Show
        FolderName = .SelectedItems(1)
    End With
    FolderName = FolderName & "\"
    FileFolderName = FolderName & "Export"
    Sheets("Sheet1").ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=FileFolderName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
End Sub

**davo3286** · 06-15-2018, 05:41 AM

The codes not working unfortunately, I do appreciate your time though!

**bakerman2** · 06-15-2018, 05:46 AM

And what exactly doesn't work ?

You asked for a Folderpicker and that's what you got.

**davo3286** · 06-15-2018, 05:52 AM

Apologies if I'm being a fool here, just my .jpg export code automatically pops up with a folder picker without me typing it into the code:

**bakerman2** · 06-15-2018, 06:00 AM

Do you have any other code still running while trying to execute ExportPDF ?

**davo3286** · 06-15-2018, 06:04 AM

No I closed the document and re-opened it to be sure.

Here is the code I use for .jp with folder picker:

Sub ExportLeagueTable()

 Dim oWs As Worksheet
 Dim oRng As Range
 Dim oChrtO As ChartObject
 Dim lWidth As Long, lHeight As Long
 Dim saveFileName As String

 Set oWs = ActiveSheet
 Set oRng = oWs.Range("A1:P26")
 
 Const cPICTURE_TYPE As String = "JPG"

saveFileName = Application.GetSaveAsFilename("NBL League Table 18." & cPICTURE_TYPE, cPICTURE_TYPE & " files (*." & cPICTURE_TYPE & "), *." & cPICTURE_TYPE)
If saveFileName = "False" Then Exit Sub
 oRng.CopyPicture xlScreen, IIf(cPICTURE_TYPE = "BMP", xlBitmap, xlPicture)
 lWidth = oRng.Width
 lHeight = oRng.Height

 Set oChrtO = oWs.ChartObjects.Add(Left:=0, Top:=0, Width:=lWidth, Height:=lHeight)

 oChrtO.Activate
 With oChrtO.Chart
  .Paste
  .Export Filename:=saveFileName, Filtername:=cPICTURE_TYPE
 End With

 oChrtO.Delete

End Sub

I can't write code I can just take bits and pieces from people and sometimes get things working but then other times like now I get a bit stuck.

Thanks

**bakerman2** · 06-15-2018, 06:12 AM

I'm sorry but I don't see the connection between this code and the code I posted you.

These are completely seperate things.

**davo3286** · 06-15-2018, 06:15 AM

I'm just giving you an example of something I have that works for .jpg and tried to then get the same outcome with .pdf

**bakerman2** · 06-15-2018, 06:32 AM

Something like this then ?

Sub ExportPDF()
    Const cDOC_TYPE As String = "pdf"
    saveFileName = Application.GetSaveAsFilename("D:\Export." & cDOC_TYPE, cDOC_TYPE & " files (*." & cDOC_TYPE & "), *." & cDOC_TYPE)
    Sheets("Sheet1").ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=saveFileName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
End Sub

**davo3286** · 06-15-2018, 06:38 AM

Unfortunately another error message, apologies.

**bakerman2** · 06-15-2018, 07:28 AM

There's a b too much in End Sub. My bad.

**davo3286** · 06-15-2018, 08:11 AM

**bakerman2** · 06-15-2018, 10:22 AM

You are probably using Option Explicit so you have to put this

 Dim saveFileName As String

as first line of the code

**davo3286** · 06-16-2018, 04:56 PM

That has now stopped me from having to add a file location into the code however, it automatically saves to my desktop and then opens the 'SaveAs' option but there is no PDF option and it only lets me select 'All Files (*.*)'

My problem is that this document is for multiple people to use so they need to get the option of where to save the PDF file once they click the macro button.

Any further suggestions welcome and appreciated

**bakerman2** · 06-16-2018, 07:40 PM

Sub ExportPDF()
    Dim saveFileName As String
    Const cDOC_TYPE As String = ".pdf"
    
    saveFileName = Application.GetSaveAsFilename("C:\", _
            cDOC_TYPE & " files (*" & cDOC_TYPE & "), *" & cDOC_TYPE)
    If saveFileName = "False" Then Exit Sub
    Sheets("Sheet1").ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=saveFileName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
End Sub

This code let's you select a folder and the oppertunity to fill in a filename. you don't need to add or select an extension because it will be added automatically upon saving.
You can even refer to a certain cell to fill in a standard filename.
After clicking OK the file will be saved in the location with the filename which you have filled in.
How much easier can it get.

**davo3286** · 06-17-2018, 06:28 AM

That's working perfectly... much appreciated Bakerman2, top work!

Macro to Export from Excel to .pdf

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro to Export from Excel to .pdf

Re: Macro to Export from Excel to .pdf

Re: Macro to Export from Excel to .pdf

Re: Macro to Export from Excel to .pdf

Re: Macro to Export from Excel to .pdf

Re: Macro to Export from Excel to .pdf

Re: Macro to Export from Excel to .pdf

Re: Macro to Export from Excel to .pdf

Re: Macro to Export from Excel to .pdf

Re: Macro to Export from Excel to .pdf

Re: Macro to Export from Excel to .pdf

Re: Macro to Export from Excel to .pdf

Re: Macro to Export from Excel to .pdf

Re: Macro to Export from Excel to .pdf

Re: Macro to Export from Excel to .pdf

Re: Macro to Export from Excel to .pdf

Re: Macro to Export from Excel to .pdf

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Macro to Export from Excel to .jpg

[SOLVED] Data Export Macro works in Excel 2013 but not Excel 2007

Macro export from excel

Macro to export from SAP to Excel

Required - An Excel 2003 Macro to export specific excel data into a word document

I would need a macro to export data from base example workbook to export worbook

Export dates from macro enabled excel spreadsheet into an excel calendar

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions