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

3 answers

Answer 1 / 3
cs_Le Pivert Posted messages 8437 Status Contributor 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
0
1. 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!
  
  0
  1. cs_Le Pivert Posted messages 8437 Status Contributor 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
    
    0
Answer 2 / 3
marco

Hello,

thank you for your reply, but regarding the verification code, where should I put it?
0
1. cs_Le Pivert Posted messages 8437 Status Contributor 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
  
  0
  1. marco > cs_Le Pivert Posted messages 8437 Status Contributor
    
    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?
    
    0
  2. cs_Le Pivert Posted messages 8437 Status Contributor 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
    
    0
Answer 3 / 3
marco

Alright, perfect, thank you!!!
Have a nice day.
0

Create PDF FILE with name retrieved from a cell

3 answers

Can vsm help with a withdrawal at lucky31 casino? thank you

Unable to install the media feature pack on windows n

Regular wi-fi dropouts and severe wi-fi slowdowns

Ms office 365 activation license

After installing linux mint on an hp elitebook 8570

Mass wireless technics sl-b210 turntable of mass

Hello, i have a problem, my phone displays register.

Disappearance of the control panel

Ssd in an acer predator helios neo 18 ai pnh 18-72

Sync my emails on my pc from iphone 17 with outlook