Need to close Text file after copying it's contents via Excel 2000 VBA

[billfinnjr]

Good afternoon!
I am cleaning up a couple issues that might cause issues for someone else using the Workbook. The macro does everything I need it to do except to close the text file after copying the contents. Text file is generated by the AS400 and the name of the text file is variable, thus the wildcard in the file name. I need to modify the macro to close the text file when it is done with it. I'm running into issues.

I've tried Windows("S:\Estimator\QPQUPRFIL_GJH_QDFTJOBD*.txt").Activate
Windows.Close

I've tried some modifications of this with no luck at all.
Not sure if my issue is the wild card or if it is syntax or something else./
Any suggestions will be very much appreciated.
Thanks,
Bill

Please Login or Register  to view this content.

[Leith Ross]

Hello billfinnjr,

After looking at the code, I am not really sure what your issue is because there is no open text file, only a new workbook that contains the text file contents.

If you want to close this workbook without saving it then you could use this...

Please Login or Register  to view this content.

This will close the most recently opened workbook, which will be the one with the text file contents.

[billfinnjr]

Leith Ross,
Thanks for your response but the Text file is opened to copy the data and never closes unless I physically close it. I would love to be able to close it in the Macro so as to prevent oversights.
Thanks much
Bill

[billfinnjr]

After some research and head scratching I figured this out. Right after I open the text file I added this line;

Please Login or Register  to view this content.

At the end of the macro I added this line;

Please Login or Register  to view this content.

Problem is resolved and the unknowing won't have to worry about closing the text file manually
Learned something new in the bargain!
Thanks,
Bill

[Leith Ross]

Hello billfinnjr,

Is this text file open on your desktop?

Okay my system is a little slow. You got it figured out. Good job!