PhysiologyWeb Logo  Search
PhysiologyWeb Loading...

Delphi Code - Add Title, Keywords (i.e., Tags), and Category to an Excel File
Here is a simple Delphi procedure that adds title, keywords (i.e., tags), category, and company information to a Microsoft Excel file. The code uses Microsoft's Workbook.BuiltinDocumentProperties to access and edit the built-in document properties. As written, the code edits these properties, however, all the built-in document properties can also be read using a similar approach.
The built-in document properties are: Title, Subject, Author, Keywords, Comments, Template, Last author, Revision number, Application name, Last print date, Creation date, Last save time, Total editing time, Number of pages, Number of words, Number of characters, Security, Category, Format, Manager, Company, Number of bytes, Number of lines, Number of paragraphs, Number of slides, Number of notes, Number of hidden Slides, Number of multimedia clips, Hyperlink base, Number of characters (with spaces), Content type, Content status, Language, Document version.
The most relevant parts of the code are highlighted. Comments are included to explain the code. If you copy and paste the code into your program, be sure to change the form and procedure names to match your setup. For additional Excel automation solutions, see Microsoft Excel Automation with Delphi.
procedure TForm1.AddTitleKeywordsAndCategoryToAnExcelFile(Sender: TObject);
var
ExcelFileName: String;
ExcelApplication, ExcelWorkbook, WorkbookProperties: Variant;
begin
//be sure ComObj and Variants units are included in the "uses" clause

ExcelFileName := 'C:\PhysiologyWeb\delphi_code_examples\excel_file.xlsx'; //replace file name with the name of your file

ExcelApplication := Null;
ExcelWorkbook := Null;
WorkbookProperties := Null;

try
//create Excel OLE
ExcelApplication := CreateOleObject('Excel.Application');
except
ExcelApplication := Null;
//add error/exception handling code as desired
end;

If VarIsNull(ExcelApplication) = False then
begin
try
ExcelApplication.Visible := True; //set to False if you do not want to see the activity in the background
ExcelApplication.DisplayAlerts := False; //ensures message dialogs do not interrupt the flow of your automation process. May be helpful to set to True during testing and debugging.

//Open Excel Workbook
try
ExcelWorkbook := ExcelApplication.Workbooks.Open(ExcelFileName);
except
ExcelWorkbook := Null;
//add error/exception handling code as desired
end;

If VarIsNull(ExcelWorkbook) = False then
begin
//get BuiltinDocumentProperties
try
WorkbookProperties := ExcelWorkbook.BuiltinDocumentProperties;
except
WorkbookProperties := Null;
//add error/exception handling code as desired
end;

If VarIsNull(WorkbookProperties) = False then
begin
WorkbookProperties.Item['Title'].Value := 'Delphi Code Examples';
WorkbookProperties.Item['Keywords'].Value := 'Delphi, Object Pascal, Code'; //this is also referred to as the "tag" property
WorkbookProperties.Item['Category'].Value := 'Delphi Programming';
WorkbookProperties.Item['Company'].Value := 'PhysiologyWeb';

//use WorkbookProperties.Count to parse through all BuiltinDocumentProperties (Name/Value) and read/write as desired
//for a complete list of BuiltinDocumentProperties, see reference below
//https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.tools.excel.workbook.builtindocumentproperties?view=vsto-2017

ExcelWorkbook.Save;
//or
//ExcelApplication.WorkBooks[1].Save;
end;
end;
finally
ExcelApplication.Workbooks.Close;
ExcelApplication.DisplayAlerts := True;
ExcelApplication.Quit;

WorkbookProperties := Unassigned;
ExcelWorkbook := Unassigned;
ExcelApplication := Unassigned;
end;
end;
end;






Posted: Saturday, December 5, 2020
Last updated: Thursday, December 24, 2020