Results 1 to 3 of 3

Thread: Saving PDF to specific folder

  1. #1
    New Member
    Join Date
    Nov 2017
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Saving PDF to specific folder

    Hi

    I've built a code that searches through a list (rngWB) of excel workbooks and produces them as PDFs. The code works well, but insists on saving the individual PDFs to my Documents folder.

    I want them to be save to a folder in my documents, but that is further named by the contents of a cell.

    The code can create the folder if it doesn't already exist, but whatever I seem to do with the "Filename" part of producing the PDF, I can't get the files into the new folder.

    Any ideas how to get the PDF file named with rngWB to save into the FolderName please??

    Code:
    Private Sub CommandButton1_Click()
    Dim ws As Worksheet, wsRO As Worksheet
    Dim fdObj As Object
    Application.ScreenUpdating = False
    If Dir(("C:\Users\") & Environ("USERNAME") & ("\Documents\PDF Sheets for Meeting ") & ThisWorkbook.Sheets("Running Order").Range("F1").Value, vbDirectory) = "" Then
      MkDir Path:="C:\Users\" & Environ("USERNAME") & ("\Documents\PDF Sheets for Meeting ") & ThisWorkbook.Sheets("Running Order").Range("F1").Value
    Call PDFSheets
    Else
    Call PDFSheets
    End If
    Application.ScreenUpdating = True
    End Sub
    
    Sub PDFSheets()
    Dim xCell As Range, xYesorNo As Integer
    Dim myFile As Variant, FolderName As String
    Dim strPath As String
    Dim ws As Worksheet, wsRO As Worksheet, wsRes As Worksheet, wsPoi As Worksheet, rngWB As Range
    
    Application.ScreenUpdating = False
    
    Set wsRO = ThisWorkbook.Sheets("Running Order")
       FolderName = "C:\Users\" & Environ("USERNAME") & ("\Documents\PDF Sheets for Meeting ") & ThisWorkbook.Sheets("Running Order").Range("F1").Value
       
           strPath = Environ("USERPROFILE") & ("\Documents\") & wsRO.Range("F1").Value & ("\")
      
        
       For Each rngWB In wsRO.Range("AZ4", wsRO.Range("AZ" & Rows.Count).End(xlUp))
             With Workbooks.Open(strPath & rngWB.Value & ".xlsx")
                For Each ws In .Worksheets
    ws.Range("B2:AL113").ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=myFile, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
            
    ActiveWorkbook.Close savechanges:=True
    
    Application.ScreenUpdating = True
    
    Next
    End With
    Next rngWB
    MsgBox "Team Sheet PDFs have been produced and are in your Document folder" & Chr(13) & Chr(13) & "PDF Sheets for Meeting " & ThisWorkbook.Sheets("Running Order").Range("F1").Value
    End Sub
    


    Thanks

    FTF
    Last edited by Frankietheflyer; Oct 22nd, 2019 at 11:58 PM.

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,055
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Saving PDF to specific folder

    Try inserting this line

    Code:
    With Workbooks.Open(strPath & rngWB.Value & ".xlsx")
      For Each ws In .Worksheets
         myFile = FolderName & "\" & rngWB.Value & ".pdf"
    
    ws.Range("B2:AL113").ExportAsFixedFormat _
      Type:=xlTypePDF, _
      Fillename:=myFile, _
      Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, _
      OpenAfterPublish:=False
    Last edited by Yongle; Oct 23rd, 2019 at 04:52 AM.

  3. #3
    New Member
    Join Date
    Nov 2017
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Saving PDF to specific folder

    Excellent!! Thanks Yongle.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •