Cannot close an open workbook from another workbook

[mrfitness]

I have workbookA on a network folder which when opened shows a splash screen form & does a bunch of stuff, and then opens workbookB (a larger file) on the users local folder.

When workbookB opens I need it to close workbookA. I've tried so many ways to close it but keep getting 'subscript out of range'.

Please help! I am using Excel 2010

WORKBOOKA code:

Please Login or Register  to view this content.

WORKBOOKB code:

Please Login or Register  to view this content.

[ImStevenB]

Subscript out of range can mean the file does not exist.

Looking at the code you have .. I thought you said that the file that Workbook A opened was on the users local drive? That would normally be the C: drive.

Your Workbook A code looks like it is opening a network mapped drive file and the Workbook B code looks like it is trying to close the local C: drive file.

Just my observation .. may want to double check the drive letters and path.

[mrfitness]

Sorry, I just changed the drive letters for the purposes of this post.
WORKBOOKA is on the network and WORKBOOKB is on the local folder, but I even tested WORKBOOKA on my C drive to close (as you can see in my code above). It is definitely not the spelling of the file path or file name before I posted.

[ImStevenB]

Try changing this line

Please Login or Register  to view this content.

To this

Please Login or Register  to view this content.

Basically just remove the path information. If the workbook is open, it should close it by referencing the name of the workbook.

Hope this helps.

[mrfitness]

That is great thanks, it works!
Only problem now is that the StartForm I have showing in WORKBOOKB in workbookopen now closes after that line is executed Any ideas?

[ImStevenB]

My Apologies .. I had been away on business and did not see the reply.

Did you get this resolved?

[mrfitness]

My Apologies .. I had been away on business and did not see the reply.

Did you get this resolved?

No I have not

[ImStevenB]

Do you have any Sub's in Workbook A that execute on a Workbook Close?

[mrfitness]

Do you have any Sub's in Workbook A that execute on a Workbook Close?

No code in workbook close

[ImStevenB]

Any chance you can either post or send me both workbooks without data in it? Then I can get a better look at it.

[mrfitness]

Sorry for the late reply...I figured out how to open another book and continue the code in the current book:

In workbook A, after I hide the splash screen I wrote:

Please Login or Register  to view this content.

[Norie]

Doesn't that end up with 2 instances of Excel open?

[mrfitness]

It opens the workbook B in a new instance but then the next line 'thisworkbook.close false' closes workbook A and you are left with workbook B open, which is what I wanted.
You have other suggestion perhaps?
Thanks in advance

[Norie]

The instance of Excel that the first workbook was opened it will still be there, so you'll have 2 instances.

That might not be a problem but it might not be a good idea either.

Anyway, is there any code in the workbook you are opening that is run when it's opened?

[mrfitness]

In workbook B, I have a user form appear which interacts with the user and upon user entry it stores values in cells 'hidden' behind the form

[Norie]

If that userform is being opened when Workbook B is opened then that's probably where the problem is.

When the userform is opened code executon will move to it and it won't return to the code that opened the workbook until you hide/unload the userform.

What do the two workbooks do?

Could they be combined into one workbook?

[mrfitness]

You make a good point...at one point the user form hides to allow a user to paste into the excel book, however when the form hides you just see a blank excel file and no where do I find the instance of workbook B. However when I attempt to re-open workbook A, only then does workbook B magically appear. This is a problem! Is there a shell command I can execute?

[Norie]

What's the purpose of each workbook?

[mrfitness]

What's the purpose of each workbook?

Workbook A is a splash screen file on the network which checks for updates to ensure workbook b and the databases it works with are currently up to date
It then opens wb B, which is the main application on their local CPU

[mrfitness]

If that userform is being opened when Workbook B is opened then that's probably where the problem is.

When the userform is opened code executon will move to it and it won't return to the code that opened the workbook until you hide/unload the userform.

What do the two workbooks do?

Could they be combined into one workbook?

It was originally in 1 large file on the network but I broke it out into 2 parts so it can open locally

[Norie]

What happens if you change the code from the first post to this?

WORKBOOK A Code

Please Login or Register  to view this content.

WORKBOOK B code

Please Login or Register  to view this content.

[mrfitness]

You are right removing the path worked (was solved a few posts down on page 1)....the issue is that in workbook B I am showing a form on workbook open...and after executing the line to close workbook A from workbook B, the form in WB B hides automatically. If I try to show the form after closing WB A, it doesn't reach that line to execute.

[mrfitness]

After doing some more research I found the following solution in WORKBOOK B:

WORKBOOK A Code

Please Login or Register  to view this content.

WORKBOOK B workbook_open code

Please Login or Register  to view this content.

[Norie]

So a timing issue perhaps, that kind of makes sense.

If that works I would go with it rather than the creating a new instance of Excel.

[mrfitness]

Thank you to everyone for your help!