Export excel sheet as PDF to an existing folder

[SSF1590]

Hello,

I am looking for a code that allows to export an excel sheet as PDF that has name TARA-19-001 to an existing folder that was previously created with the same name in the following directory C:\Users\SSF1590\Desktop\TARA Project\Folders . The name of the file will change every time a new record is created(e.g. TARA-19-002, 003, 004, etc.). Therefore, the code should look for the folder name to save the pdf into that folder. Can I please have your help? I do not have an idea of how to do this.

Thank you.

 

[SSF1590]

Hello again. I just realized that I have a picture in my worksheet that is not being copy when I export along with the pdf file. Is it possible for the picture to be saved as well?

 

[John_w]

I tried Leith's code with a picture on Sheet3 and the picture is saved in the PDF file.

 

[SSF1590]

Hi John. thank you for your feedback. I have 3 pictures in the sheet I export as pdf and neither of them saved in the pdf file. Do you have any idea what could be?

This is the code,

Dim File As Variant
Dim FileSpec As Variant
Dim Path2 As Variant
Dim Wks As Worksheet

Set Wks = ThisWorkbook.Worksheets("CARForm")
Sheets("CARForm").Visible = True

' // User input file name
File = TextBox1

' // Parent folder path
Path2 = "C:\Users\SS1590\Desktop\CAR Project\Folders"

' // Add final "" to the full file path if needed
FileSpec = IIf(Right(Path2, 1) <> "", Path2 & "" & File, Path2 & File)

' // If the folder does not exist then create it
If Dir(FileSpec, vbDirectory) = "" Then MkDir FileSpec

' // Save the worksheet as a PDF file
Wks.ExportAsFixedFormat xlTypePDF, FileSpec & "" & File & ".pdf"