Closing a .txt File from Excel VBA

[Lightning]

I posted this problem in the VBA forum, but had no response. Hopefully someone here can help me resolve this problem.

I have an Excel 97 workbook which consolidates data on a monthly basis from 5 different text files (semi-colon delimited). I have written a macro for each workbook which opens the relevant file, copies the data to a worksheet in my main workbook, formats the data and then closes the text file. I then have a separate macro that calls these macros one by one to run the whole process. Except that the text files won't close, which then causes an error in the next macro when the next macro is called. I have tried:

Workbooks(MonthName & "Staff.txt&quot.Close Savechanges:=False
(where MonthName is a variable)

and

ActiveWorkbook.Close

but the file refuses to close. If I then try to close the text file manually, it won't close. The only way I can close the file is to shut down Excel altogether.

I've tried searching the MS Knowledge Base but have not found anything that refers to this problem. A similar search through this forum also came up blank. Is this a known probelm with Excel? Does anyone know why this happens and how I can resolve this?

TIA

Lightning

 

[Dreamboat]

Hey there.

Problem: Text files don't run macros, the don't even store them.

I don't do code, but I'm familiar with a lot of the processes (you know, I'm dangerous--so take this with a grain of salt).

Try closing the activeworkbook first and then do the save thing, instead of vice-versa?

http://www.PiersonTech.com

dreamboat@nni.com
Brainbench MVP for Microsoft Word

 

[tomschafer]

Can you post the code you have for your workbook?

 

[Lightning]

Hi People.

Sorry to take so long to answer you again, but I've been just a little bit busy this week. Thanks for your suggestions, but I've found what is causing my problem and resolved it.

The MS Office standard installation where I'm currently working has a custom add-in installed as a default. The way this add-in deals with Date formats was causing a conflict with Dates in the text files. This conflict was causing the problem.

Solution: Turn off the add-in for my workbook, run the import procedure, and then turn the add-in back on. The code ran perfectly, the text files closed when required.
No more problem!

Thanks again for your help

Lightning