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!

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" wrote:

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!

Thanks, Tom

You're my hero. :)


"Tom Hutchins" wrote:

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" wrote:

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!

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.

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



"Tom Hutchins" wrote:

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" wrote:

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!