PDA

View Full Version : Solved: Problem when trying to use close method of workbook object



anandks42
06-07-2008, 11:15 AM
Hi,

I am trying to close a workbook using the close method of workbook object. Here is the code I am trying:

Sub close()
Dim wb As Workbooks
Set wb = Application.Workbooks
'Trying to set savechanges to true
wb("book1.xls").Close (True)
End Sub

I get an error saying " Run time error 9 : Subscript out of range"

Please help me resolve this. I am new to excel VBA programming.Thanks!

xld
06-07-2008, 11:19 AM
Sub close()
Dim wb As Workbooks
Set wb = Application.Workbooks("book1.xls")
'Trying to set savechanges to true
wb.Close SaveChanges:=True
End Sub

anandks42
06-07-2008, 11:28 AM
Thanks xld. However when I try this I get the following error message:
Compile error: Wrong number of arguments or invalid property assignment

Norie
06-07-2008, 11:32 AM
Try this

Sub CloseWB()
Dim wb As Workbook
Set wb = Application.Workbooks("book1.xls")
'Trying to set savechanges to true
wb.Close SaveChanges:=True

End Sub


Note I would strongly recommend you don't use something like Close for the name of a Sub/Function, it's a VBA method.:bug:

xld
06-07-2008, 11:35 AM
Thanks xld. However when I try this I get the following error message:
Compile error: Wrong number of arguments or invalid property assignment

Sorry, didn't spot the Dim statement.

Change

Dim wb As Workbooks

to

Dim wb As Workbook

anandks42
06-07-2008, 11:54 AM
Thanks Norie and XLD but I am still getting the "subscript out of range" error. I am using excel 2003.

Simon Lloyd
06-07-2008, 11:57 AM
Runtime Error 9 Subscript out of range basically means that the worksheet /workbook name cannot be found, check the name in the code for the name of your worksheet/workbook check spelling/spaces.

Norie
06-07-2008, 11:58 AM
Do you actually have a saved workbook named Book1.xls?

By the way the original code you posted doesn't even compile.

So I don't quite see how you would get any error message because it wouldn't run.:confused:

anandks42
06-07-2008, 12:05 PM
Thank you Simon. I had not saved the workbook as book1.xls and hence the error.
Norie, I ran the code without compiling it! The issue is solved.

Norie
06-07-2008, 12:07 PM
Eh, the code wouldn't run if it didn't compile.:confused:

Unless you've changed some strange setting somewhere.

If I paste the original code into a module the first line is highlighted in red, indicating a syntax error.

anandks42
06-07-2008, 12:11 PM
I had not named the sub as close when i tried it. I had named it as temp. I renamed it when I typed it here as I thought the name would be more appropriate. That is why i did not get the syntax error!

Norie
06-07-2008, 12:23 PM
Please when posting code, post the actual code.:)