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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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?
 
Upvote 0
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.


 
Upvote 0
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
 
Upvote 0
Hello SSF1590,

So, there is a single folder for each file created?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Hello SSF1590,

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

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top