Cannot get message box to show

S

Steven

I have a macro in File01.xls that opens File02.xls and then does an
Appliction.Run on the Auto_Open macro in File02.xls:

Private Sub MacroOpenFile02()

Workbooks.Open Filename:= "C:\File02.xls", Password:="aoaoao"
Application.Run "'C:\File02.xls'!Auto_Open"
Windows("File01.xls").Close (0)

End Sub

Now when the macro Application.Run "'C:\File02.xls'!Auto_Open" runs there is
a test in the code that depeding on the result, I may close the file with
Windows("File02.xls").Close(0) ; Now it returns back to continue in macro
in File01.xls I think but I want to put up a message saying File02.xls has an
error and cannot be open. I have made it work in File02.xls by putting the
MsgBox code line right before the Close(0) code line. But that means
File02.xls is open behind the MsgBox. When the user clicks ok on the MsgBox
it closes the file, but what I want is if there was an error for File02.xls,
that it closes so the user will not see File02.xls ... and then return to
File01.xls macro and show a MsgBox from File01.xls macro.

How do I accomplish this?

Thank you,

Steven
 
H

Héctor Miguel

hi, Steven !

can we assume that *only IF* an error occurs during process, File02.xls is (auto)closed
(otherwise it remains opened) -???-

if so, you can *test* IF file02.xls is still opened with something like...

Workbooks.Open Filename:= "c:\file02.xls", Password:="aoaoao"
Application.Run "'c:\file02.xls'!auto_open"
On Error Resume Next
Debug.Print Workbooks("file02.xls").Type
If Err Then MsgBox "An error has arised and File02.xls is NOT open any more !!!"

BTW using Windows("filename.XLS") could fail on systems where settings for registered FileType
are settled for NOT showing it's filename EXTensions -?-

hth,
hector.

__ OP __
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top