Excel Page Break using OLE2 1

[krindler]

Hello...

Could anyone help me out in adding an Excel Page Break, using OLE2?

I am having some trouble doing this and would appreciate any help you could give me.

Thanks In Advance...

 

[sfvb]

Here's a couple of examples:

DECLARE
   objApplication           OLE2.OBJ_TYPE;
   objWorkbooks             OLE2.OBJ_TYPE;
   objWorkbook              OLE2.OBJ_TYPE;
   objWorksheet             OLE2.OBJ_TYPE;
   objHPageBreak            OLE2.OBJ_TYPE;
   objVPageBreak            OLE2.OBJ_TYPE;
   objPBreakLoc             OLE2.OBJ_TYPE;
   
   args                     OLE2.List_Type;
BEGIN
   -------- Create Excel Application --------
   objApplication := OLE2.CREATE_OBJ('Excel.Application');
   OLE2.SET_PROPERTY(objApplication, 'Visible', TRUE);
   
   -------- Add a Workbook to the Application --------
   objWorkbooks  := OLE2.GET_OBJ_PROPERTY(objApplication, 'Workbooks');
   objWorkbook   := OLE2.INVOKE_OBJ(objWorkbooks, 'Add');
   
   -------- Make the 1st Worksheet the active sheet --------
   args := OLE2.CREATE_ARGLIST;
   OLE2.ADD_ARG(args, 1); 
   objWorksheet := OLE2.GET_OBJ_PROPERTY(objWorkbook, 'Worksheets', args);  
   OLE2.DESTROY_ARGLIST(args);
   OLE2.INVOKE(objWorksheet, 'Select');  
   
   
   ----------------------------------------------
   ------------ VBA macro to emulate ------------
   ----------------------------------------------
   ----   Dim objPBreakLoc    As Object
   ----   
   ----   Set objPBreakLoc = Sheets(1).Range("D12")
   ----   ActiveWindow.SelectedSheets.HPageBreaks.Add objPBreakLoc
   ---------------------------------------------
   ---------------------------------------------
   
   -------- get reference to cell D12 using Range method --------
   args := OLE2.CREATE_ARGLIST; 
   OLE2.ADD_ARG(args, 'D12');
   objPBreakLoc := OLE2.GET_OBJ_PROPERTY(objWorksheet, 'Range', args);
   OLE2.DESTROY_ARGLIST(args);
   
   -------- throw in a formula to give us something to look at --------
   OLE2.SET_PROPERTY(objPBreakLoc, 'Formula', '="Horizontal Page break is above this cell"');
   
   -------- set the horizontal page break above cell D12 --------
   objHPageBreak := OLE2.GET_OBJ_PROPERTY(objWorksheet, 'HPageBreaks');
   args:= ole2.create_arglist; 
   OLE2.ADD_ARG_OBJ(args, objPBreakLoc);
   OLE2.INVOKE(objHPageBreak, 'Add', args);
   OLE2.DESTROY_ARGLIST(args);
   OLE2.RELEASE_OBJ(objHPageBreak);
   OLE2.RELEASE_OBJ(objPBreakLoc);
   
   
   ----------------------------------------------
   ------------ VBA macro to emulate ------------
   ----------------------------------------------
   ----   Dim objPBreakLoc    As Object
   ----   
   ----   Set objPBreakLoc = Sheets(1).Range("G20")
   ----   ActiveWindow.SelectedSheets.VPageBreaks.Add objPBreakLoc
   ---------------------------------------------
   ---------------------------------------------
   
   -------- get reference to cell G20 using Cells Method --------
   args := OLE2.CREATE_ARGLIST; 
   OLE2.ADD_ARG(args, 20);  ---- Row 20
   OLE2.ADD_ARG(args, 7);   ---- Column G
   objPBreakLoc := OLE2.GET_OBJ_PROPERTY(objWorksheet, 'Cells', args);
   OLE2.DESTROY_ARGLIST(args);
   
   -------- throw in a formula to give us something to look at --------
   OLE2.SET_PROPERTY(objPBreakLoc, 'Formula', '="Vertical Page break is to the left of this cell"');
   
   -------- set the vertical page break to the left of cell G20 --------
   objVPageBreak := OLE2.GET_OBJ_PROPERTY(objWorksheet, 'VPageBreaks');
   args:= ole2.create_arglist; 
   OLE2.ADD_ARG_OBJ(args, objPBreakLoc);
   OLE2.INVOKE(objVPageBreak, 'Add', args);
   OLE2.DESTROY_ARGLIST(args);
   OLE2.RELEASE_OBJ(objVPageBreak);
   OLE2.RELEASE_OBJ(objPBreakLoc);
   
   
   ----------------------------------------------
   ------------ VBA macro to emulate ------------
   ----------------------------------------------
   ----   Dim objPBreakLoc    As Object
   ----   
   ----   Set objPBreakLoc = Sheets(1).Range("L35")
   ----   ActiveWindow.SelectedSheets.HPageBreaks.Add objPBreakLoc
   ----   ActiveWindow.SelectedSheets.VPageBreaks.Add objPBreakLoc
   ---------------------------------------------
   ---------------------------------------------
   
   -------- get reference to cell L35 using Range method --------
   args := OLE2.CREATE_ARGLIST; 
   OLE2.ADD_ARG(args, 'L35');
   objPBreakLoc := OLE2.GET_OBJ_PROPERTY(objWorksheet, 'Range', args);
   OLE2.DESTROY_ARGLIST(args);
   
   -------- throw in a formula to give us something to look at --------
   OLE2.SET_PROPERTY(objPBreakLoc, 'Formula', '="Horizontal and Vertical Page breaks"');
   
   -------- set both the vertical and horizontal page breaks at cell L35 --------
   objHPageBreak := OLE2.GET_OBJ_PROPERTY(objWorksheet, 'HPageBreaks');
   objVPageBreak := OLE2.GET_OBJ_PROPERTY(objWorksheet, 'VPageBreaks');
   args:= ole2.create_arglist; 
   OLE2.ADD_ARG_OBJ(args, objPBreakLoc);
   OLE2.INVOKE(objHPageBreak, 'Add', args);
   OLE2.INVOKE(objVPageBreak, 'Add', args);
   OLE2.DESTROY_ARGLIST(args);
   OLE2.RELEASE_OBJ(objVPageBreak);
   OLE2.RELEASE_OBJ(objHPageBreak);
   OLE2.RELEASE_OBJ(objPBreakLoc);
   
   -------- Release remaining objects in reverse order --------
   OLE2.RELEASE_OBJ(objWorksheet);
   OLE2.RELEASE_OBJ(objWorkbook);
   OLE2.RELEASE_OBJ(objWorkbooks);
   OLE2.RELEASE_OBJ(objApplication);
EXCEPTION
   WHEN others THEN
      message('Error');
END;