Copying Rows in Excel via Excel Buffer

Lilithyan

Hi,

I've got an rather unusal request from a customer and am not quite sure how to proceed.

The customer wants and export to excel for specific Sales Lines. Since there a quite a lot of Borderlines and at least three different font sizes it was decided, that a template for Excel would be used which then would be filled with data from the Excel Buffer table in NAV.

Now that is no problem as I have already done something similar for another customer and know what to allow the standard Excel Buffer Funktions to do and not to do (e.g. no Autofit an no changing the linesstyle, etc.).

The Customer wishes to print out the Excelsheet. If there are to many lines that they couldn't be printed out on one page, the template design, fitted for a one page print (with header and footer, borderlines, etc.), is to be compied below the already existing lines on the same sheet.

If I were to do this manually in Excel, I would mark the apropiate lines 1 to x, copy then, go to the beginning of line x+1 and paste them there.

But how do I do this from NAV with the functionality the Excel Buffer gives me?

I found for the different Automations (XlWrkSht, XlRange, etc.) some copy methods, but from their shown parameters they don't seem to be what I need.

Been at this the whole day and my google-fu hasn't helped me so far.

Anyone got a solution or an idea which I can expand on?

Thanks in advance!

Ciao,

Lilithyan

kine

Try to record macro for this when you are doing it manually and then look into the code and you can create your code in NAV based on this macro...

lubost

Hallo Lilithyan,

I think you can only change the teplate setting to repeat a portion of ines on each page and you don't need copy header lines again and again.

Lilithyan

Thanks for the Help!

I tried it with the Macros idea.

While the paste portion is possible, I got a hiccup with the actual copying.

In the Macro the lines for copying is as followed:

Rows("BeginRow:Endrow").Select
Selection.Copy

While the automations in Excel Buffer do have a Rows() method, it is without parameters in each and every one, meaning that I can't insert the ("BeginRow:Endrow").

I thought I had it with these two lines bellow:

XlWrkSht.Range(FromCell + ':' + ToCell).Copy;
XlWrkSht.Paste(XlWrkSht.Range(CopytoCell));

The Problem with this one is, that by only copying a certain range and not the whole Rows, the Rows to not take the same height when pasted as the ones they were copied from.

E.g.: The standard Excel row is 17 pixel high but the ones I want to copy are only 15 pixel. If I manually do what the macro lines did the pasted rows would also be 15 pixel. If I do it the way I have currently programmed it in Navision, then the pasted rows will be 17 pixel high.

Thoughts?

Many thanks again!

Ciao,

Lilithyan

kine

Can you enter insted fromcell:tocell in the Range something like row:row? (instead "A12:B32" use "$1:$5")?

Lilithyan

It worked!

Thanks so much!

kine

You are welcome!