Error 1004 help needed

[sb1920alk]

I'm very close to having this fixed. I need a little bit of help on the last
part.

I have a file on a loop that opens another file, does a bunch of stuff, then
closes the other file. Then everything repeats. This runs 24 hours a day and
goes for days and days and works fine, then for no reason it gives: method
open of objects workbooks failed. All I can find is that this error means the
file I'm trying to open is corrupted, but I know this is not the case because
the file that is opening a closing again and again is not being saved, so
there are no changes. Additionally, to get things going agian, I just end the
debugger and open the first file again. That's it. But I want this to happen
on it's own.

All I need is an error handling code that will do this for me. One option
that would solve my problem would be two steps. On error:
1) Excel exists completely without saving changes on any open files.
2) Workbook Star1 opens

 

[Joel]

The file is probably not avvailable either bacuase of backups or a network
drive may be off-line. You probably need to put in an On Error sttatement
that waits 30 minutes and then tries again.

 

[sb1920alk]

Both files are on the computer that is running Excel. Perhaps it's still in
the process of closing it when it's ready to open it again? 30 minutes is far
too long. I could have it wait 30 second though. I'm not sure how to
implement the error handling to do this.

 

[Joel]

Here is an example of an error handler. If you comment out the "On Error"
statement you will get Error 1004.


Sub test()

For Each cell In Range("T9:T369")
On Error GoTo My_Error:
myRoom = WorksheetFunction.VLookup(cell.Value, _
ActiveSheet.Range("A115"), 1, False)

GoTo continue

My_Error: a = 1

Resume
continue: Next cell

On Error GoTo 0

End Sub

 

[sb1920alk]

I don't understand. I tried the macro. Leaving it as is causes excel to hang.
Commenting the on error gives "400"

 

[Joel]

I'm using Excel 2003. I took the code from another posting with error 1004.
So I know the code gives an Error 1004 on at least two PCs. I put the code
in a VBA module sheet (not a sheet code or Thisworkbook). Make sure you
Insert Module from VBA menu. Then put the code on the module sheet.