OpenText function in macro hangs unless stepping through code in debug

**Part-Time Coder** · 10-29-2009, 05:51 PM

We have had a macro running for a few years (Excel 2007 now, but started in 2003) that imports about 35 text files into separate sheets, creates a calculated SUM field for each sheet and copies that value to a title (or summary) sheet. We use this 2 to 4 times per year at inventory time, copying to a new file and deleting the old data before running the macro. The imported files initially create new workbooks, but the data is copied to the initial workbook into a distinct sheets for each file.

Recently (well, last August) this macro started hanging after importing (Workbooks.OpenText) a number of files, and not necessarily the same file every time (on repeated runs.) While trying to figure out the problem, I have now managed to get it to hang every time on the first file! However, if I am stepping through in the debugger it continues past the OpenText command and on F5:Run/Continue will then continue processing the rest of the files normally.

If I delete the first file before running the macro, it then hangs on the second file instead.

Without debugging, the first file will import, display on-screen, and there it stops. If I put a break-point on the very next instruction after the import, that break-point is never reached.
THINGS I'VE TRIED:
I replaced the file contents with random typing (like ten lines of text) in case there were non-standard characters in the text file.
I tried separating the import into a separate subroutine.
I used 2-dimensional column arrays instead of the Array of Arrays currently in the code.
I've importing into a single 80-character column.
I've turned off screen updating while importing.
I've googled through at least 25 different relevant threads, and only a few of those were directly relevant (e.g., using two-dimensional column arrays to conserve memory.)

I'm about ready to open the files as input and write the data into a sheet line by line (probably would have been faster than all of that googling had I only known.

)

Here is the pertinent code:

Please Login or Register  to view this content.

Thanks in advance for any assistance!

**Part-Time Coder** · 11-06-2009, 05:26 PM

I think this is as solved as it's going to get for me; by reducing my forum search to just OPENTEXT, I ran across a suggestion from Andy Pope to use a Query Table instead of OpenText.

I have since modified my code to do this, and interestingly, can use TextToColumns to directly replace the QueryTable import with the separated column data that I need to manipulate. (Actually I tried to do this on separate sheets, but the TextToColumns always overwrote the QueryTable no matter what sheet was active or what cell I put in the Destination parameter.)

I still don't know why OpenText stopped working, but at least this provides the same outcome.

The code below, then, is my current workaround:

Please Login or Register  to view this content.

Thanks for providing the solution to a previously unresolvable problem!

**shg** · 11-06-2009, 05:32 PM

Thank you, it was thoughtful of you to stop back and post your solution. We'll try to do better by you next time.

And BTW, any suggestion by Andy Pope is spun from pure gold.