Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorksheet vs Worksheets.Item(1)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorksheet vs Worksheets.Item(1)
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorksheet vs Worksheets.Item(1)
"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 , "MP" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorksheet vs Worksheets.Item(1)
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 ... 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorksheet vs Worksheets.Item(1)
Thanks Peter and JE
Good points and infomation... "Peter T" <peter_t@discussions wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking item between two worksheets | Excel Worksheet Functions | |||
Keyboard Shortcut to toggle b/w Find and Replace and ActiveWorksheet? | Excel Discussion (Misc queries) | |||
Copy ActiveWorksheet Columns B,C,F to Another Worksheet in Workboo | Excel Programming | |||
Automating a line item for several worksheets? | Excel Worksheet Functions | |||
hyperlink item#'s across worksheets | Excel Programming |