I have to copy something from the windows clipboard to Excel and then format
it in Excel.

I have written some code which works fine with Excel 2000 but falls over
with Excel for Office XP.

The code connects to an excel worksheet, copies the clipboard data to an
empty worksheet.

I then format the spreadsheet with a macro which is stored in a delphi memo
component by linking to
a codemodule.

Works very wll with Excel 200 but fails in XP version. The data copies Ok
but it seems as if the macro
is just not copied.

I've tried turning off macro security in Excel XP but same problem.

Here's my code, any ideas???

Ian

code follows:

procedure CopyClipboardToExcel(var ExcelApplication:TExcelApplication;var
ExcelWorkbook:TExcelWorkbook;var
ExcelWorksheet:TExcelWorksheet;DoesNotApply:string);
var  LCID,LineNo:Integer;
     CM: CodeModule;
     counter:integer;
begin
LCID:=GetUserDefaultLCID;
Excelapplication.visible[LCID]:=TRUE;
application.processmessages;
Excelapplication.Workbooks.Add(EmptyParam,LCID);
application.processmessages;
ExcelWorkbook.ConnectTo(Excelapplication.ActiveWorkbook);
application.processmessages;
ExcelWorkSheet.ConnectTo(ExcelWorkbook.ActiveSheet as _Worksheet);
application.processmessages;
application.processmessages;
ExcelWorksheet.Range['A1','A1'].Select;
application.processmessages;
ExcelWorksheet.Paste(EmptyParam,EmptyParam,LCID);
application.processmessages;
try
  {This bit reads lines stored as strings in a memo compenent and writes
them
  to an excel macro}
  LockWindowUpdate(Excelapplication.VBE.MainWindow.HWND);
  CM :=
ExcelWorkbook.VBProject.VBComponents.Item('ThisWorkbook').Codemodule;
  LineNo := CM.CreateEventProc('Activate', 'Workbook');
  Application.CreateForm(THTML, HTML);
  CM.InsertLines(LineNo +1 , 'DoesNotApply="'+DoesNotApply+'"');
  for counter:=0 to HTML.EXcelMacro.Lines.count-1 do
    begin
    if Trim(HTML.EXcelMacro.Lines[counter])<>'' then
      CM.InsertLines(LineNo + counter+2, HTML.EXcelMacro.Lines[counter]);
    end;

  excelapplication.VBE.MainWindow.visible:=FALSE;
  try
    {stops inevitable errors showing when we delete connected workbook to
get rid of macro}
    Excelapplication.Run('ThisWorkbook.Workbook_Activate');
    except
  end;
finally
  LockWindowUpdate(0);
  application.processmessages;
  HTML.close;
ExcelWorksheet.Disconnect;
ExcelWorkbook.Disconnect;
{necessary because resetting it in the excel macro doesn't seem to work}
ExcelApplication.DisplayAlerts[LCID] := TRUE;
ExcelApplication.Disconnect;
end;