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

[anandks42]

Hi,

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

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

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!

[Bob Phillips]

[vba]

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

[anandks42]

Thanks xld. However when I try this I get the following error message:
Compile error: Wrong number of arguments or invalid property assignment

[Norie]

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

End Sub

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

[Bob Phillips]

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]

Thanks Norie and XLD but I am still getting the "subscript out of range" error. I am using excel 2003.

[Simon Lloyd]

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]

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.

[anandks42]

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]

Eh, the code wouldn't run if it didn't compile.

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]

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]

Please when posting code, post the actual code.