COM Syntax Queston (Excel example but general puzzle)

If/when driving Excel or other apps via COM/ActiveX, "dot" syntax isn't always available for every method and one is forced tor resort to invoke 'ing a method. This generally isn't too bad to figure out the syntax needed from the VBA documentation and is usually more-or-less straightforward when the optional parameters are constants and/or there is an example that shows calling syntax.

An example there that is pretty simple but works is to open or save a file--here's a code snippet that works to create a new workbook from a template to subsequently be populated--

  ...
  % Excel named constants w/o include file available
  xlWorkbookDefault    =51;       % default Excel workbook type ('xlsx')
  xlLocalSessionChanges= 2;       % accept user changes unequivocally
  
  isActive=false; % leave active session alone if is already open
  try    % If Excel is NOT running, throw error to the catch block.
    Excel = actxGetRunningServer('Excel.Application');
  catch
    Excel = actxserver('Excel.Application');   
    isActive=true;
  end
  Excel.Workbooks.Open(fullfile(templatedir,'Reconciliation.xltx'));      % open the template
  WBk=Excel.ActiveWorkbook;                                               % make active
  Filename=fullfile(outdir,'Reconciliation.xlsx');                        % make a .xlsx file to update
  FileFormat=xlWorkbookDefault;                                           % is the default format
  ConflictResolution=xlLocalSessionChanges;                               % suppress popup user dialog

  % Can't use named parameters with invoke(), apparently, have to count placeholders
  % Syntax from <https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.open>
  %expression.SaveAs( _FileName_ , _FileFormat_ , _Password_ , _WriteResPassword_ , ...
  %                  _ReadOnlyRecommended_ , _CreateBackup_ , _AccessMode_ , _ConflictResolution_ , ...
  %                  _AddToMru_ , _TextCodepage_ , _TextVisualLayout_ , _Local_)
  % expression is a Workbook object
  invoke(WBk,'SaveAs',Filename,FileFormat,[],[],[],[],[],ConflictResolution)
  WBk.Close
  ...

works quite nicely. All the parameter values are constants and passed by position.

If the parameter is another object, however, I've been unable to decipher how to pass to the method, The next step after the above is that there is a need to create a variable number of pages of the template depending on number of cases to be analyzed. I've run into an impasse at this point, however

<https://docs.microsoft.com/en-us/office/vba/api/excel.sheets.copy> shows syntax as

expression. Copy( _Before_ , _After_ )

expression A variable that represents a Sheets object.

where the parameter is set to the target of which sheet it is that is to be either before or after. The VBA example code is

Worksheets("Sheet1").Copy After:=Worksheets("Sheet3")

But I can't figure out any syntax that works that lets me specify the argument...

actSheet=Workbook.ActiveSheet;

I figured by similarity

invoke(actSheet,'Copy','Before',actSheet)

would be piece of cake but

K>> invoke(actSheet,'Copy','Before',actSheet)
Error using Interface.000208D8_0000_0000_C000_000000000046/Copy
Invoke Error, Dispatch Exception:
Source: Microsoft Excel
Description: Copy method of Worksheet class failed
Help File: xlmain11.chm
Help Context ID: 0 
K>> 

I've tried every pertubation I can think of and have proven that leaving off the parameter entirely creates a copy of the page in a new workbook as is documented but I can't make it work in the same workbook.

Anybody have enough experience to know the magic incantation?

[dpb]

ADDENDUM:

Well, I don't understand all I know, but...

Turns out that

 invoke(actSheet,'Copy',actSheet)

works to insert the copy of the active sheet before the active sheet...that does what I need for the present case but it's still a mystery how to write the general case or how that, specifically, mimics the documented interface...or is it simply impossible because there is no syntax that matches, I dunno...

Answer 1

[Guillaume]

I find it's very rare that you have to use invoke. You certainly don't need to for SaveAs or Copy.

Yes, matlab does not implement named argument. (It also doesn't implement default properties forcing you to explicitly use the Item property when indexing into collections). In matlab you always have to use the positional syntax, so If you want to specify the After part for a Copy or Add, you have to pass null ([] in matlab, nothing in VB) as the first argument:

excel = actxserver('Excel.Application');
excel.Visible = true;
wb = excel.Workbooks.Add;
ws1 = wb.Worksheets.Item(1);
ws1.Range('A1').Value = 10;
ws2 = wb.Worksheets.Add([], ws1);  %Add worksheet after ws1
ws1.Copy([], ws2);           %copy ws1 after ws2. Unfortunately Copy doesn't return a reference to the copy
wb.SaveAs(fullfile(pwd, 'demo.xlsx'), 51, [], [], [], [], [], 2);

[dpb]

Thanks, Guillaume...there's some valuable examples of syntax there. I had only the few extremely simple demos to observe to try to imitate, none of which did anything more than the barest of basics of reading a writing to a cell and (maybe) saving the file.

You mention "...If you want to specify the After part for a Copy or Add, you have to pass null ([] in matlab,...". I'm still mystified by the logic therein; how does the null argument play into things and what gave you the clue that that's the case, just so I could try to puzzle future enigmas out on own?

Duh! It just dawned on me that in the doc, the expression

expression. Copy( _Before_ , _After_ )

is the positional arguments and that's what the actual interface is, two arguments, not just one. Don't know why I was so dense now in retrospect on that part.

As for invoke, that was what the demo I found on the TMW site used extensively, so I just parroted it.

I kept having other issues in various forms with the dot notation owing mostly, I'm sure to my absolute complete unfamiliarity with the Excel object model...

Thanks! I believe I just learned enough to get thru with the task at hand and then hopefully I'll not have to mess with Excel for another 20 years! :)

[dpb]

"... that's what the actual interface is, two arguments, not just one. Don't know why I was so dense now in retrospect on that part."

Well, actually I do know why--the methodsview footprint for Copy showed

Copy(hndle,[variant])

which surely had me convinced there was just the one argument besides the sheet.