I have an Excel userform that writes out data to a text file (using Open and Print commands) by rows on the form, when a user selects the Submit button.
At the end of the process, I attach the resultant text file to an email and send.
An isses arises sometime that I dont get all the rows in the text file that I am expecting, the file is incomplete, which I put down to the fact that the OS has not finished writing the file when I do the email attachment. I can get round this by putting in the following code to instigate a pause before the emailing process takes over:
If blastLine = True Then
If Application.Wait(Now + TimeValue("0:00:10")) = False Then
intFile = 0
This waits 10 seconds before allowing the print routine to end. However, this solution is not very elegant as 10 seconds is often to long and sometimes may not be enough. I dont want users to wait longer than they should for the email to display with their attached file.
Is there a better solution that could be put in place to identify when a file write has completed and a file can be closed. Should I use some other method of writing the file such as utilising filesystemobject or does this have the same drawback ?