Access VBA Function To Rename A File After Printing To PDF Driver Fails???

Thanks for the reply IrogSinta ...

No temporary file is created first and when viewing the C:\PDFOut folder while the function is running I see the file show up as Report1.PDF  14KB and then it is deleted.  Currently I have a Sleep 3000 immediately after the report prints but I'd rather not use that option for reasons mention earlier.

ET

lapatiya ... I had tried a similar concept earlier to no avail.  I tried your code and got the same error ... Run -Time  75  "Path/File access error".  This indicates it is trying to access the file while the BullZip PDF driver is still creating it.

ET

Thanks lapatiya but I can determine if the file exist ... the problem is the Name function is trying to rename is while the PDF driver is still creating it.  

I need to determine if the file is open by another application, etc.

ET

If the report1.pdf is still being created, file should be in Open state. Once the file is created by the pdf printer, file should be in close state, so no handles to any other programs. this is where we can rename it. see if this link helps;

http://support.microsoft.com/kb/213383

Thanks lapatiya ... that's the direction I was heading ... test to see if the file is open.

Here what I have tried also to no avail although I thought this would have worked.  I get the same error  "Path/File access error".  If I go in and manually step through the code and open the PDF with Adope the code will loop until I close the file.  However, when I run the code it still seems like VBA is getting to the stage of renaming the file while the PDF driver is creating it.  Kind of strange.

While Dir("C:\PDFOut\Confirmation.PDF") = ""
Do Until IsFileOpen("C:\PDFOut\Report1.PDF") = False
     xCounter = xCounter + 1
Loop
                        
Name "C:\PDFOut\Report1.PDF" As "C:\PDFOut\DeliveryConfirmation.PDF"
Wend


Function IsFileOpen(filename As String) As Boolean

    Dim filenum As Integer, errnum As Integer

    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.

    ' Check to see which error occurred.
    Select Case errnum

        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False

        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True

        ' Another error occurred.
        Case Else
            IsFileOpen = True
    End Select

End Function

Select allOpen in new window

That is understandable, that it runs according to the plan when you run it in debug mode since it reads line by line with your interaction, which gives enough room to the pdf printer to finish its job. But the codes runs much faster when you don't debug.

at this point i would like to suggest you to upload this access database, if there is no problem. so I can get deep into this

What interface are you using for Bullzip?  Command line? COM?  If you are using Shell with command line, I believe there is a log switch to create a log file.  Perhaps you could use that to determine if the file is done.  If you are using COM, then there is Bullzip.PdfWriter.PdfUtil.WaitForFile(strSourcePDF, iTimeout) in the PDFPrinterSettings interface.

Ok, thanks all for the replies ... To answer the question <<<What interface are you using for Bullzip?  Command line? COM? >>>

In a way neither ... This is an old Access 2k F/E that emails some specific reports to customers.  The reports are defaulted to print to the BullZip printer in Access Report Design (File --> Page Setup --> Default Printer).  It was created about 11 years ago and back then there was no code to easily change printer drivers and of course Office 2000 did not support PDF output but the client needed PDF format as requested by customers.  The client's IT purchased a PDF driver back then and that's what was used.  They have now switched to BullZip.  

Until they decide to upgrade the application to 2010 which supports PDF, all I need to do in the interim is change the specific reports to default to the BullZip PDF driver.  Therefore, just print the report, rename it and attach it to the email but apparently the VBA code is advancing at the same time BullZip is still creating the file.  A 5 second Sleep will eliminate the issue but down the road as reports vary ... the problem just may resurface.  

I will look at some of the options posted and reply.

ET

als315 ... I tried your code and once the PDF is created the code seems to stay in a continuous loop.  It never advances to the next step in the program.

ET

In what loop it is? In first or in second? What is happened with files?

als315 ... First Loop.

Can't load up a sample because it is a F/E linke to SQL B/E db.

ET

Here is what I'm now trying which is sort of on the lines what als315 suggested but after the first loop, BullZip creates the C:\PDFOut\Report1.pdf but the function seems like it hangs as well.  App freezes up and have to force it close, etc.  Once it tries to name the file, it should error, loop until the file is free but it does not.

Function Rename_File(Fn As String, Fn1 As String)

On Error GoTo Err_RenameFile

CheckIsFileOpen:
'Rename the file
Name Fn As Fn1

Exit_RenameFile:
Exit Function

Err_RenameFile:
If Err.Number <> 0 Then 'File is open or file does not exist
    Resume CheckIsFileOpen
Else
    MsgBox Err.Number & " " & Err.DESCRIPTION
    Resume Exit_RenameFile
End If
End Function


ET

Here is something else that I tried with this test function.  I can open the Report1.pdf file with Adobe and run the function below.  It will loop until I close the file then it will rename it.  Just strange it will not do the same when running it otherwise.  Maybe it has something to do with how BullZip throws that error???


Function Rename_File2()
Dim Fn As String
Dim Fn1 As String

Fn = "C:\PDFOut\Report1.pdf"
Fn1 = "C:\PDFOut\DeliveryConfirmation.pdf"

On Error GoTo Err_RenameFile

CheckIsFileOpen:
'Rename the file
Name Fn As Fn1

Exit_RenameFile:
Exit Function

Err_RenameFile:
If Err.Number <> 0 Then 'File is open or file does not exist
    Resume CheckIsFileOpen
Else
    MsgBox Err.Number & " " & Err.DESCRIPTION
    Resume Exit_RenameFile
End If
End Function

In my tst with manual file print to bullzip.pdf function working as expected.
"... First Loop."
First loop is waiting for file. Are you sure file is created and you send to function proper path?

ET,

<<Until they decide to upgrade the application to 2010 which supports PDF, all I need to do in the interim is change the specific reports to default to the BullZip PDF driver.  Therefore, just print the report, rename it and attach it to the email but apparently the VBA code is advancing at the same time BullZip is still creating the file.  A 5 second Sleep will eliminate the issue but down the road as reports vary ... the problem just may resurface.  
>>

  Not sure why your having such a problem with this.  I too use a printer driver to print to a file (although it's post-script output) and then convert to PDF using Ghost Script (that's the last half of the routine I posted).  I have no problems in simply waiting for the file to appear from the printer driver and then doing what I want with it.

  Seems like the driver your using is creating the file, then working with it after that, so if you jump on it right away, you have a problem.

  I would not try to rename the file, but try to open it  in exclusive mode with VBA and if you get a error wait.

  That's nothing different really then other things that have been suggested, so no points here please.

Jim.

Thanks all for the replies.  I agree with JDettman's last comments as I have used other PDF Drivers without experiencing this type of problem or conflict.  Basically as Sleep for 5 seconds seems to work the best using the BullZip PDF driver.

ET