Code does not create PDF

[jex57]

Hi all,

I have the following function code (which as it is works):

Function RDB_Create_PDF(Optional Source As Object, Optional FixedFilePathName As String, _
                        Optional OverwriteIfFileExist As Boolean, Optional OpenPDFAfterPublish As Boolean) As String[INDENT=2]Dim FileFormatstr As String
    Dim Fname As Variant
    Dim ws As Worksheet[/INDENT]


    'Test If the Microsoft Add-in is installed[INDENT=2]If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _
         & Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <> "" Then[/INDENT]

[INDENT]If FixedFilePathName = "" Then[/INDENT]
[INDENT=2]'Open the GetSaveAsFilename dialog to enter a file name for the pdf
            FileFormatstr = "PDF Files (*.pdf), *.pdf"
            'Fname = Application.GetSaveAsFilename("", filefilter:=FileFormatstr, _
            '                                      Title:="Create PDF")
            Set ws = ActiveSheet[/INDENT]
            [INDENT=3]'Fname = Application.GetSaveAsFilename(Range("E13") & "_" & Range("M21") & "_" & VBA.Strings.Format(Now, "ddmmyy"), filefilter:=FileFormatstr, _
            'Title:="Create PDF")
            Fname = Application.GetSaveAsFilename(ws.Name & "_" & Range("M21") & "_" & VBA.Strings.Format(Now, "ddmmyy"), filefilter:=FileFormatstr, _
            Title:="Create PDF")[/INDENT]




            'If you cancel this dialog Exit the function[INDENT=2]If Fname = False Then Exit Function
        Else
            Fname = FixedFilePathName
        End If[/INDENT]


        'If OverwriteIfFileExist = False we test if the PDF
        'already exist in the folder and Exit the function if that is True[INDENT=2]If OverwriteIfFileExist = False Then
            If Dir(Fname) <> "" Then Exit Function
        End If[/INDENT]


        'Now the file name is correct we Publish to PDF[INDENT=2]On Error Resume Next
        Source.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                FileName:=Fname, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=OpenPDFAfterPublish
        On Error GoTo 0[/INDENT]


        'If Publish is Ok the function will return the file name[INDENT=2]If Dir(Fname) <> "" Then RDB_Create_PDF = Fname
    End If
[/INDENT]
End Function

This code creates my PDF file perfectly.

What I then wanted to do, was put it in a batch job, so created a sub that calls this. The sub is as follows:

Sub BatchRunMails()


Dim countrow As Integer, mailAddress As String, UniqueId As Integer
countrow = 5


While Sheets(BilledSheet).Cells(countrow, "A") <> ""[INDENT=2]mailAddress = Sheets(BilledSheet).Cells(countrow, "D")
    [/INDENT]
[INDENT=3]If mailAddress <> "" Then[/INDENT]
[INDENT=4]'MsgBox mailAddress[/INDENT]
[INDENT=4]UniqueId = Sheets(BilledSheet).Cells(countrow, "A")[/INDENT]
[INDENT=4]                Sheets(StatementSheet).Activate
        Sheets(StatementSheet).Cells(3, "O").Activate
        Sheets(StatementSheet).Cells(3, "O") = UniqueId
        Sheets(InvoiceSheet).Activate
        Sheets(InvoiceSheet).Cells(3, "M").Activate
        Sheets(InvoiceSheet).Cells(3, "M") = UniqueId[/INDENT]
[INDENT]
[/INDENT]
        
        [INDENT=4]RDB_Create_PDF
        RDB_Mail_PDF_Outlook[/INDENT]
[INDENT]End If[/INDENT]
    


    countrow = countrow + 1
Wend




End Sub

The sub calls the function without an issue, however it then does not produce the PDF at all - creates nothing.
I have stepped through the sub and found no errors, but obviously there is something that is falling over.

Any suggestions please.

Thank you

 

[yky]

When you stepped through the code, was the following code executed? What was the Fname?

Source.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                FileName:=Fname, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=OpenPDFAfterPublish

 

[jex57]

Hi,
FNAME = C:\Users\Documents\STATEMENT_Visser_E_040418.pdf

All the code executes, the save as dialog appears, the file looks like it is being created, but when you go to the folder there is nothing at all. No errors received either.

When I just run the Function RDB_Create_PDF
by itself, everything works - it is only when I try and call it from the batch run

 

[yky]

If you can upload the file so I can download it, I'll try it on my computer. Remove personal data first.

 

[jex57]

Hi,
Have tried but my permissions do not allow me to upload attachments.

 

[JazzSP8]

Long shot... But try a file search on your computer for one of the file names and see if it finds it.

I've had something similar in the past where I thought files weren't being created but they where actually going to a different folder than I expected.

 

[yky]

Hi,
Have tried but my permissions do not allow me to upload attachments.

There are many sites on Internet that let people store files for free.