Using VBA to export a worksheet to a pdf file

[Edwin_C]

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?

 

[sgmpatnaik]

@Edwin_C

HI

Please Try the Below 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

 

[Edwin_C]

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]

Hi

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

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]

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

Edwin

 

[sgmpatnaik]

@Edwin_c

Hi

Thanks for your kind words, if any problem get back to us

Thanks

SP

 

[Roggy_Swiss]

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]

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]

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]

@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>