A problem with creating a workbook by VBA

  • Thread starter Thread starter wangww
  • Start date Start date
W

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!
 
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
 
Thanks, Tom

You're my hero. :)


Tom Hutchins said:
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
Click to expand...
 
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.
 
Back
Top