CODEWITHSUNDEEP
vbaexcelpdf
user3926006
user3926006

Reputation: 1

Save PDF in a specific folder programmatically

I am trying to get this program to save my pdf files in the same folder that the excel template is being saved in which is my Template Code folder. Here is the code that I have so far. The part where I am stuck is down at the bottom at "Dim Save Path.."

Any suggestions are greatly appreciated!

Sub SaveToPDF()

Dim fp As String
Dim fp1 As String
Dim i As Integer
Dim Max As Integer
Dim numprints As Integer
Dim fnum As String
Dim wb As Workbook

i = 1

fnum = Sheets("Sheet2").Range("B3").Value

Worksheets("Printable").Activate
Set wb = ActiveWorkbook

'counting the number of pagebreaks to identify number of prints
Max = ActiveSheet.HPageBreaks.Count

numprints = Max / 2
k = 10

'to get the file name for each PDF and setting the folder to print
For j = 1 To numprints
    fp1 = CStr(fnum & " - " & (Replace(Sheets("Printable").Range("f" & k).Value, "/", "-")))
    fp = CStr("Q:\PATS\24-7\Partnership Accounting\2013\2013 TAX\2013 Clients\8392 - 8413 AAF Master Folder\PDF\" & fp1 & ".pdf")

' exports 2 pages at a time and creates a PDF, then loops
    wb.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fp, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False, From:=i, To:=i + 1

    i = i + 2
    k = k + 70
Next j
MsgBox ("Print to PDF Complete, Check if you have " & numprints & " PDF Files")



'save the pdf in the 8392-8413 Master Folder under the Template Code Folder


       Dim SaveName As String

       SaveName = ActiveSheet.Range("G3").Text
            ActiveWorkbook.SaveAs Filename:=SaveName & ".xls"



Dim SavePath As String


SavePath = CStr("Q:\PATS\24-7\Partnership Accounting\2013\2013 TAX\2013 Clients\8392 - 8413 AAF Master Folder\Template Code" & fp1 & ".pdf")

            SavePath = wb.Path

            ChDir SavePath
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ 
                 SavePath & "Form 8392-8413 - " & ".pdf" _
                , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                :=False, OpenAfterPublish:=True


End Sub

Upvotes: 0

Views: 3569

Answers (1)

David Zemens
David Zemens

Reputation: 53623

Well first I see that you have two assignments to SavePath variable. First, Cstr("Q..."), and on the next line you are resetting it wb.path. That is probably the source of your error.

I think you do not need this line, it looks like it was leftover from debugging because this is a full filename + path.

If you want to use this line:

SavePath = CStr("Q:\PATS\24-7\Partnership Accounting\2013\2013 TAX\2013 Clients\8392 - 8413 AAF Master Folder\Template Code" & fp1 & ".pdf")

Then you must delete this line:

SavePath = wb.Path

Alternatively, if you want to use wb.Path to concatenate within the ExportAsFixedFormat method, then the error is this: wb.Path will not end in a path separator, and so that will raise an error when you try to save.

To fix it, try:

SavePath = wb.Path
If Not Right(SavePath,1) = Application.PathSeparator Then 
    SavePath = SavePath & Application.PathSeparator
End If

Upvotes: 1

Related Questions