1 Reply Latest reply on Sep 5, 2006 4:49 PM by Dinghus

    cfobject and Excel

    codeliquid
      Really hoping someone can help me on this one....... I am trying to export query data to Excel using cfobject, creating the spreadsheet is fine and even formatting the cell borders, backgrounds etc but when I try and set the value for a cell range I keep getting the following error

      AutomationException: 0x800a03ec -

      Please can someone help on this as I have spent the last day or so browsing the web and so many of the examples just do not seem to work.

      Here is my code below

      Regards,
      Leigh

      <cfquery name="monkey" datasource="training">
      SELECT * FROM orders

      </cfquery>

      <CFTRY>
      <!--- If it exists, connect to it --->
      <CFOBJECT
      ACTION="CONNECT"
      CLASS="Excel.Application"
      NAME="objExcel"
      TYPE="COM">
      <CFCATCH>
      <!--- The object doesn't exist, so create it --->
      <CFOBJECT
      ACTION="CREATE"
      CLASS="Excel.Application"
      NAME="objExcel"
      TYPE="COM">
      </CFCATCH>
      </CFTRY>

      <cfscript>

      // Open Excel in the background
      objExcel.Visible = true;



      // Disable client alerts such as: 'Save this workbook?'
      objExcel.DisplayAlerts = false;

      // Define the workbooks object
      objWorkBook = objExcel.Workbooks;

      // Add a new workbook
      objOpenedBook = objWorkBook.Add();

      // Get the WorkSheets' collection
      objWorkSheets = objExcel.WorkSheets;

      // Add a new worksheet (this will contain our data)
      objWorkSheet = objWorkSheets;

      // Add a new worksheet (this will contain our data)
      objWorkSheet = objWorkSheets.Add();


      //Create object Interior for solid field some color
      objRange = objExcel.Range("A1:K1");
      objI = objRange.Interior;
      objI.ColorIndex = 15;

      objRange = objExcel.Range("A1:K1");
      objIn_V = objRange.Interior;
      objIn_V.ColorIndex = 12;
      objV = objRange.Font;
      objV.Size = 08;
      objRange.HorizontalAlignment = 3;
      objRange.VerticalAlignment = 1;
      objLine = objRange.Borders;
      objLine.LineStyle = 1;
      objRange.WrapText = True;
      objRange.ShrinkToFit = True;


      objRange = objExcel.Range("A3:A3");
      objRange.value = 15;

      objWorkSheet.SaveAs("c:\templates\testfile5.xls",Val(1));


      // Close the document
      objWorkBook.Close();

      // Quit Excel
      objExcel.Quit();

      // Release the object
      objExcel = "Nothing";




      </cfscript>