procedure TForm1.MicrosoftExcelAccountingFormatting(Sender: TObject);
var
ExcelFileName: String;
ExcelApplication, ExcelWorkbook, ExcelWorksheet: Variant;
begin
//be sure ComObj and Variants units are included in the "uses" clause
ExcelFileName := 'C:\PhysiologyWeb\delphi_code_examples\excel_file_accounting_formatting.xlsx'; //replace file name with the name of your file
ExcelApplication := Null;
ExcelWorkbook := Null;
ExcelWorksheet := 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.Add(-4167);
//reference
//https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.add
//https://docs.microsoft.com/en-us/office/vba/api/excel.xlsheettype
except
ExcelWorkbook := Null;
//add error/exception handling code as desired
end;
If VarIsNull(ExcelWorkbook) = False then
begin
//connect to Excel Worksheet using either the ExcelApplication or ExcelWorkbook handle
try
ExcelWorksheet := ExcelWorkbook.WorkSheets[1]; //[1] specifies the first worksheet
//reference
//https://docs.microsoft.com/en-us/office/vba/api/Excel.Worksheets.Item
except
ExcelWorksheet := Null;
//add error/exception handling code as desired
end;
If VarIsNull(ExcelWorksheet) = False then
begin
ExcelWorksheet.Select;
//add text to A1 cell [row,column]
ExcelWorksheet.Cells[1,1] := 'Microsoft Excel - Cell Number Formatting - Accounting Formatting';
//change the font size in A1 cell
ExcelWorksheet.Cells[1,1].Font.Size := 20;
//make A1 cell font bold
ExcelWorksheet.Cells[1,1].Font.Bold := True;
//change font color to blue
ExcelWorksheet.Cells[1,1].Font.Color := clBlue;
//add text to A3 cell [row,column]
ExcelWorksheet.Cells[3,1] := 'Hold your mouse over cells with numbers to see comments.';
//add text to A5 cell [row,column]
ExcelWorksheet.Cells[5,1] := 'Accounting Format';
ExcelWorksheet.Cells[5,1].Font.Bold := True;
ExcelWorksheet.Cells[5,1].Interior.Color := clAqua;
//add text to B5 cell [row,column]
ExcelWorksheet.Cells[5,2] := 'Format Code';
ExcelWorksheet.Cells[5,2].Font.Bold := True;
ExcelWorksheet.Cells[5,2].Interior.Color := clAqua;
//add text to C5 cell [row,column]
ExcelWorksheet.Cells[5,3] := 'Format Description';
ExcelWorksheet.Cells[5,3].Font.Bold := True;
ExcelWorksheet.Cells[5,3].Interior.Color := clAqua;
//add number, code, and description to row 6 [row,column]
ExcelWorksheet.Cells[6,1] := '45.79';
ExcelWorksheet.Cells[6,1].AddComment('No number formatting specified for entered number (45.79).');
ExcelWorksheet.Cells[6,3] := 'No number formatting specified for entered number (45.79).';
//add number, code, and description to row 7 [row,column]
ExcelWorksheet.Cells[7,1] := '45.79';
ExcelWorksheet.Cells[7,1].NumberFormat := '$* #,##0.00';
ExcelWorksheet.Cells[7,1].AddComment('Number format: $* #,##0.00 (two decimals) for entered number (45.79). Simple format. Align $ to the left.');
ExcelWorksheet.Cells[7,2] := 'Format code: $* #,##0.00';
ExcelWorksheet.Cells[7,3] := 'Number format: $* #,##0.00 (two decimals) for entered number (45.79). Simple format. Align $ to the left.';
//add number, code, and description to row 8 [row,column]
ExcelWorksheet.Cells[8,1] := '45.79';
ExcelWorksheet.Cells[8,1].NumberFormat := '_($* #,##0.00_);_($* (#,##0.00)';
ExcelWorksheet.Cells[8,1].AddComment('Number format: _($* #,##0.00_);_($* (#,##0.00) (two decimals) for entered number (45.79). Align $ to the left. Parentheses for negative. Decimal point is aligned.');
ExcelWorksheet.Cells[8,2] := 'Format code: _($* #,##0.00_);_($* (#,##0.00)';
ExcelWorksheet.Cells[8,3] := 'Number format: _($* #,##0.00_);_($* (#,##0.00) (two decimals) for entered number (45.79). Align $ to the left. Parentheses for negative. Decimal point is aligned.';
//add number, code, and description to row 9 [row,column]
ExcelWorksheet.Cells[9,1] := '-45.79';
ExcelWorksheet.Cells[9,1].NumberFormat := '_($* #,##0.00_);_($* (#,##0.00)';
ExcelWorksheet.Cells[9,1].AddComment('Number format: _($* #,##0.00_);_($* (#,##0.00) (two decimals) for entered number (-45.79). Align $ to the left. Parentheses for negative. Decimal point is aligned.');
ExcelWorksheet.Cells[9,2] := 'Format code: _($* #,##0.00_);_($* (#,##0.00)';
ExcelWorksheet.Cells[9,3] := 'Number format: _($* #,##0.00_);_($* (#,##0.00) (two decimals) for entered number (-45.79). Align $ to the left. Parentheses for negative. Decimal point is aligned.';
//add number, code, and description to row 10 [row,column]
ExcelWorksheet.Cells[10,1] := '-45.79';
ExcelWorksheet.Cells[10,1].NumberFormat := '$* #,##0.00;[red]$* #,##0.00';
ExcelWorksheet.Cells[10,1].AddComment('Number format: $* #,##0.00;[red]$* #,##0.00 (two decimals) for entered number (-45.79). Align $ to the left. Red for negative.');
ExcelWorksheet.Cells[10,2] := 'Format code: $* #,##0.00;[red]$* #,##0.00';
ExcelWorksheet.Cells[10,3] := 'Number format: $* #,##0.00;[red]$* #,##0.00 (two decimals) for entered number (-45.79). Align $ to the left. Red for negative.';
//add number, code, and description to row 11 [row,column]
ExcelWorksheet.Cells[11,1] := '-45.79';
ExcelWorksheet.Cells[11,1].NumberFormat := '_($* #,##0.00_);[red]_($* (#,##0.00)';
ExcelWorksheet.Cells[11,1].AddComment('Number format: _($* #,##0.00_);[red]_($* (#,##0.00) (two decimals) for entered number (-45.79). Align $ to the left. Parentheses and red for negative. Decimal point is aligned.');
ExcelWorksheet.Cells[11,2] := 'Format code: _($* #,##0.00_);[red]_($* (#,##0.00)';
ExcelWorksheet.Cells[11,3] := 'Number format: _($* #,##0.00_);[red]_($* (#,##0.00) (two decimals) for entered number (-45.79). Align $ to the left. Parentheses and red for negative. Decimal point is aligned.';
//add number, code, and description to row 12 [row,column]
ExcelWorksheet.Cells[12,1] := '-45.79';
ExcelWorksheet.Cells[12,1].NumberFormat := '_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)';
ExcelWorksheet.Cells[12,1].AddComment('Number format: _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_) (two decimals) for entered number (-45.79). Parentheses for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[12,2] := 'Format code: _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)';
ExcelWorksheet.Cells[12,3] := 'Number format: _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_) (two decimals) for entered number (-45.79). Parentheses for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 13 [row,column]
ExcelWorksheet.Cells[13,1] := '-45.79';
ExcelWorksheet.Cells[13,1].NumberFormat := '_($* #,##0.00_);[red]_($* (#,##0.00);_($* "-"??_);_(@_)';
ExcelWorksheet.Cells[13,1].AddComment('Number format: _($* #,##0.00_);[red]_($* (#,##0.00);_($* "-"??_);_(@_) (two decimals) for entered number (-45.79). Parentheses and red for negative. Dash (-) for zero.');
ExcelWorksheet.Cells[13,2] := 'Format code: _($* #,##0.00_);[red]_($* (#,##0.00);_($* "-"??_);_(@_)';
ExcelWorksheet.Cells[13,3] := 'Number format: _($* #,##0.00_);[red]_($* (#,##0.00);_($* "-"??_);_(@_) (two decimals) for entered number (-45.79). Parentheses and red for negative. Dash (-) for zero.';
//add text and formatting to row 15 [row,column]
ExcelWorksheet.Cells[15,1] := 'Specifying the Currency';
ExcelWorksheet.Cells[15,1].Font.Bold := True;
ExcelWorksheet.Cells[15,1].Interior.Color := clSilver;
ExcelWorksheet.Cells[15,2].Interior.Color := clSilver;
ExcelWorksheet.Cells[15,3].Interior.Color := clSilver;
//add number, code, and description to row 16 [row,column]
ExcelWorksheet.Cells[16,1] := '-45.79';
ExcelWorksheet.Cells[16,1].NumberFormat := '_([$$-en-US]* #,##0.00_);_([$$-en-US]* (#,##0.00);_([$$-en-US]* "-"??_);_(@_)';
ExcelWorksheet.Cells[16,1].AddComment('Number format: _([$$-en-US]* #,##0.00_);_([$$-en-US]* (#,##0.00);_([$$-en-US]* "-"??_);_(@_) (two decimals) for entered number (-45.79). US $. Parentheses for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[16,2] := 'Format code: _([$$-en-US]* #,##0.00_);_([$$-en-US]* (#,##0.00);_([$$-en-US]* "-"??_);_(@_)';
ExcelWorksheet.Cells[16,3] := 'Number format: _([$$-en-US]* #,##0.00_);_([$$-en-US]* (#,##0.00);_([$$-en-US]* "-"??_);_(@_) (two decimals) for entered number (-45.79). US $. Parentheses for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 17 [row,column]
ExcelWorksheet.Cells[17,1] := '-45.79';
ExcelWorksheet.Cells[17,1].NumberFormat := '_([$$-en-US]* #,##0.00_);[red]_([$$-en-US]* (#,##0.00);_([$$-en-US]* "-"??_);_(@_)';
ExcelWorksheet.Cells[17,1].AddComment('Number format: _([$$-en-US]* #,##0.00_);[red]_([$$-en-US]* (#,##0.00);_([$$-en-US]* "-"??_);_(@_) (two decimals) for entered number (-45.79). US $. Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[17,2] := 'Format code: _([$$-en-US]* #,##0.00_);[red]_([$$-en-US]* (#,##0.00);_([$$-en-US]* "-"??_);_(@_)';
ExcelWorksheet.Cells[17,3] := 'Number format: _([$$-en-US]* #,##0.00_);[red]_([$$-en-US]* (#,##0.00);_([$$-en-US]* "-"??_);_(@_) (two decimals) for entered number (-45.79). US $. Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 18 [row,column]
ExcelWorksheet.Cells[18,1] := '45.79';
ExcelWorksheet.Cells[18,1].NumberFormat := '_([$USD]* #,##0.00_);_([$USD]* (#,##0.00);_([$USD]* "-"??_);_(@_)';
ExcelWorksheet.Cells[18,1].AddComment('Number format: _($* #,##0.00_);[red]_($* (#,##0.00);_($* "-"??_);_(@_) (two decimals) for entered number (45.79). US Dollar (USD). Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[18,2] := 'Format code: _($* #,##0.00_);[red]_($* (#,##0.00);_($* "-"??_);_(@_)';
ExcelWorksheet.Cells[18,3] := 'Number format: _($* #,##0.00_);[red]_($* (#,##0.00);_($* "-"??_);_(@_) (two decimals) for entered number (45.79). US Dollar (USD). Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 19 [row,column]
ExcelWorksheet.Cells[19,1] := '-45.79';
ExcelWorksheet.Cells[19,1].NumberFormat := '_([$USD]* #,##0.00_);[red]_([$USD]* (#,##0.00);_([$USD]* "-"??_);_(@_)';
ExcelWorksheet.Cells[19,1].AddComment('Number format: _($* #,##0.00_);[red]_($* (#,##0.00);_($* "-"??_);_(@_) (two decimals) for entered number (-45.79). US Dollar (USD). Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[19,2] := 'Format code: _($* #,##0.00_);[red]_($* (#,##0.00);_($* "-"??_);_(@_)';
ExcelWorksheet.Cells[19,3] := 'Number format: _($* #,##0.00_);[red]_($* (#,##0.00);_($* "-"??_);_(@_) (two decimals) for entered number (-45.79). US Dollar (USD). Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 20 [row,column]
ExcelWorksheet.Cells[20,1] := '45.79';
ExcelWorksheet.Cells[20,1].NumberFormat := '_([$€-2]* #,##0.00_);_([$€-2]* (#,##0.00);_([$€-2]* "-"??_);_(@_)';
ExcelWorksheet.Cells[20,1].AddComment('Number format: _([$€-2]* #,##0.00_);_([$€-2]* (#,##0.00);_([$€-2]* "-"??_);_(@_) (two decimals) for entered number (45.79). € Euro (€ 123). Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[20,2] := 'Format code: _([$€-2]* #,##0.00_);_([$€-2]* (#,##0.00);_([$€-2]* "-"??_);_(@_)';
ExcelWorksheet.Cells[20,3] := 'Number format: _([$€-2]* #,##0.00_);_([$€-2]* (#,##0.00);_([$€-2]* "-"??_);_(@_) (two decimals) for entered number (45.79). € Euro (€ 123). Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 21 [row,column]
ExcelWorksheet.Cells[21,1] := '-45.79';
ExcelWorksheet.Cells[21,1].NumberFormat := '_([$€-2]* #,##0.00_);[red]_([$€-2]* (#,##0.00);_([$€-2]* "-"??_);_(@_)';
ExcelWorksheet.Cells[21,1].AddComment('Number format: _([$€-2]* #,##0.00_);[red]_([$€-2]* (#,##0.00);_([$€-2]* "-"??_);_(@_) (two decimals) for entered number (-45.79). € Euro (€ 123). Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[21,2] := 'Format code: _([$€-2]* #,##0.00_);[red]_([$€-2]* (#,##0.00);_([$€-2]* "-"??_);_(@_)';
ExcelWorksheet.Cells[21,3] := 'Number format: _([$€-2]* #,##0.00_);[red]_([$€-2]* (#,##0.00);_([$€-2]* "-"??_);_(@_) (two decimals) for entered number (-45.79). € Euro (€ 123). Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 22 [row,column]
ExcelWorksheet.Cells[22,1] := '45.79';
ExcelWorksheet.Cells[22,1].NumberFormat := '_([$€-1]* #,##0.00_);_([$€-1]* (#,##0.00);_([$€-1]* "-"??_);_(@_)';
ExcelWorksheet.Cells[22,1].AddComment('Number format: _([$€-1]* #,##0.00_);_([$€-1]* (#,##0.00);_([$€-1]* "-"??_);_(@_) (two decimals) for entered number (45.79). € Euro (123 €). Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[22,2] := 'Format code: _([$€-1]* #,##0.00_);_([$€-1]* (#,##0.00);_([$€-1]* "-"??_);_(@_)';
ExcelWorksheet.Cells[22,3] := 'Number format: _([$€-1]* #,##0.00_);_([$€-1]* (#,##0.00);_([$€-1]* "-"??_);_(@_) (two decimals) for entered number (45.79). € Euro (123 €). Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 23 [row,column]
ExcelWorksheet.Cells[23,1] := '-45.79';
ExcelWorksheet.Cells[23,1].NumberFormat := '_([$€-1]* #,##0.00_);[red]_([$€-1]* (#,##0.00);_([$€-1]* "-"??_);_(@_)';
ExcelWorksheet.Cells[23,1].AddComment('Number format: _([$€-1]* #,##0.00_);[red]_([$€-1]* (#,##0.00);_([$€-1]* "-"??_);_(@_) (two decimals) for entered number (-45.79). € Euro (123 €). Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[23,2] := 'Format code: _([$€-1]* #,##0.00_);[red]_([$€-1]* (#,##0.00);_([$€-1]* "-"??_);_(@_)';
ExcelWorksheet.Cells[23,3] := 'Number format: _([$€-1]* #,##0.00_);[red]_([$€-1]* (#,##0.00);_([$€-1]* "-"??_);_(@_) (two decimals) for entered number (-45.79). € Euro (123 €). Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 24 [row,column]
ExcelWorksheet.Cells[24,1] := '45.79';
ExcelWorksheet.Cells[24,1].NumberFormat := '_([$EUR]* #,##0.00_);_([$EUR]* (#,##0.00);_([$EUR]* "-"??_);_(@_)';
ExcelWorksheet.Cells[24,1].AddComment('Number format: _([$EUR]* #,##0.00_);_([$EUR]* (#,##0.00);_([$EUR]* "-"??_);_(@_) (two decimals) for entered number (45.79). EUR for Euro. Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[24,2] := 'Format code: _([$EUR]* #,##0.00_);_([$EUR]* (#,##0.00);_([$EUR]* "-"??_);_(@_)';
ExcelWorksheet.Cells[24,3] := 'Number format: _([$EUR]* #,##0.00_);_([$EUR]* (#,##0.00);_([$EUR]* "-"??_);_(@_) (two decimals) for entered number (45.79). EUR for Euro. Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 25 [row,column]
ExcelWorksheet.Cells[25,1] := '-45.79';
ExcelWorksheet.Cells[25,1].NumberFormat := '_([$EUR]* #,##0.00_);[red]_([$EUR]* (#,##0.00);_([$EUR]* "-"??_);_(@_)';
ExcelWorksheet.Cells[25,1].AddComment('Number format: _([$EUR]* #,##0.00_);[red]_([$EUR]* (#,##0.00);_([$EUR]* "-"??_);_(@_) (two decimals) for entered number (-45.79). EUR for Euro. Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[25,2] := 'Format code: _([$EUR]* #,##0.00_);[red]_([$EUR]* (#,##0.00);_([$EUR]* "-"??_);_(@_)';
ExcelWorksheet.Cells[25,3] := 'Number format: _([$EUR]* #,##0.00_);[red]_([$EUR]* (#,##0.00);_([$EUR]* "-"??_);_(@_) (two decimals) for entered number (-45.79). EUR for Euro. Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 26 [row,column]
ExcelWorksheet.Cells[26,1] := '45.79';
ExcelWorksheet.Cells[26,1].NumberFormat := '_([$CAD]* #,##0.00_);_([$CAD]* (#,##0.00);_([$CAD]* "-"??_);_(@_)';
ExcelWorksheet.Cells[26,1].AddComment('Number format: _([$CAD]* #,##0.00_);_([$CAD]* (#,##0.00);_([$CAD]* "-"??_);_(@_) (two decimals) for entered number (45.79). Canadian Dollar. Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[26,2] := 'Format code: _([$CAD]* #,##0.00_);_([$CAD]* (#,##0.00);_([$CAD]* "-"??_);_(@_)';
ExcelWorksheet.Cells[26,3] := 'Number format: _([$CAD]* #,##0.00_);_([$CAD]* (#,##0.00);_([$CAD]* "-"??_);_(@_) (two decimals) for entered number (45.79). Canadian Dollar. Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 27 [row,column]
ExcelWorksheet.Cells[27,1] := '-45.79';
ExcelWorksheet.Cells[27,1].NumberFormat := '_([$CAD]* #,##0.00_);[red]_([$CAD]* (#,##0.00);_([$CAD]* "-"??_);_(@_)';
ExcelWorksheet.Cells[27,1].AddComment('Number format: _([$CAD]* #,##0.00_);[red]_([$CAD]* (#,##0.00);_([$CAD]* "-"??_);_(@_) (two decimals) for entered number (-45.79). Canadian Dollar. Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[27,2] := 'Format code: _([$CAD]* #,##0.00_);[red]_([$CAD]* (#,##0.00);_([$CAD]* "-"??_);_(@_)';
ExcelWorksheet.Cells[27,3] := 'Number format: _([$CAD]* #,##0.00_);[red]_([$CAD]* (#,##0.00);_([$CAD]* "-"??_);_(@_) (two decimals) for entered number (-45.79). Canadian Dollar. Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 28 [row,column]
ExcelWorksheet.Cells[28,1] := '45.79';
ExcelWorksheet.Cells[28,1].NumberFormat := '_([$AUD]* #,##0.00_);_([$AUD]* (#,##0.00);_([$AUD]* "-"??_);_(@_)';
ExcelWorksheet.Cells[28,1].AddComment('Number format: _([$AUD]* #,##0.00_);_([$AUD]* (#,##0.00);_([$AUD]* "-"??_);_(@_) (two decimals) for entered number (45.79). Australian Dollar. Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[28,2] := 'Format code: _([$AUD]* #,##0.00_);_([$AUD]* (#,##0.00);_([$AUD]* "-"??_);_(@_)';
ExcelWorksheet.Cells[28,3] := 'Number format: _([$AUD]* #,##0.00_);_([$AUD]* (#,##0.00);_([$AUD]* "-"??_);_(@_) (two decimals) for entered number (45.79). Australian Dollar. Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 29 [row,column]
ExcelWorksheet.Cells[29,1] := '-45.79';
ExcelWorksheet.Cells[29,1].NumberFormat := '_([$AUD]* #,##0.00_);[red]_([$AUD]* (#,##0.00);_([$AUD]* "-"??_);_(@_)';
ExcelWorksheet.Cells[29,1].AddComment('Number format: _([$AUD]* #,##0.00_);[red]_([$AUD]* (#,##0.00);_([$AUD]* "-"??_);_(@_) (two decimals) for entered number (-45.79). Australian Dollar. Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[29,2] := 'Format code: _([$AUD]* #,##0.00_);[red]_([$AUD]* (#,##0.00);_([$AUD]* "-"??_);_(@_)';
ExcelWorksheet.Cells[29,3] := 'Number format: _([$AUD]* #,##0.00_);[red]_([$AUD]* (#,##0.00);_([$AUD]* "-"??_);_(@_) (two decimals) for entered number (-45.79). Australian Dollar. Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 30 [row,column]
ExcelWorksheet.Cells[30,1] := '45.79';
ExcelWorksheet.Cells[30,1].NumberFormat := '_([$NZD]* #,##0.00_);_([$NZD]* (#,##0.00);_([$NZD]* "-"??_);_(@_)';
ExcelWorksheet.Cells[30,1].AddComment('Number format: _([$NZD]* #,##0.00_);_([$NZD]* (#,##0.00);_([$NZD]* "-"??_);_(@_) (two decimals) for entered number (45.79). New Zealand Dollar. Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[30,2] := 'Format code: _([$NZD]* #,##0.00_);_([$NZD]* (#,##0.00);_([$NZD]* "-"??_);_(@_)';
ExcelWorksheet.Cells[30,3] := 'Number format: _([$NZD]* #,##0.00_);_([$NZD]* (#,##0.00);_([$NZD]* "-"??_);_(@_) (two decimals) for entered number (45.79). New Zealand Dollar. Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 31 [row,column]
ExcelWorksheet.Cells[31,1] := '-45.79';
ExcelWorksheet.Cells[31,1].NumberFormat := '_([$NZD]* #,##0.00_);[red]_([$NZD]* (#,##0.00);_([$NZD]* "-"??_);_(@_)';
ExcelWorksheet.Cells[31,1].AddComment('Number format: _([$NZD]* #,##0.00_);[red]_([$NZD]* (#,##0.00);_([$NZD]* "-"??_);_(@_) (two decimals) for entered number (-45.79). New Zealand Dollar. Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[31,2] := 'Format code: _([$NZD]* #,##0.00_);[red]_([$NZD]* (#,##0.00);_([$NZD]* "-"??_);_(@_)';
ExcelWorksheet.Cells[31,3] := 'Number format: _([$NZD]* #,##0.00_);[red]_([$NZD]* (#,##0.00);_([$NZD]* "-"??_);_(@_) (two decimals) for entered number (-45.79). New Zealand Dollar. Parentheses and red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 32 [row,column]
ExcelWorksheet.Cells[32,1] := '45.79';
ExcelWorksheet.Cells[32,1].NumberFormat := '_([$GBP]* #,##0.00_);_([$GBP]* (#,##0.00);_([$GBP]* "-"??_);_(@_)';
ExcelWorksheet.Cells[32,1].AddComment('Number format: _([$GBP]* #,##0.00_);_([$GBP]* (#,##0.00);_([$GBP]* "-"??_);_(@_) (two decimals) for entered number (45.79). British Pound Sterling. Parentheses/red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[32,2] := 'Format code: _([$GBP]* #,##0.00_);_([$GBP]* (#,##0.00);_([$GBP]* "-"??_);_(@_)';
ExcelWorksheet.Cells[32,3] := 'Number format: _([$GBP]* #,##0.00_);_([$GBP]* (#,##0.00);_([$GBP]* "-"??_);_(@_) (two decimals) for entered number (45.79). British Pound Sterling. Parentheses/red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 33 [row,column]
ExcelWorksheet.Cells[33,1] := '-45.79';
ExcelWorksheet.Cells[33,1].NumberFormat := '_([$GBP]* #,##0.00_);[red]_([$GBP]* (#,##0.00);_([$GBP]* "-"??_);_(@_)';
ExcelWorksheet.Cells[33,1].AddComment('Number format: _([$GBP]* #,##0.00_);[red]_([$GBP]* (#,##0.00);_([$GBP]* "-"??_);_(@_) (two decimals) for entered number (-45.79). British Pound Sterling. Parentheses/red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[33,2] := 'Format code: _([$GBP]* #,##0.00_);[red]_([$GBP]* (#,##0.00);_([$GBP]* "-"??_);_(@_)';
ExcelWorksheet.Cells[33,3] := 'Number format: _([$GBP]* #,##0.00_);[red]_([$GBP]* (#,##0.00);_([$GBP]* "-"??_);_(@_) (two decimals) for entered number (-45.79). British Pound Sterling. Parentheses/red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 34 [row,column]
ExcelWorksheet.Cells[34,1] := '45.79';
ExcelWorksheet.Cells[34,1].NumberFormat := '_([$DEM]* #,##0.00_);_([$DEM]* (#,##0.00);_([$DEM]* "-"??_);_(@_)';
ExcelWorksheet.Cells[34,1].AddComment('Number format: _([$DEM]* #,##0.00_);_([$DEM]* (#,##0.00);_([$DEM]* "-"??_);_(@_) (two decimals) for entered number (45.79). German Deutsche Mark. Parentheses/red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[34,2] := 'Format code: _([$DEM]* #,##0.00_);_([$DEM]* (#,##0.00);_([$DEM]* "-"??_);_(@_)';
ExcelWorksheet.Cells[34,3] := 'Number format: _([$DEM]* #,##0.00_);_([$DEM]* (#,##0.00);_([$DEM]* "-"??_);_(@_) (two decimals) for entered number (45.79). German Deutsche Mark. Parentheses/red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 35 [row,column]
ExcelWorksheet.Cells[35,1] := '-45.79';
ExcelWorksheet.Cells[35,1].NumberFormat := '_([$DEM]* #,##0.00_);[red]_([$DEM]* (#,##0.00);_([$DEM]* "-"??_);_(@_)';
ExcelWorksheet.Cells[35,1].AddComment('Number format: _([$DEM]* #,##0.00_);[red]_([$DEM]* (#,##0.00);_([$DEM]* "-"??_);_(@_) (two decimals) for entered number (-45.79). German Deutsche Mark. Parentheses/red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[35,2] := 'Format code: _([$DEM]* #,##0.00_);[red]_([$DEM]* (#,##0.00);_([$DEM]* "-"??_);_(@_)';
ExcelWorksheet.Cells[35,3] := 'Number format: _([$DEM]* #,##0.00_);[red]_([$DEM]* (#,##0.00);_([$DEM]* "-"??_);_(@_) (two decimals) for entered number (-45.79). German Deutsche Mark. Parentheses/red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 36 [row,column]
ExcelWorksheet.Cells[36,1] := '45.79';
ExcelWorksheet.Cells[36,1].NumberFormat := '_([$JPY]* #,##0.00_);_([$JPY]* (#,##0.00);_([$JPY]* "-"??_);_(@_)';
ExcelWorksheet.Cells[36,1].AddComment('Number format: _([$JPY]* #,##0.00_);_([$JPY]* (#,##0.00);_([$JPY]* "-"??_);_(@_) (two decimals) for entered number (45.79). Japanese Yen. Parentheses/red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[36,2] := 'Format code: _([$JPY]* #,##0.00_);_([$JPY]* (#,##0.00);_([$JPY]* "-"??_);_(@_)';
ExcelWorksheet.Cells[36,3] := 'Number format: _([$JPY]* #,##0.00_);_([$JPY]* (#,##0.00);_([$JPY]* "-"??_);_(@_) (two decimals) for entered number (45.79). Japanese Yen. Parentheses/red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 37 [row,column]
ExcelWorksheet.Cells[37,1] := '-45.79';
ExcelWorksheet.Cells[37,1].NumberFormat := '_([$JPY]* #,##0.00_);[red]_([$JPY]* (#,##0.00);_([$JPY]* "-"??_);_(@_)';
ExcelWorksheet.Cells[37,1].AddComment('Number format: _([$JPY]* #,##0.00_);[red]_([$JPY]* (#,##0.00);_([$JPY]* "-"??_);_(@_) (two decimals) for entered number (-45.79). Japanese Yen. Parentheses/red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[37,2] := 'Format code: _([$JPY]* #,##0.00_);[red]_([$JPY]* (#,##0.00);_([$JPY]* "-"??_);_(@_)';
ExcelWorksheet.Cells[37,3] := 'Number format: _([$JPY]* #,##0.00_);[red]_([$JPY]* (#,##0.00);_([$JPY]* "-"??_);_(@_) (two decimals) for entered number (-45.79). Japanese Yen. Parentheses/red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 38 [row,column]
ExcelWorksheet.Cells[38,1] := '45.79';
ExcelWorksheet.Cells[38,1].NumberFormat := '_([$CNY]* #,##0.00_);_([$CNY]* (#,##0.00);_([$CNY]* "-"??_);_(@_)';
ExcelWorksheet.Cells[38,1].AddComment('Number format: _([$CNY]* #,##0.00_);_([$CNY]* (#,##0.00);_([$CNY]* "-"??_);_(@_) (two decimals) for entered number (45.79). Chinese Yuan. Parentheses/red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[38,2] := 'Format code: _([$CNY]* #,##0.00_);_([$CNY]* (#,##0.00);_([$CNY]* "-"??_);_(@_)';
ExcelWorksheet.Cells[38,3] := 'Number format: _([$CNY]* #,##0.00_);_([$CNY]* (#,##0.00);_([$CNY]* "-"??_);_(@_) (two decimals) for entered number (45.79). Chinese Yuan. Parentheses/red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 39 [row,column]
ExcelWorksheet.Cells[39,1] := '-45.79';
ExcelWorksheet.Cells[39,1].NumberFormat := '_([$CNY]* #,##0.00_);[red]_([$CNY]* (#,##0.00);_([$CNY]* "-"??_);_(@_)';
ExcelWorksheet.Cells[39,1].AddComment('Number format: _([$CNY]* #,##0.00_);[red]_([$CNY]* (#,##0.00);_([$CNY]* "-"??_);_(@_) (two decimals) for entered number (-45.79). Chinese Yuan. Parentheses/red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.');
ExcelWorksheet.Cells[39,2] := 'Format code: _([$CNY]* #,##0.00_);[red]_([$CNY]* (#,##0.00);_([$CNY]* "-"??_);_(@_)';
ExcelWorksheet.Cells[39,3] := 'Number format: _([$CNY]* #,##0.00_);[red]_([$CNY]* (#,##0.00);_([$CNY]* "-"??_);_(@_) (two decimals) for entered number (-45.79). Chinese Yuan. Parentheses/red for negative. Dash (-) for zero. Excel recognizes this as native Accounting format.';
//add number, code, and description to row 40 [row,column]
ExcelWorksheet.Cells[40,1] := '45.79';
ExcelWorksheet.Cells[40,1].NumberFormat := '_([$£-809]* #,##0.00_);_([$£-809]* (#,##0.00);_([$£-809]* "-"??_);_(@_)';
ExcelWorksheet.Cells[40,1].AddComment('Number format: _([$£-809]* #,##0.00_);_([$£-809]* (#,##0.00);_([$£-809]* "-"??_);_(@_) (two decimals) for entered number (45.79). £ English (United Kingdom). Parentheses/red for negative. Dash (-) for zero. Recognized as Excel native Accounting format.');
ExcelWorksheet.Cells[40,2] := 'Format code: _([$£-809]* #,##0.00_);_([$£-809]* (#,##0.00);_([$£-809]* "-"??_);_(@_)';
ExcelWorksheet.Cells[40,3] := 'Number format: _([$£-809]* #,##0.00_);_([$£-809]* (#,##0.00);_([$£-809]* "-"??_);_(@_) (two decimals) for entered number (45.79). £ English (United Kingdom). Parentheses/red for negative. Dash (-) for zero. Recognized as Excel native Accounting format.';
//add number, code, and description to row 41 [row,column]
ExcelWorksheet.Cells[41,1] := '-45.79';
ExcelWorksheet.Cells[41,1].NumberFormat := '_([$£-809]* #,##0.00_);[red]_([$£-809]* (#,##0.00);_([$£-809]* "-"??_);_(@_)';
ExcelWorksheet.Cells[41,1].AddComment('Number format: _([$£-809]* #,##0.00_);[red]_([$£-809]* (#,##0.00);_([$£-809]* "-"??_);_(@_) (two decimals) for entered number (-45.79). £ English (United Kingdom). Parentheses/red for negative. Dash (-) for zero. Native Excel Accounting format.');
ExcelWorksheet.Cells[41,2] := 'Format code: _([$£-809]* #,##0.00_);[red]_([$£-809]* (#,##0.00);_([$£-809]* "-"??_);_(@_)';
ExcelWorksheet.Cells[41,3] := 'Number format: _([$£-809]* #,##0.00_);[red]_([$£-809]* (#,##0.00);_([$£-809]* "-"??_);_(@_) (two decimals) for entered number (-45.79). £ English (United Kingdom). Parentheses/red for negative. Dash (-) for zero. Native Excel Accounting format.';
//references
//https://learn.microsoft.com/en-us/office/vba/api/excel.cellformat.numberformat
//https://learn.microsoft.com/en-us/office/vba/api/excel.range.numberformat
//https://support.microsoft.com/en-us/office/number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68
//https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.numberingformat
//https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.numberingformats
//https://learn.microsoft.com/en-us/windows-hardware/manufacture/desktop/default-input-locales-for-windows-language-packs?view=windows-11
//autofit the width of Column A
ExcelWorksheet.Columns[1].ColumnWidth := 35; //sets width of Column A
ExcelWorksheet.Columns[2].EntireColumn.AutoFit; //autofits Column A
ExcelWorksheet.Columns[3].EntireColumn.AutoFit; //autofits Column A
//reference
//https://learn.microsoft.com/en-us/office/vba/api/excel.range.columnwidth
//https://docs.microsoft.com/en-us/office/vba/api/excel.range.autofit
//select B1 cell
ExcelWorksheet.Cells[2,1].Select; //selects Cell B1
ExcelWorkbook.SaveAs(ExcelFileName);
//Note: If a file with the new name already exists, it overwrites it. Write additional code to address as desired.
//reference
//https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.saveas
end;
end;
finally
ExcelApplication.Workbooks.Close;
ExcelApplication.DisplayAlerts := True;
ExcelApplication.Quit;
ExcelWorksheet := Unassigned;
ExcelWorkbook := Unassigned;
ExcelApplication := Unassigned;
end;
end;
end;