http://www.winsoft.sk/officfaq.htm
Office Component Suite FAQ
Last Updated Friday January 03, 2003
Q1. How do I open an Excel application, workbook, worksheet?
Q2. How do I close an Excel application?
Q3. How do I specify a range of cells?
Q5. How do I access directly Excel application COM object?
Q6. How do I access directly Excel workbook COM object?
Q7. How do I access directly Excel worksheet COM object?
Q8. How do I access directly Excel range COM object?
Q9. How do I access directly Excel chart COM object?
Q10. How do I display note indicators?
Q11. How do I print a worksheet?
Q12. How do I copy content and format of cells?
Q13. How do I set cell borders?
Q15. How do I find and replace some text?
Q16. How do I close Excel Worksheet?
Q17. How do I save the specified Word document in HTML format?
Q18. How do I insert the current page number at the begining of each line?
Q19. How do I specify a workbook name?
Q20. How do I specify a worksheet name?
Q21. How do I draw a line in Excel worksheet?
Q22. How do I place a picture into worksheet?
Q23. How do I open a password protected workbook? New!!!
Q24. How do I add a table in the word document? New!!!
Q1. How do I open an Excel application, workbook, worksheet?
ExcelApplication.Active := True; // opens Excel application
ExcelWorkbook.Parent := ExcelApplication;
ExcelWorkbook.WorkbookName := 'C:\MyFile.xls';
ExcelWorkbook.Active := True; // opens Excel workbook
ExcelWorksheet.Parent := ExcelWorkbook;
ExcelWorksheet.WorksheetName := 'MySheet';
ExcelWorksheet.Active := True; // opens Excel worksheet
Q2. How do I close an Excel application?
ExcelApplication.Active := False;
Q3. How do I specify a range of cells?
ExcelRange.Range := 'B3'; // one cell selected
ExcelRange.Range := 'A1:B2'; // four cells A1, A2, B1, B2 selected
Q4. How do I set cell values?
ExcelRange.Formula := 3; // integer constant
ExcelRange.Formula := 'Hello, world!'; // string constant
ExcelRange.Formula := '=SUM(B1:B5)'; // formula specification
Q5. How do I access directly Excel application COM object?
use ExcelApplication property:
with ExcelApplication1.ExcelApplication.ActiveCell do
ShowMessage(IntToStr(Row) + ':' + IntToStr(Column)); // shows active cell position
Q6. How do I access directly Excel workbook COM object?
use ExcelWorkbook property:
ShowMessage(ExcelWorkbook1.ExcelWorkbook.Windows[1].Caption); // shows workbook window caption
Q7. How do I access directly Excel worksheet COM object?
use ExcelWorksheet property:
ShowMessage(IntToStr(ExcelWorksheet1.ExcelWorksheet.Comments.Count)); // shows numer of worksheet comments
Q8. How do I access directly Excel range COM object?
use ExcelRange property:
ShowMessage(IntToStr(ExcelRange1.ExcelRange.Rows.Count)); // shows numer of range rows
Q9. How do I access directly Excel chart COM object?
use ExcelChart property:
ShowMessage(ExcelChart1.ExcelChart.ChartTitle[0].Text); // shows chart title
Q10. How do I display note indicators?
ExcelApplication.DisplayNoteIndicators := True;
Note indicators are small dots in upper-right corner of cell. They display cell tips for cells containing notes.
Q11. How do I print a worksheet?
ExcelWorksheet.PrintOut(
EmptyParam, // from
EmptyParam, // to
EmptyParam, // copies
True, // preview
EmptyParam, // active printer
EmptyParam, // print to file
EmptyParam, // collate
EmptyParam); // file name for print to file
Q12. How do I copy content and format of cells?
ExcelRange.FillDown; // takes content and format of cells from top row of range and fills other rows from top to bottom
ExcelRange.FillUp; // takes content and format of cells from bottom row of range and fills other rows from bottom to top
ExcelRange.FillRight; // takes content and format of cells from left column of range and fills other columns from left to right
ExcelRange.FillLeft; // takes content and format of cells from right column of range and fills other columns from right to left
Q13. How do I set cell borders?
with ExcelRange.ExcelRange.Borders do
begin
Color := clNavy; // navy color
LineStyle := xlDouble; // double style
end;
applicable line styles are: xlContinuous, xlDash, xlDashDot, xlDashDotDot, xlDot, xlDouble, xlSlantDashDot and xlLineStyleNone
Q14. How do I set cell font?
with ExcelRange.ExcelRange.Font do
begin
Name := 'Terminal';
Color := clBlue;
Size := 12;
Italic := False;
Bold := True;
end;
Q15. How do I find and replace some text?
var
FindText: OleVariant;
ReplaceText: OleVariant;
Replace: OleVariant;
FindText := 'a';
ReplaceText := 'b';
Replace := wdReplaceOne;
with WordDocument1.WordDocument.Content do
Find.Execute(FindText, // text to find
EmptyParam, // match case
EmptyParam, // match whole word
EmptyParam, // match wildcards
EmptyParam, // match sounds like
EmptyParam, // match all word forms
EmptyParam, // forward
EmptyParam, // wrap
EmptyParam, // format
ReplaceText, // replace with
Replace, // replace
EmptyParam, // match Kashida
EmptyParam, // match diacritics
EmptyParam, // match AlefHamza
EmptyParam); // match control
Q16. How do I close Excel Worksheet?
ExcelWorkBook1.Close(False, // save changes
EmptyParam, // filename
EmptyParam); // route workbook
Q17. How do I save the specified Word document in HTML format?
var
FileName: OleVariant;
FileFormat: OleVariant;
FileName := 'c:\mydoc.html';
FileFormat := wdFormatHTML;
WordDocument1.SaveAs(FileName, // document name
FileFormat, // file format
EmptyParam, // lock the document for comments
EmptyParam, // password for opening the document
EmptyParam, // add the document to the list of recently used files
EmptyParam, // password for saving changes to the document
EmptyParam, // Word suggest read-only status
EmptyParam, // save TrueType fonts with the document
EmptyParam, // save only the Windows version of the imported graphics
EmptyParam, // save the form data
EmptyParam); // save the document as AOCE letter (the mailer is saved)
Q18. How do I insert the current page number at the begining of each line?
with WordDocument1.WordDocument.Paragraphs do
for i := 1 to Count do
with Item(i).Range do
begin
End_ := Start; // select start of range (line)
Text := IntToStr(Information[wdActiveEndAdjustedPageNumber]) + ': '; // add text
end
Q19. How do I specify a workbook name?
ExcelWorkbook1.SaveAs('MyWorkbook.xls', EmptyParam, EmptyParam, EmptyParam, EmptyParam,
EmptyParam, xlNoChange, EmptyParam, EmptyParam, EmptyParam, EmptyParam);
Q20. How do I specify a worksheet name?
ExcelWorksheet1.ExcelWorksheet.Name := 'MyWorksheet';
Q21. How do I draw a line in Excel worksheet?
// draw line from (10, 10) to (300, 300)
// X, Y measured in points
ExcelWorksheet1.ExcelWorksheet.Shapes.AddLine(10, 10, 300, 300);
Q22. How do I place a picture into worksheet?
ExcelWorksheet1.ExcelWorksheet.Shapes.AddPicture(
'C:\MyBitmap.bmp', // filename
msoFalse, // LinkToFile (True = make link to file, False = make copy of the file in document)
msoCTrue, // SaveWithDocument (must be True if LinkToFile is False)
10, // Left
10, // Top
300, // Width
300); // Height
Q23. How do I open a password protected workbook?
ExcelApplication1.ExcelApplication.Workbooks.Open(
'C:\MyWorkbook.xls', // Filename
3, // UpdateLinks
False, // ReadOnly
EmptyParam, // Format
'passwd', // Password
'passwdwrite', // WriteResPassword
True, // IgnoreReadOnlyRecommended
EmptyParam, // Origin
EmptyParam, // Delimiter
EmptyParam, // Editable
EmptyParam, // Notify
EmptyParam, // Converter
True, // AddToMru
0); // lcid
Q24. How do I add a table in the word document?
var
DefaultTableBehavior, AutoFitBehavior: OleVariant;
Table: Word.Table;
WordRange1.RangeStart := 1;
WordRange1.RangeEnd := 2;
WordRange1.Active := True;
DefaultTableBehavior := wdWord8TableBehavior;
AutoFitBehavior := wdAutoFitContent;
Table := WordApplication1.WordApplication.ActiveDocument.Tables.Add(
WordRange1.WordRange, // Range
10, // NumRows
10, // NumColumns
DefaultTableBehavior, // DefaultTableBehavior
AutoFitBehavior); // AutoFitBehavior
Table.Cell(1,1).Range.Text := 'Hello!';
Have a question? Ask here
