A problem with creating a workbook by VBA

[wangww]

Hi

I know example1 is right, example2 is wrong.
Please tell me, why is the second example wrong?
Following is my code:

Example(1)
Public Sub CreatWB()
Set NewBook = Workbooks.Add
NewBook.SaveAs Filename:="D:\111"
End Sub


Example(2)
Public Sub CreatWB()
Set Newbook = Workbooks
Newbook.Add
Newbook.SaveAs Filename:="D:\111"
End Sub

Thanks in advance for any help!

 

[Tom Hutchins]

In the first example, NewBook is assigned to be the new workbook created. In
the second example, NewBook is assigned to be the Workbooks collection (not a
particular workbook). NewBook.Add creates a new workbook, but doesn't change
the value of NewBook. You can't use SaveAs with the Workbooks collection
itself.

Hope this helps,

Hutch

 

[wangww]

Thanks, Tom

You're my hero.

In the first example, NewBook is assigned to be the new workbook created. In
the second example, NewBook is assigned to be the Workbooks collection (not a
particular workbook). NewBook.Add creates a new workbook, but doesn't change
the value of NewBook. You can't use SaveAs with the Workbooks collection
itself.

Hope this helps,

Hutch

 

[Andy K.]

wangww,

When you add a new workbook to the workbooks collection. The
Newbook(workbooks.count) will refer to the new workbook. so we may change the
code as below:

Public Sub CreatWB()
Set Newbook = Workbooks
Newbook.Add
Newbook(workbooks.count).SaveAs Filename:="D:\111"
End Sub


Best Regards

Andy K.