troubleshooting Question

How to tell when a file write using Print command in VBA has finished

Avatar of fester62
fester62Flag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft DOSMicrosoft ExcelMicrosoft Development
2 Comments2 Solutions554 ViewsLast Modified:
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
        End If
        Close #intFile
        intFile = 0
    End If

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 ?

Join our community to see this answer!
Unlock 2 Answers and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros