PhysiologyWeb Logo  Search
Share on Facebook Share on Twitter Share on LinkedIn Share on Pinterest Email Copy URL
PhysiologyWeb Loading...

Delphi Code - Microsoft Excel Accounting Formatting
Here is a simple Delphi procedure that opens a new Microsoft Excel file (i.e., Excel workbook) and demonstrates how to apply a variety of accounting formatting options. The file is then saved. 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. We recommend that you run the code and open and examine the Excel file generated. For additional Excel automation solutions, see Microsoft Excel Automation with Delphi.
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;






Posted: Saturday, December 3, 2022