Excel questions

[leekn23]

1. I would like to put a heading on top of each page. How can I find out if the next line is in the next page in excel and what row is it? Or is it any other way I can put the heading?

2. I'm having trouble with borders. I would like to set 2 different thickness of the line for two boxes. Can anyone give me a quick example for 2 different thickness?

 

[rainylsh]

1.You can count lines displayed, when line num le someone num, page, show title, assign line num eq 0, and then go on.
2. I want know also.

 

[sphipp]

2. I'm having trouble with borders. I would like to set 2 different thickness of the line for two boxes. Can anyone give me a quick example for 2 different thickness?

To create a border (Code Snippet Only):

    def var cell_range as char initial "A1:C5" no-undo.
    def var iLineStyle as int no-undo.
 
    assign iLineStyle = -4119. /* Double */
    assign iLineStyle = 1. /* Single */
 
    excelAppl:Range(cell_range):select ().
    excelAppl:Selection:borders:linestyle = iLineStyle.

To change the weighting of the border (Code Snippet Only):

    def var cell_range as char initial "A1:C5" no-undo.
    def var iBorderWeight as int no-undo.
 
    assign iBorderWeight = 1. /* Light */
    assign iBorderWeight = 2. /* Medium */
    assign iBorderWeight = 3. /* Thick */
    assign iBorderWeight = 4. /* Very Thick */
 
    excelAppl:Range(cell_range):select ().
    excelAppl:Selection:borders:weight = iBorderWeight.

This assumes you have created your excelAppl Com-Handle and opened/created your spreadsheet.

 

[leekn23]

Thank you for the answer:

rainlysh: I don't know if line count is going to work in excel because if you change the setting of the printer, the cell you going to use will be different unless I force a page break (if any in excel). I want to try to use heading in excel but I'm still looking for the command ... I know I'm slow ...

sphipp: thx alot! It works great

 

[sphipp]

Thank you for the answer:

rainlysh: I don't know if line count is going to work in excel because if you change the setting of the printer, the cell you going to use will be different unless I force a page break (if any in excel). I want to try to use heading in excel but I'm still looking for the command ... I know I'm slow ...

If you just want a heading to appear at the top of the page you can do it fairly easily without using COM Objects.

Create a blank spreadsheet, Choose File-Page Setup and click on the "Sheet" Tab. Change Rows to Repeat At Atop to how many rows you want in the header ($1:$2 for the first two rows, $1:$3 for the first three rows etc) and press OK.

Save the spreadsheet with a meaningful name such as 2HeaderRows.xls.

When you want to create a new Spreadsheet with COM Objects, first copy 2HeaderRows.xls to a new file, open this new file and do your COM Manipulations, making sure you put something in rows 1 and 2 and then save it off.

If you look at the Print Preview you will see that the Spreadsheet has kept the Page Setup information and has used the first 2 rows as headers.

OK, it isn't a COM Objects solution but you can use the same techniques to pre-prepare blank templates for heavily formatted spreadsheets without resorting to complex COM Manipulation, as long as you know which templates to use in which situation.

 

[leekn23]

If you just want a heading to appear at the top of the page you can do it fairly easily without using COM Objects.

Create a blank spreadsheet, Choose File-Page Setup and click on the "Sheet" Tab. Change Rows to Repeat At Atop to how many rows you want in the header ($1:$2 for the first two rows, $1:$3 for the first three rows etc) and press OK.

Save the spreadsheet with a meaningful name such as 2HeaderRows.xls.

When you want to create a new Spreadsheet with COM Objects, first copy 2HeaderRows.xls to a new file, open this new file and do your COM Manipulations, making sure you put something in rows 1 and 2 and then save it off.

If you look at the Print Preview you will see that the Spreadsheet has kept the Page Setup information and has used the first 2 rows as headers.

OK, it isn't a COM Objects solution but you can use the same techniques to pre-prepare blank templates for heavily formatted spreadsheets without resorting to complex COM Manipulation, as long as you know which templates to use in which situation.

YES! Thats exectly what I want. Never think about doing it in the template side ... Thank you very much Sphipp

 

[leekn23]

Unfortunealy, the template is setted by others and I asked my boss and I cannot change it.

I tried to copy for the templete but I copy most of the details except the heading (column width and row height also)

maybe you can take a look of the code:

DEFINE VAR chExcelAppl AS COM-HANDLE NO-UNDO.
DEFINE VAR chWorkbook AS COM-HANDLE NO-UNDO.
DEFINE VAR chWorksheet AS COM-HANDLE NO-UNDO.

/*template */
DEFINE VAR chExcelApplicationori AS COM-HANDLE NO-UNDO.
DEFINE VAR chWorkbookori AS COM-HANDLE NO-UNDO.
DEFINE VAR chWorksheetori AS COM-HANDLE NO-UNDO.

CREATE "Excel.Application" chExcelAppl.
CREATE "Excel.Application" chExcelApplicationori.

chWorkbookori = chExcelApplicationori:Workbooks:Open("temp.xls").

chWorkSheetori = chExcelApplicationori:Sheets:Item(1).
chExcelApplicationori:Cells():SELECT().
chExcelApplicationori:SELECTION():COPY().

chWorkbook = chExcelAppl:Workbooks:Add().
chWorkSHeet = chExcelAppl:Sheets:Item(1).
chExcelAppl:Cells():SELECT().
chExcelAppl: displayAlerts = false.
chExcelAppl:ActiveSheetASTE().

/* SOME CALCULATION AND DISPLAY */

IF NextSheet = "Y" THEN DO:
chWorkSHeet = chExcelAppl:Sheets:Add().
chExcelApplicationori:Cells():SELECT().
chExcelApplicationori:SELECTION():COPY().
chExcelApplicationori: displayAlerts = false.
chExcelAppl:Cells():SELECT().
chExcelAppl: displayAlerts = false.
chExcelAppl:ActiveSheetASTE().

/* MORE CALCULATION AND DISPLAY */

END.

chExcelApplicationori: displayAlerts = false.
chWorkbookori:Close().
chExcelApplicationori:Quit().

RELEASE OBJECT chExcelApplicationori NO-ERROR.
RELEASE OBJECT chWorkbookori NO-ERROR.
RELEASE OBJECT chWorksheetori NO-ERROR.
RELEASE OBJECT chExcelAppl NO-ERROR.
RELEASE OBJECT chWorkbook NO-ERROR.
RELEASE OBJECT chWorksheet NO-ERROR.

 

[sphipp]

I found some old code. To change the heading for Sheet1 to rows 1-3 try:

chExcelApplicationori:sheets("Sheet1"):pagesetup:PrintTitleRows = "$1:$3"

 

[leekn23]

I found some old code. To change the heading for Sheet1 to rows 1-3 try:

chExcelApplicationori:sheets("Sheet1"):pagesetup:PrintTitleRows = "$1:$3"

Got an error message :
"Error occur while accessing component property/method: sheets.
Execption occurred"

I found the proper command to create underlined font 16 header for everysheet now

chExcelAppl:ActiveSheetageSetup:LeftHeader = "&""-,Regular""&16&USUMMARY REPORT".

THX ALOT FOR ALL THE HELP!!!!!!!

 

[sphipp]

It's probably because your Sheet is not called "Sheet1" - you could try to use the name of your first sheet rather than "Sheet1".

The following might work, although I haven't tried it.

chExcelAppl:Sheets:Item(1):pagesetup:PrintTitleRows = "$1:$3"

 

[sphipp]

You probably don't have "Sheet1" as your worksheet. Have you tried the name of the first worksheet instead of "Sheet1"? That might work.

You could try one of these, although I haven't tested them.

chExcelAppl:Sheets:Item(1):PrintTitleRows = "$1:$3".
chWorkSHeet:pagesetup:PrintTitleRows = "$1:$3".

Quite often, dealing with Excel requires a lot of fiddling around with syntax to get the commands right.