Skip to main content

Problem with Excel OLE

  • January 17, 2013
  • 2 replies
  • 0 views
D

In the archived forums there is a great example of controlling excel through ole automation.

I have used the sample program from time to time with out a problem, however I am trying to duplicate a VBA macro that formats a header row nicely. I have most of it working except "AutoFit" and "FreezePanes". AutoFit is more important to me than FreezePanes.

Here is the VB code:

 Rows("1:1").Select
 With Selection.Interior
         .ColorIndex = 37
         .Pattern = xlSolid
 End With
 Selection.Font.Bold = True
 Selection.AutoFilter
 Rows("2:2").Select
 ActiveWindow.FreezePanes = True
 Cells.Select
 Cells.EntireColumn.AutoFit
 Range("A1").Select

Here is the reevant parts of the COBOL code:

INQUIRE EX-WORKBOOK @Worksheets::Item(1) IN EX-WORKSHEET.

INQUIRE EX-WORKSHEET @Range("A3:O3") IN EX-RANGE.
MODIFY EX-RANGE @Font::Bold = 1.
MODIFY EX-RANGE @Interior::ColorIndex = 37.
MODIFY EX-RANGE @Interior::Pattern = @xlSolid.
MODIFY EX-RANGE @AutoFilter = 1.
* MODIFY EX-RANGE @AutoFit.
* MODIFY EX-WORKSHEET @FreezePanes = 1.

DESTROY EX-RANGE.

 It works with AutoFit and FreezePanes remarked out.

Any ideas?


#ExcelOLE

    2 replies

    D
    • Dominique SacreRocketeer
    • Author
    • Rocketeer
    • 19312 replies
    • January 18, 2013

    In the archived forums there is a great example of controlling excel through ole automation.

    I have used the sample program from time to time with out a problem, however I am trying to duplicate a VBA macro that formats a header row nicely. I have most of it working except "AutoFit" and "FreezePanes". AutoFit is more important to me than FreezePanes.

    Here is the VB code:

     Rows("1:1").Select
     With Selection.Interior
             .ColorIndex = 37
             .Pattern = xlSolid
     End With
     Selection.Font.Bold = True
     Selection.AutoFilter
     Rows("2:2").Select
     ActiveWindow.FreezePanes = True
     Cells.Select
     Cells.EntireColumn.AutoFit
     Range("A1").Select

    Here is the reevant parts of the COBOL code:

    INQUIRE EX-WORKBOOK @Worksheets::Item(1) IN EX-WORKSHEET.

    INQUIRE EX-WORKSHEET @Range("A3:O3") IN EX-RANGE.
    MODIFY EX-RANGE @Font::Bold = 1.
    MODIFY EX-RANGE @Interior::ColorIndex = 37.
    MODIFY EX-RANGE @Interior::Pattern = @xlSolid.
    MODIFY EX-RANGE @AutoFilter = 1.
    * MODIFY EX-RANGE @AutoFit.
    * MODIFY EX-WORKSHEET @FreezePanes = 1.

    DESTROY EX-RANGE.

     It works with AutoFit and FreezePanes remarked out.

    Any ideas?


    #ExcelOLE

    Example where we use AutoFit:

              inquire xls-sheet  @CELLS xls-range.

              modify  xls-range  @SELECT().

              modify  xls-range  @COLUMNS::@AUTOFIT().

    u have to define what you will AutoFit...

    ---

    Example for FreezePanes:

              inquire xls-sheet  @CELLS::@ITEM(2, "A") xls-range.      

              modify  xls-range @SELECT().

              modify  xls-app   @ActiveWindow::@FreezePanes = 1.

    The Freeze must be defined for the Active Window...

    D
    • Dominique SacreRocketeer
    • Author
    • Rocketeer
    • 19312 replies
    • January 18, 2013

    In the archived forums there is a great example of controlling excel through ole automation.

    I have used the sample program from time to time with out a problem, however I am trying to duplicate a VBA macro that formats a header row nicely. I have most of it working except "AutoFit" and "FreezePanes". AutoFit is more important to me than FreezePanes.

    Here is the VB code:

     Rows("1:1").Select
     With Selection.Interior
             .ColorIndex = 37
             .Pattern = xlSolid
     End With
     Selection.Font.Bold = True
     Selection.AutoFilter
     Rows("2:2").Select
     ActiveWindow.FreezePanes = True
     Cells.Select
     Cells.EntireColumn.AutoFit
     Range("A1").Select

    Here is the reevant parts of the COBOL code:

    INQUIRE EX-WORKBOOK @Worksheets::Item(1) IN EX-WORKSHEET.

    INQUIRE EX-WORKSHEET @Range("A3:O3") IN EX-RANGE.
    MODIFY EX-RANGE @Font::Bold = 1.
    MODIFY EX-RANGE @Interior::ColorIndex = 37.
    MODIFY EX-RANGE @Interior::Pattern = @xlSolid.
    MODIFY EX-RANGE @AutoFilter = 1.
    * MODIFY EX-RANGE @AutoFit.
    * MODIFY EX-WORKSHEET @FreezePanes = 1.

    DESTROY EX-RANGE.

     It works with AutoFit and FreezePanes remarked out.

    Any ideas?


    #ExcelOLE

    Thanks, that works like a charm!


    Terms & ConditionsAccessibility statement