Workbooks("test.xls").close stops program

[Nimbo1842]

Need some help with an application i'm writing. The problem that i have is that i have a to open different workbooks which isn't a problem however no matter what happens when i use the close function it stops by program from running.

I've use ActiveWorkbook.Close, Workbooks(2).Close and Workbooks("test.xls").close. All variations close the appropriate workbook and then nothing.

How can i close the right workbook and then continue the program.

 

[DavidAtWork]

I've used this method many times and never experienced this problem. Can you post your code as it might give a clue as to the cause of the problem

David

 

[Nimbo1842]

Thanks for looking at looking at this

Basically i open one workbook to run some code to create a tempory workbook and then run the tempory workbook and it should then close the origial workbook, whilst still being able to action the temp workbook! Simple

Startprogram Workbook

FileCopy "C:\Users\Nimbo1842\Documents\Project\Engagement Form v6.xls", "C:\Users\Nimbo1842\Documents\Project\temp\Engagement Form v6" + Application.UserName + ".xls"

Workbooks.Open "C:\Users\Nimbo1842\Documents\Project\temp\Engagement Form v6" + Application.UserName + ".xls"

The above Open command opens the temp workbook

Private Sub Workbook_Open()
StartUpScreen.Show
Workbooks("Startprogram").close
End Sub

The above just shows the StartUpScreen i swapped it to -

Private Sub Workbook_Open()
Workbooks("Startprogram").close
StartUpScreen.Show
End Sub

This now closes the StartProgram Workbooks but then doesn't show the startupscreen.

I've even tried having the below

Private Sub Workbook_Open()
StartUpScreen.Show
End Sub

Private Sub UserForm_Initialize()
Workbooks("Startprogram").close
End Sub

This again stops when the workwook Start program is closed and there is no StartUpScreen.

I've seen other similar problems on other forums regarding this but no solutions. i don't know if i've set things up correctly or its a problem with the command???

 

[boblarson]

You need to use OBJECTS with your code.

Private Sub Workbook_Open()
[B]Dim wb As WorkBook[/B]
    StartUpScreen.Show

Set wb = GetObject("[B][COLOR=red]YouNeedTheFullPathAndNameOfFileHere[/COLOR][/B].xls")
[B]wb.Close False[/B]
End Sub

 

[Nimbo1842]

Bob,

Thanks for that but i get exactly the same problem when the startupscreen.show line runs that it just brings up the startupscreen form and waits for the next input. Just to note that the startUp Screen is a form with four command buttons on it.

I've changed the StartUpScreen.show to come after the workbooks.close bit of your code

Private Sub Workbook_Open()
Dim wb As WorkBook

Set wb = GetObject("YouNeedTheFullPathAndNameOfFileHere.xls")
wb.Close False
StartUpScreen.Show

End Sub


This works fine in terms of closing the correct workbook. But then stalls like before, without showing the StartUpScreen, so back to square one