Excel Merge and center

chad

Hi to all,

i have a problem in nav, i created a report that exports to excel which i have to merge and center the title column for every specific details. does anyone know how to do such thing here in nav? a sample code will be very much appreciated for i dnt have any idea how to do this. [-o<

Thanks!
chad

kine

Just do what you want in Excel, record it as macro and look at the code. Than you have example of the code you need... ;-)

chad

hi kine,

thx for the reply but i really dnt have any idea in macro i am not very used with excel. ](*,) i hope that there's someone here who could post regarding my problem. have u ever tried to do such thing like mine?

garak

Little Tip:

Open Excel -> Start Macro Recording -> Do this what Navision should do -> Stop macro recording -> View the VBA source.
These vb code you must "transfer" (not directly) to your C/AL Code. for this, take a look to the excel buffer table in NAV. This is the central function to fill excel.
Here you can add your source.

Also read the VBA Help for your excel version (if it is not on your computer, then it is on your Office product cd and you can install it).
For an example read this (there is the MS Access help file shown): viewtopic.php?f=1&t=31344

regards

chad

thx garak for you response,

i have just saw the code thru macro, but does range in excel buffer exist? so how will i convert the macro code to c/al if theres no range in excel buffer any idea?

here is the code that i generate:

Range("B1:C1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge

kine

In Excel Buffer is code like:

    IF Formula = '' THEN
      XlWrkSht.Range(xlColID + xlRowID).Value := "Cell Value as Text"
    ELSE
      XlWrkSht.Range(xlColID + xlRowID).Formula := GetFormula;
    IF Comment <> '' THEN
      XlWrkSht.Range(xlColID + xlRowID).AddComment := Comment;
    IF Bold THEN
      XlWrkSht.Range(xlColID + xlRowID).Font.Bold := Bold;
    IF Italic THEN
      XlWrkSht.Range(xlColID + xlRowID).Font.Italic := Italic;

As you can see, the range is used there, all the properties are available (you do not need to use Select as in the macro, which first selected the area and then worked with the selected area). The constants like xlBottom you can find e.g. there: http://techsupt.winbatch.com/ts/T000001033005F9.html You need to use the number instead the constant.

chad

this is what i wanted to do :

ExcelBuf.AddColumn('PURCHASES',FALSE,'',TRUE,FALSE,TRUE,'');
ExcelBuf.AddColumn('',FALSE,'',TRUE,FALSE,TRUE,'');
ExcelBuf.AddColumn('',FALSE,'',TRUE,FALSE,TRUE,'');
ExcelBuf.AddColumn('',FALSE,'',TRUE,FALSE,TRUE,'');

i want to merge the three columns in my presentation in excel sheet but how will i do such thing if theres no range in excel buffer? i have not used the automation of excel for your info. #-o

chad

this is what i wanted to do :

ExcelBuf.AddColumn('PURCHASES',FALSE,'',TRUE,FALSE,TRUE,'');
ExcelBuf.AddColumn('',FALSE,'',TRUE,FALSE,TRUE,'');
ExcelBuf.AddColumn('',FALSE,'',TRUE,FALSE,TRUE,'');
ExcelBuf.AddColumn('',FALSE,'',TRUE,FALSE,TRUE,'');

i want to merge the three columns in my presentation in excel sheet but how will i do such thing if theres no range in excel buffer? i have not used the automation of excel for your info. #-o

kine

You need to extend the excelbuffer for new function and do it inside... ;-) inside the Range is available...

chad

how will i extend the excel buffer to a function so that i have a range? i dnt have any idea ](*,) ](*,)

do i need to declare a new excelbuffer? :?:

chad

in excel buffer there are functions start range, endrange, autofit(rangename). does this relevance with the cell merging? :-k

kine

1) What is your job? Are you end-user or developer for partner?
2) Autofit is function which will autofit e.g. column to values inside. You need to create something similar, only the code will be different because it will call another functions over range. Just look into the code of the function.

chad

developer. not so familiar with the excelbuffer and its capables.

kine

chad wrote:

developer. not so familiar with the excelbuffer and its capables.

Than it is time to learn something to be more familiar with it... ;-) Look at the code, study it, try to understand how it work, than it will be easy to add what you need... 8)

garak

Also you can search the forum for excel buffer. there are many questions about it, and there is often the question: How to use the excel buffer and how to expand it.

Regards

chad

thx i already found the answer to my question hehe..got some idea on excel buffer..