I am having difficulties understanding how the COM automation fuction FUNCTION Worksheets_Add($OBJECT, ...) works. I have a test code that opens an Excel instance and then creates a new workbook. To add a new worksheet to this workbook, I use the following:
WorkSheet = Worksheets_Add(WorkBooks, $STATUS=status)
call $WorkSheet_Activate(WorkSheet, $STATUS=status)
WorkSheet = $Workbook_GetActiveSheet(WorkBook, $STATUS=status)
call $WorkSheet_SetName(WorkSheet, 'SheetName', $STATUS=status)
When I try "WorkSheet = WorkSheets_Add(WorkBook, $STATUS=status)", the function returns an error. It would make sense to me to add a worksheet to the current WorkBook and not the WorkBooks collection.
Also, when I try to add a second sheet to the WorkBook, it always overwrites the first, no matter how I call the WorkSheets_Add function. I have tried using the 'After' argument assigned to the IDispatch pointer to the previous worksheet, but this does not seem to work. I think the error relates to the above code and the first and second worksheets are not members of the same workbook. Also note, I have created the WorkBooks collection but not the WorkSheets collection at this point. ??
I have made a surprising amount of progress in only a couple of hours at my first attempt at automating Excel through COM with IVF-10, but the process has been frusturating from a seemingly lack of documentation / information on the routines the Module Wizard creates. The F90VB documentation discussed in other posts has some high-level information but cannot help at the detailed level since most of these routines are hidden from the user.
Thanks in advance for any assistance or thoughts.