Export excel sheet as PDF to an existing folder

SSF1590

Board Regular
Joined
Oct 20, 2019
Messages
73
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

Well-known Member
Joined
Mar 17, 2008
Messages
1,858
Office Version
2010, 2007
Platform
Windows
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

Board Regular
Joined
Oct 20, 2019
Messages
73
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,954
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:

Code:
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

Well-known Member
Joined
Mar 17, 2008
Messages
1,858
Office Version
2010, 2007
Platform
Windows
Hello SSF1590,

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

SSF1590

Board Regular
Joined
Oct 20, 2019
Messages
73
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

Board Regular
Joined
Oct 20, 2019
Messages
73
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

Well-known Member
Joined
Mar 17, 2008
Messages
1,858
Office Version
2010, 2007
Platform
Windows
Hello SSF1590,

This macro should do what you want.

Code:
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

Board Regular
Joined
Oct 20, 2019
Messages
73
Thank you very much for your great help on this. It is working perfectly!
 

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,858
Office Version
2010, 2007
Platform
Windows
Hello SSF1590,

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

Forum statistics

Threads
1,078,367
Messages
5,339,783
Members
399,323
Latest member
letitiaysk

Some videos you may like

This Week's Hot Topics

Top