Save as pdf destination issues

Question

I am working on a save to pdf export from a loop function. I have set the file_name as:

Sub AutoFill_export2pdf()
'

Dim rowCount As Integer
Dim CurBU As String
Dim CurOPRID As String
Dim CurName As String
Dim CurJournalID As String
Dim CurJournalDate As String
Dim FILE_NAME As String

Sheets("List").Select

rowCount = ActiveSheet.UsedRange.Rows.count

Set Destsh = ActiveWorkbook.Sheets("Sheet")

For sourceRow = 2 To rowCount

CurOPRID = Range("A" & CStr(sourceRow)) 'OPRID
CurName = Range("B" & CStr(sourceRow)) 'Name
CurBU = Range("C" & CStr(sourceRow)) 'BU
CurJournalID = Range("D" & CStr(sourceRow)) 'Journal ID
CurJournalDate = Range("E" & CStr(sourceRow)) 'Journal Date

FILE_NAME = ActiveWorkbook.Path & "\" & "OTGL_" & "JRNL_" & CurBU & "_" &     CurJournalID & "_" & Format(CurJournalDate, "mm-dd-yyyy") & "_" & ".PDF"
CurName = "*" & CurName & "*"
CurBU = "*" & CurBU & "*"
CurJournalID = "*" & CurJournalID & "*"
CurJournalDate = "*" & CurJournalDate & "*"

Destsh.Range("K27") = CurName
Destsh.Range("D7") = CurBU
Destsh.Range("G7") = CurJournalID
Destsh.Range("I7") = CurJournalDate

On Error GoTo 0

Call SaveAsPDF(Destsh, FILE_NAME)

Sheets("List").Select

Next

End Sub


Public Sub SaveAsPDF(ByVal destSheet As Worksheet, ByVal PDFName As String)


On Error Resume Next
Kill PDFName

destSheet.Activate

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=FILE_NAME,   Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False


End Sub


Sub Autofill()
'

Dim rowCount As Integer
Dim CurBU As String
Dim CurName As String
Dim CurOPRID As String
Dim CurJournalID As String
Dim CurJournalDate As String
Dim FILE_NAME As String



CurName = "*" & CurName & "*"
CurBU = "*" & CurBU & "*"
CurJournalID = "*" & CurJournalID & "*"
CurJournalDate = "*" & CurJournalDate & "*"

Sheets("List").Select

rowCount = ActiveSheet.UsedRange.Rows.count

Set Destsh = ActiveWorkbook.Sheets("Sheet")

For sourceRow = 2 To rowCount

CurOPRID = Range("A" & CStr(sourceRow)) 'OPRID
CurName = Range("B" & CStr(sourceRow)) 'Name
CurBU = Range("C" & CStr(sourceRow)) 'BU
CurJournalID = Range("D" & CStr(sourceRow)) 'Journal ID
CurJournalDate = Range("E" & CStr(sourceRow)) 'Journal Date

FILE_NAME = ActiveWorkbook.Path & "\" & "OTGL_" & "JRNL_" & CurBU & "_" & CurJournalID & "_" & Format(CurJournalDate, "mm-dd-yyyy") & "_" & ".PDF"

Destsh.Range("K27") = CurName
Destsh.Range("D7") = CurBU
Destsh.Range("G7") = CurJournalID
Destsh.Range("I7") = CurJournalDate

On Error GoTo 0

Call SaveAsPDF(Destsh, FILE_NAME)


Sheets("List").Select



Next


End Sub

The goal is to export to a specific folder with the naming convention I have established. If you need the entire macro, please let me know.

When I do that, it doesn't save anywhere. Do I need to put the destination folder in the FIle_Name?? — TylerH, Jul 01 '16 at 16:46
Yes but you already have `ActiveWorkbook.Path` then again you're concatenating a lot of stuff.... make sure each item is correct. — findwindow, Jul 01 '16 at 16:47
concatenations are valid, I had this working a month ago and has since been edited. new to vba obviously :P Please see changes to code and any recommendation would be great — TylerH, Jul 01 '16 at 16:55
You did `Destsh.Range("K27")` why not here `CurOPRID = Range("A" & CStr(sourceRow))` — findwindow, Jul 01 '16 at 16:59
Don't know what that means but you missed my point XD Edit: wait, if the argument is `PDFName` why are you using `FILE_NAME`? Edit2: going to lunch. Still guessing one of your variables is bad. — findwindow, Jul 01 '16 at 17:01
firstly, thanks for your patience. secondly, should I change the PDFName to File_Name? or the other way around? — TylerH, Jul 01 '16 at 17:13
Take a look at what I posted and see if that helps you find out which variable it is. — Rodger, Jul 01 '16 at 17:17

Rodger · Answer 1 · 2016-07-01T17:16:33.790

In the interest of teaching to fish vs handing out fish...

Put option explicit at the very top (even before your first sub).
Open your locals window (View in the VBE). Step through your code with F8, watching your variables in the locals window to ensure that they are what you expect them to be at that step in your script.
Option explicit will identify numberous variables that you haven't declared. ("Variable not defined")
Fix those and any other issues that come up. (When it goes to other subs it will ID issues with variables there also.)
Your issue might go away just fixing what option explicit identifies and/or it might become apparent stepping through your code and watching the locals window each time you hit F8.
If you are still stuck, then edit your post with your updated code and explain any error message and line it is occurring on. If there isn't an error explain the expected and actual behavior.

score 0 · Answer 2 · answered Jul 01 '16 at 17:53

The above code works perfectly, the file I was working off of was a copy with an identical name saved in the temp folder, so all the files were saving into the active workbook path..which wasnt in the folder I was monitoring. I have about 30000 pdfs in there now from running the macro so many times, lol! Thanks to all who tried to help.

Save as pdf destination issues

2 Answers2