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

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




"MP" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>

"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".

In article <(E-Mail Removed)>,
"MP" <(E-Mail Removed)> wrote:

> 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

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


"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
> 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
>
>
>
>
> "MP" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >
> >
>
>

Thanks Peter and JE
Good points and infomation...

"Peter T" <peter_t@discussions> wrote in message
news:uY$(E-Mail Removed)...
> 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! :-)

>
> Regards,
> Peter T
>
>