Create PDF FILE with name retrieved from a cell

marco -
marco - 24 Jan 2020 at 11:45

Hello,

as a novice in macros, I'm turning to you for help.
I want to save my file as a PDF in the same directory as my Excel file, retrieving its name from a cell.
I’d like to specify two things: the Excel file is located on an internal server (I don't know if that matters).
The cell where the name to be retrieved is located uses the formula
=CONCATENATE(E11;" "; B12;" "; E12)
to create the name.
Thank you in advance for your help!

Configuration: Windows / Firefox 72.0

Liens connexes:

3 réponses

Réponse 1 / 3

cs_Le Pivert Posted messages 8437 Status Contributeur 730

Hello,

like this:

Sub savepdf() Dim nom As String ' name of the pdf Dim chemin As String 'path of the save folder nom = Range("A1").Value 'adapt cell chemin = ActiveWorkbook.Path & "\" ' same folder as the workbook 'save as PDF ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ chemin & nom, Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ False End Sub

--
@+ The Pivert

marco

Hello,

thank you for your response and your promptness.
Indeed, it works very well!!!
I would like to push this a bit further if possible.
Let me explain. Could there be an error message if a PDF file with the same name already exists in the directory?
Thanks again!

cs_Le Pivert Posted messages 8437 Status Contributeur 730 > marco

You will automatically receive an error message if the file already exists in the folder.

However, if you want to test before saving, see this:

https://excel-malin.com/codes-sources-vba/vba-verifier-si-le-fichier-existe/

@+ Le Pivert

Réponse 2 / 3

marco

Hello,

thank you for your reply, but regarding the verification code, where should I put it?

cs_Le Pivert Posted messages 8437 Status Contributeur 730

Option Explicit Public Function FileExists(MyFile As String) 'by Excel-Malin.com ( https://excel-malin.com ) If Len(Dir(MyFile)) > 0 Then FileExists = True Else FileExists = False End If End Function Private Sub CommandButton1_Click() 'by Excel-Malin.com ( https://excel-malin.com ) Dim MyFile As String Dim name As String ' name of the pdf Dim path As String 'path of the save folder name = Range("A1").Value 'adjust cell path = ActiveWorkbook.Path & "\" ' same folder as the workbook MyFile = path & name If FileExists(MyFile) = True Then MsgBox "The file exists..." Else 'The file does not exist we can save 'saving as PDF ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ path & name, Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ False End If End Sub

@+ The Woodpecker

marco > cs_Le Pivert Posted messages 8437 Status Contributeur

Thank you again, but I poorly phrased my question; should this code be added to the end of the code you provided for creating the PDF?

cs_Le Pivert Posted messages 8437 Status Contributeur 730 > marco

It's well explained with the comments (in green)

You need to replace it if you have a button named "CommandButton1"; otherwise, you must put the name of your button instead!

See this to get started

https://support.microsoft.com/fr-fr/office/assigner-une-macro-%c3%a0-un-formulaire-ou-%c3%a0-un-bouton-de-contr%c3%b4le-d58edd7d-cb04-4964-bead-9c72c843a283?ui=fr-fr&rs=fr-fr&ad=fr

@+ The Woodpecker

Create PDF FILE with name retrieved from a cell

3 réponses

Epson printer prints unexpectedly

Texture issues in enshrouded

Announcement: online source code archive

Notice shows wr30 m

Search for a song in the series foudre

Help for programming came remote control

Windows 10 reset issues

Where is the house wii sport resort wii

Sms sfr "call me back" exists or not

Download games for ipod nano-chromatic