Close a .txt File using VBA

[Lightning]

Hopefully someone can resolve this problem for me.

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

 

[RobBroekhuis]

If I had a problem like that, I'd try putting a breakpoint in my code where it's trying to close the workbook, and try different approaches from the immediate window. First I'd try if at that point I can still close directly from Excel, then I'd check if the activeworkbook is really the one you think it is. Strange problem, though...
Rob

 

[Lightning]

Hi rob.

Sorry to take so long to answer you again, but I've been just a little bit busy! 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