HOWTO: Example to retain leading zeros in Excel

The information in this article applies to:

ASNA Visual RPG, Release 3.0 and higher
Windows
Microsoft Excel

SUMMARY:

This article will discuss a way in which you can retain the leading zeros in Excel from AVR.

NOTE: Figure 1 below shows a form allowing users to send a number to Excel.

Figure 1

MORE INFORMATION:

NOTE: These methods listed below are examples. There might be other methods in which this feature can be incorporated into an AVR application that is not mentioned in this article.

Requirements:

An AVR Form
Microsoft Excel installed

Program details:

NOTE: Please make a reference to the Microsoft Excel Object. Once this object is selected, it will be a requirement on each client's PC. Pay note to the version number, as this version will also be expected on the client's machine when deploying.

This example assumes a form has one IOField. This IOField will allow a user to enter a number that may contain leading zeros:

Note you will to at least one IOFields (see Figure 2 for the IOFields Properties) and a command button and name it btnCancel.

Figure 2

Here is the code that is needed for Figure 1:

//Mainline code:
DCLFLD xlApp   TYPE(Excel.Application) DCLFLD xlBook TYPE(Excel.Workbook) DCLFLD xlSheet TYPE(Excel.Worksheet) DCLFLD xlRange TYPE(Excel.Range) DCLFLD FileEOF TYPE(*IND) DCLFLD FileERR TYPE(*IND) DCLFLD NumRecs TYPE(*Zoned) LEN(4,0) DCLFLD Row     TYPE(*Zoned) LEN(4,0) // Center up the form *thisform.left = (*SCREEN.Width - *thisform.Width) / 2 *thisform.top = (*SCREEN.Height - *thisform.Height) / 2 // -------------------------------------------------------------------- BEGSR NAME(cmdCreate) EVENT(Click)    NumRecs = IONumRecs    xlApp.visible=*ON    xlBook=xlApp.Workbooks.Add()    xlbook.sheets['Sheet1'].Name = 'Customers' // Change the worksheet name just for kicks    xlapp.cells[1,1] = 'Our Customers' // Give it some title    xlrange=xlapp.Range['A1', 'E1']    xlrange.select

/*

xlapp.selection will create an instance of an object. This will allow us to pass several Interface Items, such as the cell formatting, etc..

*/    xlapp.selection.horizontalalignment = -4108 // Merge and center title    xlapp.selection.merge()    xlapp.cells[2,1] = 'Number' // Using numerics for row & column, instead of A1, B1...    xlrange=xlapp.Range['A1','E3'] // We have to use String values here (see Object Browser    xlrange.select // EXCEL, WORKSHEET, RANGE class for info)    xlapp.selection.horizontalalignment = -4108 // -4108 = Center align (xlCenter constant in VBA)    xlapp.selection.font.Bold = 1 // NOTE: using *ON does not work    xlapp.selection.font.name = 'Arial'    xlapp.selection.font.size = 12

// We will format the cell that we will write below to have a max length of 5    xlapp.selection.numberformat = "00000"    xlapp.selection.interior.colorindex = 15 // light grey    xlapp.selection.interior.pattern = 1 // xlSolid    xlapp.selection.borders.linestyle = 1 // Continuous line    xlapp.selection.borders.weight = 3 // Medium weight      // Read in and place the data    row = 3 // Start writing data on row 3 of worksheet    xlapp.cells[3,1] = IONumRecs // We will pass the number in the IOField to Excel    MSGBOX MSG('DONE!') TITLE('Excel Example') ENDSR // -------------------------------------------------------------------- BEGSR btnCancel Click    Unload *thisform ENDSR // --------------------------------------------------------------------

Figure 3 demonstrates the the result for the Cell Format after the AVR example application has run..

Figure 3

Note the Sample Frame. This will allow you to preview the number as you change the Type. You can use this to see how many zeros you will need.