Excel Buffer, Print Line on every Page

cure

Hi there
I'm using ExcelBuffer for Export Data to Excel. How can I define a Line, witch will be printed on Every Page (in German called "Drucktitel").

Thanks for any answer!

best regards

cure

tinoruijs

If you mean a Header on each page;
When you call the function CreateSheet in table 370, the second parameter ReportHeader can be used.
Look for "XlWrkSht.PageSetup.LeftHeader" in the code of table 370.

richtsang

actually, I am doing the same thing to my client now. what I am doing is using an excel template. In navision, refer to account schedule, there is create workbook and update workbook.

1. I create an excel template
2. in the excel, I use "print title" function in excel to include the line need to print every page
3. in table 370, I create a new function called, updatesheet. And, I copy from the createsheet function.
4. I remove autofit
5. update the excel buffer to the worksheet.

my suggestion: when defining the excel template, print and check the result with testing data before you import the data from excel buffer.

cure

Thank you very much for your reply. What I'm looking for is not the Header information, but the Print Title as richtsang has described on point 2.

Can I set a "Print Title" for a line without using an Excel Template?

Thanks again
cure

cure

Anything new on this topic??

Thankyou again!! :P

tinoruijs

I guess it has to be something like:

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$27"
.PrintTitleColumns = ""

I recorded a macro in Excel and modified the page-setup. Then I looked at the code that Visual Basic recorded.
If this is not the answer to your question, you can record your own macro in Excel and do the things you want to do and take a look at the VBA-code.
Next you have to use this code in NAV.

cure

Hey Tino

Thank you very much for your info. With this, it was really easy to make the change in NAV 4.0.

I've done it like that (maybe it helps anyone else....)

Table 370 (Excel Buffer), Function "CreateSheet"
Add new Parameter for this Function "SetPrintTitleLineNo" as Integer

Add the Code to Function

...

XlWrkSht.PageSetup.RightHeader :=
  STRSUBSTNO(Text006,GetExcelReference(2),GetExcelReference(3),CRLF,UserID2);
XlWrkSht.PageSetup.Orientation := XlLandscape;

// New Code   Set PrintTitel 0 = No Print Title   > 0 = Line for Print Title
IF SetPrintTitleLineNo <> 0 THEN BEGIN
  XlWrkSht.PageSetup.PrintTitleRows := '$' + FORMAT(SetPrintTitleLineNo) + ':$1';
  XlWrkSht.PageSetup.PrintTitleColumns := '';
END;
// New Code END

...

best regards to all NAV experts and comming up experts!! \:D/

cure

tinoruijs

Thanks for the feedback!
I'm going to bookmark this topic.

By the way: I don't think you have to use XlWrkSht.PageSetup.PrintTitleColumns := '';

cure

yeah, that could be right. But now it works, and I think: "never change a running System"!

But now my customer has a new wish. Can I format a Cell to the Right (called Rechtsbünding in German).

And can I hide the Grid by NAV Code?

have a nice weekend
bye, curdin

tinoruijs

cure wrote:

But now my customer has a new wish. Can I format a Cell to the Right (called Rechtsbünding in German).

And can I hide the Grid by NAV Code?

Never tried it, but I guess when you record a macro and take a look at the vba-code, you could use this code in NAV. Like with the titles.

cure

VB - Macro looks like that

With Selection
  .HorizontalAligment = xlRight
  .VerticalAlignment = xlBottom

...

So I tried the same in NAV

...
IF FormatRight THEN
  XlWrkSht.Range(xlColID + xlRowID).HorizontalAlignment := 'Right';
...

FormatRight is a new Boolean Field in T370!

Unfortunately Excel has not so much fun with this line. The Error is

The call to ember HorizontalAlignment failed!

Then in German: Die HorizontalAlignment Eigenschaft des Range Objektes kann nicht festgelegt werden!

OK, I now, that my NAV Code is really ugly. Maybe someone knows it better?