excel called from Visual basic

[carlasilva]

Why when writing the code below, Visual basic indicates an error in the third line? if it was Set xlSheet = xlBook.Worksheets(1) or Set xlSheet =xlBook.Worksheets(2) or Set xlSheet =xlBook.Worksheets(3) he accpet.

Set xlApp = CreateObject("Excel.Application&quot
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(4)
xlBook.Worksheets(4).Name = "T"
xlSheet.Cells(1, 1).Value = "n"


Thank you very much.

 

[ponerse]

I'm guess you have to add a 4th worksheet before you can reference it.

 

[sdraper]

Agreeing with ponerse. Three sheets is the default for new workbooks. You can change this though in Tools > OPtions > General Tab.
Just curious as to why you would have
Set xlSheet = xlBook.Worksheets(4)
which gives the object xlSheet the ref to xlBook.Worksheets(4)
and then in the next line you have
xlBook.Worksheets(4).Name = "T"
why not simply
xlSheet.Name = "T" ?

hope this helps,
sdraper

 

[carlasilva]

Thank you , it worked!