Excel Format with Automation

Stefan_Limpert

Hello again,
I want to move some statistics to Excel. My Problem is, if i move a large Integer value, for an example "Serialno", Excel converts the Value into Power. It's only possible when i format the Columns manually to Text, before i move the value to the columns. Did anybody know how i format the Cell or Column in a "Text Format" with Automation Controller?

Thanx a lot

XYLAZEL

Hi Stefan,

Maybe this piece of code can help you.

Sorry some words are in spanish.

...

CREATE(l_autApp);
l_autApp.Visible(TRUE);
l_autBook := l_autApp.Workbooks.Add;
l_autSheet := l_autBook.ActiveSheet;

l_autSheet.Range('A:A').NumberFormat := '@';
l_autSheet.Range('A:A').ColumnWidth := 15;
l_autSheet.Range('B:B').NumberFormat := '@';
l_autSheet.Range('C:C').NumberFormat := '@';
l_autSheet.Range('C:C').ColumnWidth := 15;
l_autSheet.Range('D ').ColumnWidth := 40;
l_autSheet.Range('E:E').NumberFormat := '@';
l_autSheet.Range('E:E').ColumnWidth := 15;
l_autSheet.Range('F:F').NumberFormat := '#.##0;-#.##0';
l_autSheet.Range('G:G').NumberFormat := '#.##0;-#.##0';
l_autSheet.Range('H:H').NumberFormat := '#.##0;-#.##0';

l_autSheet.Range('1:1').Font.Bold := TRUE;
l_autSheet.Range('3:3').Font.Bold := TRUE;

l_autSheet.Range('A1').Offset(0,0).Value := STRSUBSTNO('Tarifa de precios (%1) cliente: %2 %3',
l_dateTarifa, p_recCliente."Nº", p_recCliente.Nombre);

l_autSheet.Range('A1').Offset(2,0).Value := 'Grupo';
l_autSheet.Range('A1').Offset(2,1).Value := 'Código';
l_autSheet.Range('A1').Offset(2,2).Value := 'EAN';
l_autSheet.Range('A1').Offset(2,3).Value := 'Descripción';
l_autSheet.Range('A1').Offset(2,4).Value := 'Presentación';
l_autSheet.Range('A1').Offset(2,5).Value := 'Und/Caja';
l_autSheet.Range('A1').Offset(2,6).Value := 'Pedido min';
l_autSheet.Range('A1').Offset(2,7).Value := 'Precio';
...

---

[This message has been edited by XYLAZEL (edited 08-02-2001).]

Luc_VanDyck

When you pass the integer-value to Excel, using the FORMAT instruction to convert this first to a string-value, you can precede this value with a single quote '. Then Excel knows this value is a text-value instead of an integer-value.

mfabian

The following idea has been prooved very useful: Instead of exporting the data into a "naked" Excel-Worksheet which you create, you can open an existing empty worksheet (like a Template) and fill in the data. Usually I proceed as follows:

1) Create a new unformated Worksheet like you do now.
2) Format the Worksheet (Column-Widths, Font, size, format etc.) 3) clear all data and save the empty but formated Worksheet as template.

More functionality cann be added in Step 2. For example you can put a chart or pivot table on a second worksheet within the same workbook etc.

The key of course is that the resulting file will be saved as different filename so the Template is not being changed.

One more positive side-effect is that you can layouting of the Template to the end-user.

Hope this helps

Marcus

---

Marcus Fabian
m.fabian@thenet.ch
+41 79 439 78 72

Stefan_Limpert

Thanx my friends
all these advises were very useful. My Problem is solved.