Eric Sherman
asked on
Access VBA Function To Rename A File After Printing To PDF Driver Fails???
DoCmd.OpenReport "ConfirmationEmail", acViewNormal
If Dir("C:\PDFOut\Confirmation.PDF") <> "" Then
Kill "C:\PDFOut\Confirmation.PDF"
End If
While Dir("C:\PDFOut\Confirmation.PDF") = ""
Name "C:\PDFOut\Report1.PDF" As "C:\PDFOut\Confirmation.PDF"
Wend
I have a VBA Funtion in a Access 2k F/E that has to first print a report to its default pdf printer, rename the file then attach it to a email. I am using BullZip PDF to accomplish this. The problem is the VBA code is trying to rename the file seems like before it has been created thus resulting in a "File Not Found" error. I can add a 5 second Sleep between printing the report and renaming it but I'd rather not as each report can be different and may require more time, etc.Pretty straightforward in that the PDF Driver is setup to always print the report in C:\PDFOut\Report1.pdf, then the code renames it so it can be attached to an email.
I need some way to have the code determine the PDF Printer is still working on the file. This is used in a Recordset processing loop for a table of customers. I have noticed the BullZip PDF Driver displays a little ballon in the bottom right corner of the Windows task bar informing that the PDF has been created.
Any ideas will be appreciated.
Thanks,
ET
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ET
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
http://support.microsoft.com/kb/213383
ASKER
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.
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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. WaitForFil e(strSourc ePDF, iTimeout) in the PDFPrinterSettings interface.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
ET
In what loop it is? In first or in second? What is happened with files?
ASKER
als315 ... First Loop.
Can't load up a sample because it is a F/E linke to SQL B/E db.
ET
Can't load up a sample because it is a F/E linke to SQL B/E db.
ET
ASKER
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
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
ASKER
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\DeliveryConfirm ation.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
Function Rename_File2()
Dim Fn As String
Dim Fn1 As String
Fn = "C:\PDFOut\Report1.pdf"
Fn1 = "C:\PDFOut\DeliveryConfirm
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?
"... 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.
<<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.
ASKER
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
ET
ASKER
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