ActiveWorksheet vs Worksheets.Item(1)

[MP]

Hi
Trying to learn automating excel via vba

Dim oWb as Workbook
Set oWb = oExcelApp.WorkBooks.Add (TEMPLATE_NAME)
'that works

Dim oWs as WorkSheet
Set oWs = oWb.Worksheets.Item(1)
'That works

so the above line is ok but I thought maybe this would be a "better" way
I assumed a new workbook would begin with the first sheet activated???
'Set oWs = oExcelApp.ActiveWorkSheet

'but it throws error (Object doesn't support this property or method)

What is the proper way to get the first sheet in a new workbook?
Thanks
Mark

 

[Peter T]

Hi Mark,

Typically just after loading a template the activesheet would be the first
sheet in the template, ie first tab, though not necessarily depending on how
the template was saved.

change

'Set oWs = oExcelApp.ActiveWorkSheet

to
Set oWs = oExcelApp.ActiveSheet

Note Activesheet could also be a Chart sheet

Regards,
Peter T

 

[JE McGimpsey]

"Proper" doesn't have much meaning - there are usually multiple ways to
accomplish a goal in XL/VBA. Your first method obviously works fine,
though you can also use the shortcut

Set oWs = oWb.Worksheets(1)

(it's compiled the same way, IIRC).

Since creating a workbook makes it the active workbook, you could also do

Set oWs = oExcelApp.ActiveSheet

as long as the template was saved with the first worksheet active
(otherwise the ActiveSheet will be whatever sheet was active when the
template was saved).

My preference is the former, but both are "proper".

 

[Peter T]

Seeing JE McGimpsey's response makes me realize I didn't fully digest your
question, in particular that you want to reference the first worksheet
irrespective as to which is the Activsheet. Your code was right first time,
stick with -

Set oWs = oWb.Worksheets.Item(1)
or
Set oWs = oWb.Worksheets(1)

and for future refernce keep in mind your 'ActiveWorkSheet' should have read
ActiveSheet

Regards,
Peter T

 

[MP]

Thanks Peter and JE
Good points and infomation...

Seeing JE McGimpsey's response makes me realize I didn't fully digest your
question, in particular that you want to reference the first worksheet
irrespective as to which is the Activsheet. Your code was right first
time,
stick with -

Set oWs = oWb.Worksheets.Item(1)

Guess I'll stick with this to avoid possibility someone saved template with
different sheet active...
Thanks for the pointer

or
Set oWs = oWb.Worksheets(1)

I always try to avoid default properties...lots of experts advise against
that on vb groups...in case of future revisions to object model I
suppose(however unlikely that might be)

and for future refernce keep in mind your 'ActiveWorkSheet' should have
read
ActiveSheet

Yeah! Duh!