Excel 2007 Workbook.Open crashes

[cls36]

Hello all,
This is my first time posting, but I'm in a real pickle. I'm pretty good with code in general and have learned VBA without much problem, but this one is stumping me. We have a workbook with VBA code to update it for a new year. When this code is run, it opens up 4 other excel files, reads data in, and closes them, in sequence. This file has been around and working for about two years, and was run to update as recently as this February without problems.

Just yesterday, I opened it up, clicked the button, and after a little run time, it said "Microsoft Excel has stopped working" and tried to recover. After much messing around, I've found the problem is when it tries to open the last file, called "Weekly Plan.xlsx". Originally, it opened it with:

Please Login or Register  to view this content.

Thinking this might be the problem, I changed it to:

Please Login or Register  to view this content.

It didn't change anything. And, mind you, this is the fourth file to be opened this way, and I changed all of them. It consistently crashes on this one.

I can open this file myself, manually, without any problems. The file has no code in it, and only two links, both to files that were previously opened in this code. However, the file does have an "Open in Read-Only" prompt. So, I saved a copy of it, titled "Copy of Weekly Plan.xlsx" without the prompt, and used it instead. It worked. The only problem is that Weekly Plan is updated by another system regularly, so I need that name. After messing around a little more, I made another copy of Weekly Plan titled "Weekly Plan2.xlsx", again without the prompt. It crashed. I removed the original file and renamed the Copy of to just "Weekly Plan.xlsx". It crashed. Back to Copy of and it works again.


At this point, I'm about ready to beat my head against my computer until one or the other suffers a catestrophic failure. Can anyone help me understand this?

[:) Sixthsense :)]

What happens if you set the read only mode of that workbook explicitly to true while trying to open the workbook via code?

Please Login or Register  to view this content.

[cls36]

Setting the workbook to read-only still causes a crash, but it seems that if I set OpnFl to Nothing first, and then set it to the Weekly Planning workbook, it works. Does a workbook variable retain any information from previous opens if it get's written over without being reset to Nothing? And I'm still mystified as to why this suddenly became a problem after two years of no problems. I'm still a little gunshy, so I'll test it more before I consider this issue solved. Thanks.

[:) Sixthsense :)]

if I set OpnFl to Nothing first, and then set it to the Weekly Planning workbook, it works

Yes when you use the same variable in loop then you have to reset it in the beginning or ending of each loop

[cls36]

Thanks, that seems to have solved it.