Knowing how long to wait....?

[nigelandrewfoster]

Hello

My macro creates a PDF file (printing to, as it happens, Bullzip, a PDF Printer) called "To Email"
It then renames the file to a unique and useful name

However... It is frequently trying to
a) Create another file called "To Email" whilst the previous file is still called "To Email" or
b) Rename the file "To Email" when it has already been renamed.

I have tried many solutions. The only one that has worked is to create an arbitrary Application.Wait before AND after the rename. Got caught out this afternoon when installing onto a very slow PC. I have no way of knowing how long it should wait - in short, it's a cludge.

My simple solution does not work. Why?

Function Invoice_Exists() As Boolean    Dim strFilename As String
    strFilename = Worksheets("Workpad").[b1] & "NEW INVOICES\FOR EMAILING\To Email.pdf"
    If Dir(strFilename) <> "" Then Invoice_Exists = True
End Function

Sub Rename_File
            Do
            Loop Until Invoice_Exists()
            
[B]            Name Worksheets("Workpad").[b1] & "NEW INVOICES\FOR EMAILING\To Email.pdf" As Worksheets("Workpad").[b1] & _[/B]
[B]            "NEW INVOICES\FOR EMAILING\" & CName & Cpy & ".pdf"[/B]
            
            Do While Invoice_Exists()
            Loop
End Sub

This leads to File path access error at the highlighted line. When I hit CONTINUE, it works ok - so AGAIN it must be a timing error. Can someone explain why? And better still, a concrete solution? Some of my clients still use 2003, hence my need to use an external PDF creator. It's only the timing that I need help on, it works fine otherwise.

Many thanks

Nigel Foster

 

[Roderick_E]

wait is a notoriously bad way to handle anything since as you experienced, different CPU process at different wait times due to load and such. Just because you tell Excel to .wait X amount of seconds doesn't mean that's what will happen. Can you add an extension to the file name like "To Email_" & format(now(), "yymmddhhmmss") to make it unique?

 

[nigelandrewfoster]

Thanks for your reply! Wait was an absolutely last resort that I used today when nothing else worked. The initial filename has to be a default one as it is established in the settings of the PDF creator and can not be accessed via VBA. Obviously if I add a suffix when it is renamed I hit the same snag...

 

[SpillerBD]

I often hit some alert from Excel about the filenames. Are you using any

Application.DisplayAlerts = False

to avoid that interaction?

When done manually, are there any message boxes that come up?

 

[nigelandrewfoster]

Hello. Thanks for replying. No such line used in this macro. Could you clarify what you mean by "often hit some alert from Excel about the filenames", please?

Appreciate your time.

 

[SpillerBD]

reread the OP.
So it sounds like your macro blazes through the process but gets hung up waiting for the file system/print job to finish. If the Macro is going to interact with any newly created PDF's, there will still remain some wait issue.
The best solution for a wait process is to have routine log the time the process begins and then check for a minimum elapsed time to have past before continuing. By using the system clock you get more consistent results, but you baseline must use the slowest most dreadful machine under the worst conditions. It becomes a poor trade of efficiency for reliability.
As far as 2003 or 2013/2016 with the ability to export directly to PDF, I think you would still have some wait issues. However that might be alleviated because that would be occurring in Excel and not a separate application.