A problem with creating a workbook by VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

wangww - 18 Mar 2008 15:35 GMT

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!

Reply to this Message

Tom Hutchins - 18 Mar 2008 15:54 GMT

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

> Hi
>
[quoted text clipped - 16 lines]
>
> Thanks in advance for any help!

Reply to this Message

wangww - 21 Mar 2008 04:36 GMT

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
[quoted text clipped - 26 lines]
> >
> > Thanks in advance for any help!

Reply to this Message

Andy K. - 21 Mar 2008 04:46 GMT

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

Reply to this Message