Excel questions

L

leekn23

New Member
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?
 
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.
 
leekn23 said:
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?
Click to expand...

To create a border (Code Snippet Only):
Code: 
    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):

Code: 
    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.
 
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
 
leekn23 said:
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 ...
Click to expand...

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.
 
sphipp said:
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.
Click to expand...

YES! Thats exectly what I want. Never think about doing it in the template side ... Thank you very much Sphipp
 
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:ActiveSheet:PASTE().

/* 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:ActiveSheet:PASTE().

/* 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.
 
I found some old code. To change the heading for Sheet1 to rows 1-3 try:

Code: 
chExcelApplicationori:sheets("Sheet1"):pagesetup:PrintTitleRows = "$1:$3"
 
sphipp said:
I found some old code. To change the heading for Sheet1 to rows 1-3 try:

Code: 
chExcelApplicationori:sheets("Sheet1"):pagesetup:PrintTitleRows = "$1:$3"
Click to expand...

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:ActiveSheet:PageSetup:LeftHeader = "&""-,Regular""&16&USUMMARY REPORT".

THX ALOT FOR ALL THE HELP!!!!!!!
 
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.

Code: 
chExcelAppl:Sheets:Item(1):pagesetup:PrintTitleRows = "$1:$3"
 
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.

Code: 
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.
 
You must log in or register to reply here.
Back
Top