Delphi Win32 COM Excel problem

[Smithy1001]

Hi,

I am using D2010, Win32, COM to communicate with Excel. The plan for my app is to have an excel template in a folder on our server which is pre-formatted, to open that file, copy the formatting to an open workbook which I create on the fly and then close the template. This gets round the need to copy the template to a location and re-open it, I just have it open but as say Book1 unsaved.
How do I do this?
I was planning on copying a Wbk sheet by sheet but I cannot get the .copy fn to work. So the question is, how do I open a wbk, copy it completely to an open fresh Wbk, then close the original?
Thanks

 

[DjangMan]

The trick with dealing with Excel and Delphi via COM when you're using Early Binding is that you have to code in all of the parameters when calling Excel. If you're using Late Binding you get a break on that.

When I'm working with Excel I have Excel record a macro of the process. Then I look up the function calls that it recorded to see what all of the other parameters are. From there you should be able to do your coding.

Here is a clip of some code that I use to open an Excel template:

function OpenExcelBook(const TemplateName: string; aForEditing: boolean = false):boolean;

      XLBook := XLApp.Workbooks.Open(TemplateName, '0', False,
                   EmptyParam, EmptyParam, EmptyParam,
                   EmptyParam, EmptyParam, EmptyParam,
                   OpenForEditing, EmptyParam, EmptyParam,
                   EmptyParam, EmptyParam, EmptyParam, LCID);

Copying a worksheet within a single book:

procedure TdmXL.CopySheet(const aSourceSheet, aAfterSheet: integer);
var
 tempXLSheet: _Worksheet;

begin
  tempXLSheet := XLBook.Sheets.Item[aSourceSheet] as _Worksheet;
  if (aSourceSheet <= aAfterSheet) then
  begin
    tempXLSheet.Copy(EmptyParam,XLBook.Sheets.Item[aAfterSheet] as _Worksheet,LCID);
    tempXLSheet := XLBook.Sheets.Item[aAfterSheet+1] as _Worksheet; //the new sheet
    
  end;

end;

 

[Smithy1001]

Hi, thanka for the information but I am using late binding and my question is
How do I copy a template workbook to an open fresh blank workbook using late binding? This then enables me to work on a workbook that is open, not presaved and does not need to be saved to a local drive in advance?
Thanks

 

[DjangMan]

Try recording a macro in Excel and then adapt the code.