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.

 

[Leith Ross]

Hello SSF1590,

Which path is correct: "C:\Users\SSF1590\Desktop\TARA Project\TARA", "C:\Users\SSF1590\Desktop\TARA Project\TARA-19", or "C:\Users\SSF1590\Desktop\TARA Project\TARA-19-001" ?

If the path is "C:\Users\SSF1590\Desktop\TARA Project\TARA-19-001" then is there a folder named "C:\Users\SSF1590\Desktop\TARA Project\TARA-002" for the second file?

 

[SSF1590]

Hello Leith,

The correct path would be C:\Users\SSF1590\Desktop\TARA Project\Folders\TARA-19-001. Therefore, there would be a previously created folder that will have the same name as the created pdf file. As the name of the pdf file changes based on textbox1.

 

[John_w]

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.

From your description and 'reading between the lines' because your description is a little ambiguous, my understanding is that your main folder is "C:\Users\SSF1590\Desktop\TARA Project\Folders" and within that the subfolders "TARA-19-001", "TARA-19-002", "TARA-19-003", etc. So the full folder paths are:

C:\Users\SSF1590\Desktop\TARA Project\Folders\TARA-19-001
C:\Users\SSF1590\Desktop\TARA Project\Folders\TARA-19-002
C:\Users\SSF1590\Desktop\TARA Project\Folders\TARA-19-003

In this example the latest subfolder is TARA-19-003 and you want the sheet to be exported as a PDF and saved as "C:\Users\SSF1590\Desktop\TARA Project\Folders\TARA-19-003\TARA-19-003.pdf". Is that correct? If I'm correct then try this macro:

Public Sub Save_Sheet_As_PDF_In_Latest_Folder()

    Dim mainPath As String
    Dim folder As String
    Dim subfolders() As String, n As Long, i As Long
    Dim latestSubfolder As String
    Dim PDFfullName As String
    
    mainPath = "C:\Users\SSF1590\Desktop\TARA Project\Folders\"
    If Right(mainPath, 1) <> "\" Then mainPath = mainPath & "\"
    
    'Create array of subfolder names
    
    n = 0
    folder = Dir(mainPath, vbDirectory)
    While folder <> vbNullString
        If folder <> "." And folder <> ".." Then
            ReDim Preserve subfolders(n)
            subfolders(n) = folder
            n = n + 1
        End If
        folder = Dir
    Wend
    
    If n > 0 Then
    
        'Determine latest (i.e. highest by ASCII character number) subfolder name
        
        latestSubfolder = ""
        For i = 0 To UBound(subfolders)
            If StrComp(subfolders(i), latestSubfolder, vbBinaryCompare) = 1 Then latestSubfolder = subfolders(i)
        Next
        
        'Export active sheet as PDF to latest subfolder, with same file name as the subfolder
        
        PDFfullName = mainPath & latestSubfolder & "\" & latestSubfolder & ".pdf"
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfullName, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    
        MsgBox "Sheet '" & ActiveSheet.Name & "' exported and saved as " & PDFfullName
        
    Else
    
        MsgBox "There are no subfolders in " & mainPath
        
    End If
    
End Sub

 

[Leith Ross]

Hello SSF1590,

So, there is a single folder for each file created?

 

[SSF1590]

Hello,

Thank you very much for your help on this and for sharing the above code. Sorry for the ambiguous explanation. The lates folder won't be TARA-19-003. For each new entry, that number will sequentially change (TARA-19-XXX). Every new entry changes based on textbox1 which is a sequential number generated. The name of the pdf file will be based on textbox1 and should be saved on the folder with the same name. For example, if my new entry is TARA-19-180, there will be a folder in my path C:\Users\SSF1590\Desktop\TARA Project\Folders\TARA-19-180 in which the my pdf file with the same name TAARA-19-180 will be saved. The code I am looking for is to save Sheet3 as pdf file with the name on texbox1 in the folder with the same name that already exist.

 

[SSF1590]

Yes, there is a single folder for each file created. I need to save sheet3 as pdf file with the name on textbox1 which can be TARA-19-180 (sequential number in the last three digits) and this file has to be saved in the folder with the same name in this path C:\Users\SSF1590\Desktop\TARA Project\Folders\TARA-19-180. There will be an existing folder with the name of the file.

 

[Leith Ross]

Hello SSF1590,

This macro should do what you want.

Sub Test()


    Dim File        As Variant
    Dim FileSpec    As Variant
    Dim Path        As Variant
    Dim Wks         As Worksheet
    
        Set Wks = ThisWorkbook.Worksheets("Sheet3")
            
        ' // User input file name
        File = TextBox1
        
        ' // Parent folder path
        Path = "C:\Users\SSF1590\Desktop\TARA Project\Folders\"
        
        ' // Add final "\" to the full file path if needed
        FileSpec = IIf(Right(Path, 1) <> "\", Path & "\" & File, Path & 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"
            
End Sub

 

[SSF1590]

Thank you very much for your great help on this. It is working perfectly!

 

[Leith Ross]

Hello SSF1590,

You're welcome. Thanks for the feedback. Good to know it is working correctly.