Using VBA to export a worksheet to a pdf file

Edwin_C

New Member
Joined
Apr 22, 2013
Messages
3
Hi All,


I'm new to the forum (first post) and am hoping to get some help.


I have a vba procedure in a workbook that, after updating some tables that a chart references - prints a page to a pdf file.


The code I use for this is as follows:


Sheets("Report").Range("B2:U59").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "\" & storelist(i) & ".pdf", to:=2, IgnorePrintAreas:=False


This part of code is within a loop (basically prints a page to a pdf file and then updates the page and charts before printing again).


It exports the file fine on all computers in the office.


The client who I set this up for however is having issues - the charts on the pdf output change in size and are much larger than they should be (they actually don't even fit on the page).


Is there any advice anyone can offer?
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

sgmpatnaik

Board Regular
Joined
Jul 6, 2012
Messages
75
@Edwin_C

HI

Please Try the Below Code

Code:
Private Sub CommandButton2_Click()
'This macro opens the SaveAs option with the defult file path "you have to set this file path below" coverts the whole sheet into .pdf file format
'And opens the .pdf to view <-- you can disable the view after covert option with lower code: OpenAfterPublish:=False

    pdfName = ActiveSheet.Range("T1")
    ChDir "C:\Temp\" 'This is where youo set a defult file path.
    fileSaveName = Application.GetSaveAsFilename(pdfName, _
    fileFilter:="PDF Files (*.pdf), *.pdf")
    If fileSaveName <> False Then
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
        fileSaveName _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
    End If
    MsgBox "File Saved to" & " " & fileSaveName
End Sub

Thanks

SP
 
Last edited:

Edwin_C

New Member
Joined
Apr 22, 2013
Messages
3
Hi Sp,

Thanks for passing this on - it seems to work okay apart from the dialog box (getsaveasfilename) remains open until the user presses the save button.

Is there any way for it to save without having the user select the 'save' button?

Thanks again for your help :)
 

sgmpatnaik

Board Regular
Joined
Jul 6, 2012
Messages
75
Hi

Sorry for my late replay please try the below code which is direct save the pdf format where the file is located

Code:
Private Sub CommandButton2_Click()


    pdfName = ActiveSheet.Name
    ChDir ActiveWorkbook.Path & "\"
    fileSaveName = ActiveSheet.Name
    
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        fileSaveName _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
    
    MsgBox "File Saved " & " " & fileSaveName
End Sub


Hope it is solve your problem

Thanks

SP
 

Edwin_C

New Member
Joined
Apr 22, 2013
Messages
3

ADVERTISEMENT

Sorry didn't get back to you earlier on this SP - just wanted to say thanks for all your assistance, really appreciate it :)

Edwin
 

Roggy_Swiss

New Member
Joined
May 30, 2013
Messages
10

ADVERTISEMENT

Hi everyone, I already have this code here to safe the excel sheet as an PDFSub SaveAs_PDF()
'Select the sheet with the report and set the print area
ActiveSheet.Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$N$66"
ActiveSheet.PageSetup.Orientation = xlLandscape
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="\\K:\EMEA\Closing EMEA\FY14\EMEA Prepayments FY14.pdf", Quality:=xlQualityStandard, IgnorePrintAreas:=False
End Sub, is there any way I can prompt the user to save the file under a different name? E.g. Message box: File Name? User enters: "Test", clicks OK, then the file is saved in the same path but named like the user wishes it to be saved as. Hope you can help. Thanks already in advance. Regards Roger
 

brodyhunt

New Member
Joined
Mar 13, 2013
Messages
5
Hi everyone, I already have this code here to safe the excel sheet as an PDFSub SaveAs_PDF()
'Select the sheet with the report and set the print area
ActiveSheet.Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$N$66"
ActiveSheet.PageSetup.Orientation = xlLandscape
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="\\K:\EMEA\Closing EMEA\FY14\EMEA Prepayments FY14.pdf", Quality:=xlQualityStandard, IgnorePrintAreas:=False
End Sub, is there any way I can prompt the user to save the file under a different name? E.g. Message box: File Name? User enters: "Test", clicks OK, then the file is saved in the same path but named like the user wishes it to be saved as. Hope you can help. Thanks already in advance. Regards Roger

You want to save Excel sheet to PDF with different names?
 

Roggy_Swiss

New Member
Joined
May 30, 2013
Messages
10
Hi brodyhunt. Yes, exactly! Many users use this excel file. On the sheet there is a CommandButton "Save as PDF". When everyone clicks the button the PDF is saved under the same location with the same name which will eventually lead to an error (cannot save two files with the same name). So what my idea was, is to prompt the user to enter a name for their specific data when clicking the CommandButton (variable due to Dropdowns on the sheet). This would ignore any errors, because the PDF's the users create all have different names. The first option that came into my mind was "InputBox", but as I'm pretty new to VBA I don't know how that would work. Thank you very much in the meantime! BR, Roger
 

sgmpatnaik

Board Regular
Joined
Jul 6, 2012
Messages
75
@Roggy

AS per my second post you can achieve your job actually what do you want please try that, i will give some instructions
1. Please create a Temp folder in C drive
2. Then use the code every time it will prompt to save box for where to save
3. Change This Line

pdfName = ActiveSheet.Range("T1")

pdfName = ActiveSheet.Range("A1")

Hope it will solve other wise please inform

Thanks

Patnaik
</pre>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,912
Messages
5,598,833
Members
414,260
Latest member
joishe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top