excel automation field formatting

has anybody good examples for field formatting in excel ? all my trys ended with some error messages … i want to change the format in currency or something else … has anybody also an example for making border a using the automation ?

Var Sheet ‘Microsoft Excel 9.0 Object Library’.Worksheet Sheet.Range(‘A1:B10’).Borders.Item(7).Linestyle := 1; //Left border Sheet.Range(‘A1:B10’).Borders.Item(8).Linestyle := 1; //Top border Sheet.Range(‘A1:B10’).Borders.Item(9).Linestyle := 1; //Bottom border Sheet.Range(‘A1:B10’).Borders.Item(10).Linestyle := 1; //Right border Sheet.Range(‘A1:B10’).Borders.Item(12).Linestyle := 1; //Horizontal border Sheet.Range(‘A1:B1’).Numberformat := ‘#,##0’; // 0 decimals Sheet.Range(‘A2:B2’).Numberformat := ‘#,##0.00’; // 2 decimals Sheet.Range(‘A3:B3’).Numberformat := ‘@’; // Text

Look at this source XLWorksheet.Range(‘A’+FORMAT(Excelzeile)+’:M’+FORMAT Excelzeile)).BorderAround(1,2,-4105); //Rahmen xlrange:=XLWorksheet.Columns.Item(12); //Spalte 12 zentriert xlrange.HorizontalAlignment := -4108; xlrange:=XLWorksheet.Columns.Item(3); //Spalte 3 linksbündig xlrange.HorizontalAlignment := -4131; xlrange:=XLWorksheet.Columns.Item(3); //Spalte 3 Zeilenumbruch xlrange.WrapText := TRUE; xlrange:=XLWorksheet.Columns.Item(6); //Spalte 3 linksbündig xlrange.HorizontalAlignment := -4131; xlrange:=XLWorksheet.Columns.Item(1); //Spalte 1 linksbündig xlrange.HorizontalAlignment := -4131; xlrange:=XLWorksheet.Columns.Item(2); //Spalte 2 linksbündig xlrange.HorizontalAlignment := -4131; xlrange:=XLWorksheet.Columns.Item(4); //Spalte 4 linksbündig xlrange.HorizontalAlignment := -4131; xlrange:=XLWorksheet.Columns.Item(5); //Spalte 5 linksbündig xlrange.HorizontalAlignment := -4131; xlrange:=XLWorksheet.Columns.Item(7); //Spalte 7 linksbündig xlrange.HorizontalAlignment := -4131; xlrange:=XLWorksheet.Columns.Item(9); //Spalte 9 linksbündig xlrange.HorizontalAlignment := -4131; xlrange:=XLWorksheet.Columns.Item(7); //Spalte 7 Zeilenumbruch xlrange.WrapText := TRUE; xlrange:=XLWorksheet.Columns.Item(10); //Spalte 10 Zahlenformat xlrange.NumberFormat := ‘0,00’; xlrange:=XLWorksheet.Columns.Item(11); //Spalte 11 Zahlenformat xlrange.NumberFormat := ‘0,00’; xlrange:=XLWorksheet.Columns.Item(13); //Spalte 13 zentriert xlrange.HorizontalAlignment := -4108; FOR ii := 1 TO 14 DO BEGIN xlrange:=XLWorksheet.Columns.Item(ii); xlrange.Font.Size := 8; //Schriftgröße 8 xlrange.VerticalAlignment := -4160; //Ausrichtung oben END; XLWorksheet.Range(‘a1:m1’).Font.Bold := 1; //fett XLWorksheet.Range(‘a1:m1’).Font.Underline := 2; //unterstrichen XLWorksheet.Range(‘a1:m1’).WrapText := TRUE; //Zeilenumbruch XLWorksheet.Range(‘a1:m1’).VerticalAlignment := -4107; //zentriert END; best regards Jochem

quote:


Originally posted by pduck
has anybody good examples for field formatting in excel ? all my trys ended with some error messages … i want to change the format in currency or something else … has anybody also an example for making border a using the automation ?


You should search the forum and I’m sure you’ll find a lot of examples [;)]

does anyone successfully used the .numberformat - property in excel 97 (ms excel 8.0 object) ?? i get an error that this property can’t be reached out of navision …

quote:


Originally posted by pduck
does anyone successfully used the .numberformat - property in excel 97 (ms excel 8.0 object) ?? i get an error that this property can’t be reached out of navision …


Just to be sure. Did you change , and . in your code for the German version? ‘#.##0,00’ insted of ‘#,##0.00’ bye André

quote:


Originally posted by pduck
does anyone successfully used the .numberformat - property in excel 97 (ms excel 8.0 object) ?? i get an error that this property can’t be reached out of navision …


I used this property quite a lot - as Andre already said, watch out for German vs. English format elements. I would like to add that it’s not only , and . but also [Red] vs. [Rot] for negative values - this caused me some headaches a while ago [B)]

this is my line tabellenblatt.Range(‘g1’).NumberFormat := ‘TT.MM.JJJJ’;

quote:


Originally posted by pduck
this is my line tabellenblatt.Range(‘g1’).NumberFormat := ‘TT.MM.JJJJ’;


Just tried it here - no problem. I have German Excel 2000, though. A 97 version is not available any more [B)] What is your locale setting for date? Are you sure . is recognized as separator?